Skip to main content
B
btrevaskis1-Visitor
1-Visitor
March 20, 2018
Question

Call MSSQL stored procedure with user defined table type

  • March 20, 2018
  • 7 replies
  • 4406 views

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

S
supandey5-Regular Member
5-Regular Member
March 21, 2018

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

B
btrevaskis1-VisitorAuthor
1-Visitor
March 21, 2018

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. 

S
supandey5-Regular Member
5-Regular Member
March 21, 2018
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.
Terms & ConditionsAccessibility statement