cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Need to share some code when posting a question or reply? Make sure to use the "Insert code sample" menu option. Learn more! X

Call MSSQL stored procedure with user defined table type

btrevaskis
5-Regular Member

Call MSSQL stored procedure with user defined table type

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. 

7 REPLIES 7
supandey
19-Tanzanite
(To:btrevaskis)

Hi @btrevaskis wondering if you tried it with SQL Command as the service handler.

btrevaskis
5-Regular Member
(To:supandey)

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. 

supandey
19-Tanzanite
(To:btrevaskis)

Going by the stored procedure name i suppose you are looking to update certain entity in DB, so keeping that in mind my vote will be for SQLCommand rather than SQL Query. May be you can test with SET NOCOUNT ON , having it prior to the execution statement for the stored procedure.
btrevaskis
5-Regular Member
(To:supandey)

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
)
supandey
19-Tanzanite
(To:btrevaskis)

Does it throw same error if you replace [[Data]] with <<Data>> ?

I would also put the LoggingSubsystem in StackTrace by going to Composer > System > Subsystems > Logging Subsystem > Configuration > Enable Stack Tracing & Enable Script Stack Tracing* (* this configuration is available starting 8.2)

Then reproduce the problem and check for the stack trace in ThingWorxStorage\logs errorlogs & scriptErrorLog* (*available starting 8.2)
btrevaskis
5-Regular Member
(To:supandey)

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. 

supandey
19-Tanzanite
(To:btrevaskis)

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

Top Tags