In reply to: Account deleted
Hi,
Alright, I was able to "partially" solve the problem. It simply led to another problem.
It really looks like the SQL query was the problem. 😒 Not sure why, but it seems to be the named blocks of Webcon. Example {WFCONCOL:1257}
(I didn't even type in the named blocks manually, but rather by using the fields in the SQL query configuration dialog of Webcon).
(And via "Test" in the configuration dialog it always worked, so I excluded the query first - obviously hasty after all)
Here's how I solved the original problem with the syntax error '}':
I enclosed each table attribute with [ and ]. This sanitizes the attribute names.
**
Here I have a question for the Webcon developers. Are SQL queries "interpreted" or "transformed" again in Webcon before they are executed?
Are comments with '--' or '/* */' filtered out?
I noticed that 'ORDER BY' does not work with the 'FROM' table if there is no 'TOP (any number)' in the 'SELECT'.
There is an "Unkown error" in the query test. Even if I comment out the 'ORDER BY' in the query.
As soon as 'TOP (any number)' is present, the 'ORDER BY' works again.
**
Nevertheless, now for my new problem. 😅
The SQL query outputs employee names and fills them into person fields. (Example 'Supervisor' of a requester/author).
The problem now is that I am outputting "some" persons who are no longer employed by the company and therefore are not recognized by Webcon as users because they are not BPS users (BPS ID).
The error "No exact match was found for: 'former employee name'" is displayed.
My question: is there a possibility to insert users that are not available as BPS users into a person field?
(what I found out is that if the employee is in BPS format "user@example.com#User name" it can be inserted regardless of whether the person really exists or not. But since we have several hundred employees I don't know which persons are no longer employed and if I can change the entry on-the-fly at all (example: "no-reply@company.com#former employee name")).
Does anyone have an idea how to solve this?
Thanks a lot in advance.
Hi Roman,
comments:
The query is passed as it is, -- and /**/ are part of it. Even so single line comments seldom cause problems, it happens.
If you enable the diagnostic mode, you can search the trace for a line from your query and see the executed one. That's far easier than my previous approach to use the SQL Profiler. :)
Users no longer available:
There was once a stored procedure ReplaceLogins which updated logins, but it seems that it was removed in later versions. That would have been an option to update the old users. At least if you want to do it. Of course this would break the audit trail, as this was intended to replace the old user new with a new one, if this changed.
ALTER PROCEDURE [dbo].[ReplaceLogins]
@oldLogin nvarchar(150),
@newLogin nvarchar(150),
@newDisplayName nvarchar(100)
AS
BEGIN
Since this is no longer an option, I would do the following:
Add a dictionary which maps the old users to new ones.
Build a cyclical action which periodically checks the relevant person/choose fields whether a user id used, which is no longer in the CacheOrganizationStructure
Add an entry and send a mail, that something needs to be done.
For example define a new user or fallback user.
In the processes where this could happen you could then map the user for example with some like this
select
...
, isnull((select NewUserId from Dictionary where OldUserId = Author),Author)
If the author still exist, there should be no entry and it would use author, otherwise the new user
The action populating the dictionary should also check, whether a user account is back again, for any reason.
Off topic:
The removed users, was a use case for my first blog post. :)
https://daniels-notes.de/posts/2021/series-expert-guide-part-1#part-overview
Best regards,
Daniel
09.08.2023
15:00
