Community Tip - Did you know you can set a signature that will be added to all your posts? Set it here! X
I am attempting to pass a user-defined table type to a stored procedure as an input parameter from within a Thingworx service. I've created the stored procedure, user-defined table type and data shape (fields matching the naming and casing of the table type). I've tested the stored procedure from within SQL and it works as expected. However, when trying to call from a service, I get the following error:
Unable to Invoke Service UpdateMachineDowntime on MsSqlOpcaThing : null
The service is set to call as a SQL Query, there is a single input parameter (named Data) configured as an INFOTABLE with the data shape I created, and the text of the call is:
EXEC spUpdateMachineDowntime [[Data]]
Has anyone had any success with this before? I'm concerned this may not be doable.
Hi @btrevaskis wondering if you tried it with SQL Command as the service handler.
Yes, I have tried it both ways. I have made quite a few changes since I last tried that method, so I will try that again.
For the time being, I have written a separate process that essentially serializes the data into a string and de-serializes it on the SQL server. This is working, but it isn't very efficient and I would prefer to avoid it.
You're correct. I'm looking to pass a collection of values that need to have logic processed on them, and then inserted.
I confirmed NO COUNT ON, and set to a sql command. I get the following:
EXEC spUpdateMachineDowntime [[Data]]
results in:
Unable to Invoke Service UpdateMachineDowntime2 on MsSqlOpcaThing : null
EXEC spUpdateMachineDowntime
results in: no error with a return of -1, indicating failure.
I also simplified my stored procedure considerably to ensure the issue wasn't with the code, or how it was handled. The following code is what is running and returning -1. I am able to run this from within SQL without an issue.
ALTER PROC [dbo].[spUpdateMachineDowntime] ( @Data udttMachineDowntime READONLY ) AS SET NOCOUNT ON DECLARE @ret INT SELECT @ret = COUNT(*) FROM @Data RETURN @ret
My user defined table type:
CREATE TYPE [dbo].[udttMachineDowntime] AS TABLE( [MachineName] [VARCHAR](50) NULL, [DownTime] [BIGINT] NULL, [DownTimeLabel] [VARCHAR](50) NULL )
EXEC spUpdateMachineDowntime <<Data>>
Results in
Unable to Invoke Service UpdateMachineDowntime2 on MsSqlOpcaThing : Incorrect syntax near 'rows'.
Unfortunately I'm running on 8.0. We're in the process of standing up our 8.2 servers now.
You can still enable the stack tracing in logging subsystem, sorry may be i wasn't crystal clear. Versions previous to 8.2 don't have separate configuration for script stack tracing, but you will still be able to enable the stack tracing using Composer > System > Subsystems > Logging Subsystem > Configuration > Enable Stack Tracing and this gets logged in the ErrorLog.log in ThingWorxStorage\logs