Stored Procedure call with multiple input and output parameters

[dbrown78]

Ok, I have seen many postings on this in here, but none of the postings have quite what I am looking for.  

My stored procedure has 8 input parameters (of varying datatypes) and 7 columns of returned data.  I can find examples with multiple inputs and a single element output, but none with multiple results and/or columns.

Everything I've tried produces the same error:

Error -2147217887 occurred at NI_Database_API.lvlib:Cmd Execute.vi->StoredProc.vi

Possible reason(s):
ADO Error: 0x80040E21
Exception occured in Microsoft OLE DB Provider for SQL Server: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. in NI_Database_API.lvlib:Rec Create - Command.vi->NI_Database_API.lvlib:Cmd Execute.vi->StoredProc.vi

I tried stepping through the sub-vis, but I just can't track down what the exact error is. 

Here is the stored proc call I run (successfully) in SQL Sever.

exec dbo.PULL_ALLOCATION_PLANS
'MODEL' --@LV_CNN
, 'Program' -- @LV_PROGRAM
, 'true' -- @LV_CLEARED
, 'false' -- @LV_DAMAGE
, 'false' --@LV_ANTITHEFT
, '3' -- @LV_GRADE
, '0' --@LV_COSMETICS
, '0' --@LV_FUNCTIONAL

It generates 7 output columns with typically 3-4 rows

The attached file contains a For loop displaying the columns and datatypes of the output.

If anyone can point me in the right direction here, I'd greatly appreciate it!

[abalma21]

Hello, Brown!

This link describes how to set input parameters and get output parameters from an SQL Stored Procedure with Labview Database Connectivity Toolkit:

Have you already checked it? If not, please tell me if it is helpful.

Regards,

Gustave

[dbrown78]

I did see that and tried following it for running the stored proc.  The error is getting generated at the execute query sub vi (before i even get to the output piece in the example.  I tried wiring inputs only to the set parameters and all the inputs and expected outputs.  All with the same result.  I'm really stumped here. I am able to run the stored proc in MS SQL no issues... just cant seem to execute from LabVIEW.  I also tried running it like a normal query, as you would in MS SQL (exec dbo... etc), but that doesn't work either.  

[JensG69]

Hi, I use this setup with procedure calls on an MS-SQL-Server:

But I'm using only the directions Input & Output.

The string "SQL procedure call" has the format:

{?=call PROCEDURENAME(?,?,?)}

where the number of questionmarks within the parantheses equals the number of inputs & outputs.

Jens

EDIT: The FOR-Loop is not necessary. It is there to repeat the procedure call once in case of an error.

[Bill_Welch]

I do not know if you have found a Solution but the idea it sounds like is you are calling a stored procedure that can return multiple columns and rows of data.

The solution to that is to use the Fetch Record-set from the database connectivity toolkit.  It will return the entire recordset produced by the stored procedure output.  You take the output of that and pass it to a database variant to data converter and it will output an array of the data.

You have to know the columns and names that will be returned as they have to be used to tell the variant to data converter how to format the variant.

If need be I believe I have an example of how I did something similar with an oracle procedure.