01-24-2017 11:52 AM - edited 01-24-2017 11:54 AM
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
Solved! Go to Solution.
01-24-2017 01:34 PM
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.
01-24-2017 02:56 PM
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.
01-25-2017 03:32 PM
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
01-25-2017 03:37 PM
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
01-25-2017 04:10 PM
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(?,?,?,?)}
01-25-2017 04:25 PM
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
01-26-2017 10:06 AM
Perfect. Solved. Thank you for your support
Thanks in advanced