Stored Procedures how Can I set the OUTPUT and Execute

[wmg025]

Hello Guys,

I am trying to make a Stored Procedure goes throught the NI TestStand or Labvie but no Sucess. For labview I am having the following error message:

NI_Database_API.lvlib:Cmd Execute.vi->SQL SP Return_Test.vi<ERR>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->SQL SP Return_Test.vi

And When I tried the NI TestSandard I am geeting the following response form the step Get Results:

Error getting data from column "@OutMsgTest". The column '@OutMsgTest' was not returned from the SQL statement.

Error getting data from column "@OutMsgPrint". The column '@OutMsgPrint' was not returned from the SQL statement.

SQL (Stored Procedures)

USE [TSTFNLT8]

GO

DECLARE @return_value int,

@OutMsgTest varchar(100),

@OutMsgPrint varchar(100)

EXEC @return_value = [dbo].[sproc_App_LookupTestData]

@FobSN = N'1415199D',--B83FC39C

@FOBPN = N'227587-139',

@OutMsgTest = @OutMsgTest OUTPUT,

@OutMsgPrint = @OutMsgPrint OUTPUT

SELECT @OutMsgTest as N'@OutMsgTest',

@OutMsgPrint as N'@OutMsgPrint'

SELECT 'Return Value' = @return_value

GO

I have attached both vi and the sequence.

I would aprreciate your help.

Marcelo

[aputman]

I'm a bit confused about your SQL code.  I am not an expert on SQL but when I create a stored procedure in Microsoft SQL, it would look something like this:

CREATE PROCEDURE sproc_App_LookupTestData 
	-- Add the parameters for the stored procedure here
	@OutMsgTest varchar(100) OUTPUT,
	@OutMsgPrint varchar(100) OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT @OutMsgTest as N'@OutMsgTest',
		@OutMsgPrint as N'@OutMsgPrint'
END
GO

I am not sure what you are trying to do in your SQL code. Is that your stored procedure?  Please explain.

[aputman]

For setting up a call to a stored procedure in Labview, I would suggest building your parameter array similar to this.  This keeps everything organized, each parameter has a case in the case structure and it's easy to add additional parameters if necessary. 

Also, if i remember correctly, for output parameters, you have to wire up a data type to the Value variant so that space can be reserved for the response.  So in this example, for OutMsgTest, I wired an empty string to the value variant terminal. 

[wmg025]

Hi , thank you for the Help but I still got the same error when changed and build as you sent:

NI_Database_API.lvlib:Cmd Execute.vi->SQL SP Return_Test.vi<ERR>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->SQL SP Return_Test.vi

Is thare any other idea ?

Regards,

Marcelo

[wmg025]

Hi, thank you for you help.

The Sql is including the Stored Procedure on it:

I can not open the stores procedure source code because this is IT side I can just see the parameters and this is what Microsoft SQL Server Management Studio shows me when I execute the Stores Procedure [sproc_App_LookupTestData]

USE [TSTFNLT8]

GO

DECLARE @return_value int,

@OutMsgTest varchar(100),

@OutMsgPrint varchar(100)

EXEC @return_value = [dbo].[sproc_App_LookupTestData]

@FobSN = N'1415199D',--B83FC39C

@FOBPN = N'227587-139',

@OutMsgTest = @OutMsgTest OUTPUT,

@OutMsgPrint = @OutMsgPrint OUTPUT

SELECT @OutMsgTest as N'@OutMsgTest',

@OutMsgPrint as N'@OutMsgPrint'

SELECT 'Return Value' = @return_value

GO

[aputman · Accepted solution]

Does SSMS return the expected results of the stored procedure?

Have you searched for that error code on Google?  After a quick search on my end, the common cause of that error is a type mismatch in your column datatype.  It's hard to say without seeing any SQL code.

I also notice that on the Create Parameterized Query VI that you have this as your query text:

{call sproc_App_LookupTestData(?,?,?)}

This is missing a ? if you indeed have 4 parameters in this SP.  It should be:

{call sproc_App_LookupTestData(?,?,?,?)}

[aputman]

Also try copying the text below into a text file and save it with a .udl extension.  And then double-clicking the file will open it up in a utility where you can test the connection to see if your connection string is correct.  If the connection fails, modify the settings until you get a successful connection and open the UDL up in a text editor to get the modified connection string.  

[oledb]
Provider=SQLOLEDB.1;Password=p@ssw0rd;Persist Security Info=True;User ID=test_eng;Initial Catalog=TSTKIT03;Data Source=AUBNY-FNLTST02

[wmg025]

Perfect. Solved. Thank you for your support

Thanks in advanced