SQL: Unknown error:Incorrect syntax near ')'...

0

Hi FHE,

my best idea, is to create a trace using SQL Server Profiler, while the problematic action is executed.
Maybe you can add some unique text to the action, so you can easily search for the executed SQL statement.

Once you have found it, you can copy it to management studio and execute it there. This will make it easier to identify the problem and you may even can find a workaround.

Best regards,
Daniel

Hi FHE (Moin/Servus FHE),

before i ask a lot of infos, my question: "Did you already tried to change the _rows to return_ at the bottom?"
It looks, as you only would get one row as result. Some where deep in my memory I believe I had the same problem with the option ALL in the same situation. Not 100% sure but worth a try.

I attached a modified screenshot to make clear, what i would try first.

Hope it helps,
Bjoern Poller

Hi guys (Grüezi),

thanks for your input and sorry for the delayed answer. I upgraded to BPS 2022 hoping it would resolve itself but unfortunately it didn't.

@Bjoern: Thank you. It was set to "all rows" and changing it to "first row" doesn't make a difference. As we can see on your screenshot there's a "Test" button which shows the SQL query that is working. Mine looks as follows:


SELECT * FROM (

SELECT [COS_BpsID] AS [COS_BpsID],[COS_DisplayName] AS [COS_DisplayName],[COS_AzureADID] AS [COS_AzureADID],[COS_Department] AS [COS_Department],[COS_Domain] AS [COS_Domain],[COS_DomainPath] AS [COS_DomainPath],[COS_Email] AS [COS_Email],[COS_GroupType] AS [COS_GroupType],[COS_IsActive] AS [COS_IsActive],[COS_JobTitle] AS [COS_JobTitle],[COS_LastSuccessfulSyncTime] AS [COS_LastSuccessfulSyncTime],[COS_Login] AS [COS_Login],[COS_ManagerBpsID] AS [COS_ManagerBpsID],[COS_ManagerDisplayName] AS [COS_ManagerDisplayName],[COS_ManagerID] AS [COS_ManagerID],[COS_ManagerLogin] AS [COS_ManagerLogin],[COS_OnPremisesSyncEnabled] AS [COS_OnPremisesSyncEnabled],[COS_PhoneNumber] AS [COS_PhoneNumber],[COS_ProviderType] AS [COS_ProviderType],[COS_SID] AS [COS_SID],[COS_SynchronizationError] AS [COS_SynchronizationError],[COS_UserType] AS [COS_UserType],[COS_AD_accountexpires] AS [COS_AD_accountexpires],[COS_AD_adspath] AS [COS_AD_adspath],[COS_AD_cn] AS [COS_AD_cn],[COS_AD_comment] AS [COS_AD_comment],[COS_AD_company] AS [COS_AD_company],[COS_AD_countrycode] AS [COS_AD_countrycode],[COS_AD_department] AS [COS_AD_department],[COS_AD_description] AS [COS_AD_description],[COS_AD_displayname] AS [COS_AD_displayname],[COS_AD_distinguishedname] AS [COS_AD_distinguishedname],[COS_AD_givenname] AS [COS_AD_givenname],[COS_AD_homedirectory] AS [COS_AD_homedirectory],[COS_AD_homephone] AS [COS_AD_homephone],[COS_AD_info] AS [COS_AD_info],[COS_AD_ipphone] AS [COS_AD_ipphone],[COS_AD_iscriticalsystemobject] AS [COS_AD_iscriticalsystemobject],[COS_AD_localeid] AS [COS_AD_localeid],[COS_AD_mail] AS [COS_AD_mail],[COS_AD_manager] AS [COS_AD_manager],[COS_AD_memberOf] AS [COS_AD_memberOf],[COS_AD_mobile] AS [COS_AD_mobile],[COS_AD_name] AS [COS_AD_name],[COS_AD_objectcategory] AS [COS_AD_objectcategory],[COS_AD_objectclass] AS [COS_AD_objectclass],[COS_AD_objectguid] AS [COS_AD_objectguid],[COS_AD_objectsid] AS [COS_AD_objectsid],[COS_AD_objectversion] AS [COS_AD_objectversion],[COS_AD_ou] AS [COS_AD_ou],[COS_AD_pager] AS [COS_AD_pager],[COS_AD_physicalDeliveryOfficeName] AS [COS_AD_physicalDeliveryOfficeName],[COS_AD_postalcode] AS [COS_AD_postalcode],[COS_AD_postofficebox] AS [COS_AD_postofficebox],[COS_AD_primarygroupid] AS [COS_AD_primarygroupid],[COS_AD_proxyaddresses] AS [COS_AD_proxyaddresses],[COS_AD_samaccountname] AS [COS_AD_samaccountname],[COS_AD_samaccounttype] AS [COS_AD_samaccounttype],[COS_AD_securityidentifier] AS [COS_AD_securityidentifier],[COS_AD_showinaddressbook] AS [COS_AD_showinaddressbook],[COS_AD_sn] AS [COS_AD_sn],[COS_AD_st] AS [COS_AD_st],[COS_AD_streetaddress] AS [COS_AD_streetaddress],[COS_AD_telephonenumber] AS [COS_AD_telephonenumber],[COS_AD_title] AS [COS_AD_title],[COS_AD_useraccountcontrol] AS [COS_AD_useraccountcontrol],[COS_AD_userparameters] AS [COS_AD_userparameters],[COS_AD_userprincipalname] AS [COS_AD_userprincipalname],[COS_AD_whencreated] AS [COS_AD_whencreated],[COS_AD_wwwhomepage] AS [COS_AD_wwwhomepage],[COS_ExtensionAttribute01] AS [COS_ExtensionAttribute01],[COS_ExtensionAttribute02] AS [COS_ExtensionAttribute02],[COS_ExtensionAttribute03] AS [COS_ExtensionAttribute03],[COS_ExtensionAttribute04] AS [COS_ExtensionAttribute04],[COS_ExtensionAttribute05] AS [COS_ExtensionAttribute05],[COS_ExtensionAttribute06] AS [COS_ExtensionAttribute06],[COS_ExtensionAttribute07] AS [COS_ExtensionAttribute07],[COS_ExtensionAttribute08] AS [COS_ExtensionAttribute08],[COS_ExtensionAttribute09] AS [COS_ExtensionAttribute09],[COS_ExtensionAttribute10] AS [COS_ExtensionAttribute10],[COS_ExtensionAttribute11] AS [COS_ExtensionAttribute11],[COS_ExtensionAttribute12] AS [COS_ExtensionAttribute12],[COS_ExtensionAttribute13] AS [COS_ExtensionAttribute13],[COS_ExtensionAttribute14] AS [COS_ExtensionAttribute14],[COS_ExtensionAttribute15] AS [COS_ExtensionAttribute15],[COS_ExtensionAttribute16] AS [COS_ExtensionAttribute16],[COS_ExtensionAttribute17] AS [COS_ExtensionAttribute17],[COS_ExtensionAttribute18] AS [COS_ExtensionAttribute18],[COS_ExtensionAttribute19] AS [COS_ExtensionAttribute19],[COS_ExtensionAttribute20] AS [COS_ExtensionAttribute20],[COS_ExtensionAttribute21] AS [COS_ExtensionAttribute21],[COS_ExtensionAttribute22] AS [COS_ExtensionAttribute22],[COS_ExtensionAttribute23] AS [COS_ExtensionAttribute23],[COS_ExtensionAttribute24] AS [COS_ExtensionAttribute24],[COS_ExtensionAttribute25] AS [COS_ExtensionAttribute25],[COS_ExtensionAttribute26] AS [COS_ExtensionAttribute26],[COS_ExtensionAttribute27] AS [COS_ExtensionAttribute27],[COS_ExtensionAttribute28] AS [COS_ExtensionAttribute28],[COS_ExtensionAttribute29] AS [COS_ExtensionAttribute29],[COS_ExtensionAttribute30] AS [COS_ExtensionAttribute30],[COS_ID] AS [COS_ID]

FROM [CacheOrganizationStructure]

WHERE [COS_AccountType] = 1

) AS nnn WHERE (((((NOT (([COS_JobTitle] = N'System-Postfach')) AND NOT (([COS_JobTitle] = N'Team-Postfach'))) AND ([COS_Email] LIKE N'%@domain.com%')) AND NOT (([COS_AD_cn] LIKE N'%sizi%'))) AND NOT (([COS_BpsID] LIKE N'%onmicrosoft.com%')))) and (([COS_BpsID] = N'test.user@domain.com')) ORDER BY [COS_DisplayName]



Nothing out of the ordinary I suppose.



@Daniel: I think this is the query that testing the business rule throws the error for:

exec sp_executesql N 'SELECT TOP 1 [COS_ID], [COS_Login], [COS_SID], [COS_BpsID], [COS_Department], [COS_JobTitle], [COS_Email], [COS_DisplayName], [COS_AccountType], [COS_ManagerLogin], [COS_ManagerBpsID], [COS_AD_userprincipalname], [COS_AD_telephonenumber], [COS_ProviderType], [COS_Domain] FROM [CacheOrganizationStructure] WHERE [COS_Login] = @COS_Login1 AND [COS_IsActive] = @COS_IsActive2 ',
N'@COS_Login1 nvarchar(255),@COS_IsActive2 bit',
@COS_Login1 = N'test.user@domain.com',
@COS_IsActive2 = 1

the error is:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SELECT TOP 1 [COS_ID], [COS_Login], [COS_SID], [COS_BpsID], [COS_Department], [COS_JobTitle], [COS_Email], [COS_DisplayName], [C'.


Let me know if I can supply more screenshots.

Thanks!

Hi,
if I understood correctly, you are using a business rule that returns data from source in the cyclic action 'update instance'?
If so, and given that the action works if you set a constant value in it, it may be because the cyclic action is not executed in the context of a document, so the email value in the filter of the rule will be empty.
I am not sure about this, but as a workaround you can use the cyclic action 'Move workflow' and update the value on the elements path transition

Hi Florian,

Sebastians approach could be an option.

This seems not to be the correct SQL statement because of the different error message. At least I'm assuming that there was some copy&paste issue
There should be no space between N'
wrong: exec sp_executesql N 'SELECT TOP 1...
correct: exec sp_executesql N'SELECT TOP 1...

Maybe you could change the expression with "Rückgabewert AD Cycical..." to something like
'ABCDE >' + "Rückgabewert AD Cycical..." +'<ABCDE'
So that you can search the trace for this expression.
Maybe some unexpected result is returned.

Best regards,
Daniel

Thanks Sebastian and Daniel. Will test soon and report back.