Hello,
I've successfully created and tested a stored procedure in SSMS. It executes perfectly with the following query:
-- Example execution of the stored procedure DECLARE @UserSID NVARCHAR(MAX) = '0x010500000000000515000000A065CF7E784B9B5FE77C8770F8010000' -- Example SID in hexadecimal string format EXEC [dbo].[cspGetUserGroups] @UserSID
However, when I attempt to execute this stored procedure within a Laserfiche Workflow using the Custom Query activity with the query:
EXEC [dbo].[cspGetUserGroups] @UserSID
and setting the parameter as follows:
- Parameter Name: @UserSID
- Type: String
- Parameter Value: %(UserSID) (where %(UserSID) is a token holding a static SID value)
I encounter an error: "Error converting data type nvarchar to varbinary." This error does not occur when I input the static SID value directly as the parameter value.
It seems like the conversion from NVARCHAR to VARBINARY, which is handled within the stored procedure, is causing issues when executed from Laserfiche Workflow. Here's the relevant portion of my stored procedure:
@UserSID NVARCHAR(MAX) -- Changed from VARBINARY(MAX) to NVARCHAR(MAX) AS BEGIN DECLARE @BinaryUserSID VARBINARY(MAX) = CONVERT(VARBINARY(MAX), @UserSID, 1); -- Use of @BinaryUserSID in the procedure
I'm puzzled about why this conversion error is happening, given that the procedure is supposed to handle the conversion internally. Any insights or suggestions on how to resolve this issue would be helpful!
Edit: Although I have not found a fix when testing the query, publishing and running my workflow resolves the error and returns the expected data.