SQL stored procedure input and output parameters

[RogerMontague]

Hello Gang,

My stymied trying to call an SQL Server 2008 Stored procedure with the LV Database Toolkit.

for experimentation purposes, I've created a small procedure with one input parameter that returns an integer as a return value.  It works calling it from a query, but all attempts from LV yield an error -2147217900 at the execute query VI.  I've not found any examples of doing this.

Sending a bunch of parameters with a stored procedure and checking the return value would seem to be pretty common stuff.

I'll appreciate all help.

Thanks,

Roger

[MattWhitlock · Accepted solution]

Hi Roger,

There are a couple of prerequisites which I'll list below. If you haven't verified these things, please do so as your hiccup may be there.

Prerequisites:

1. Ensure that you can read from a table in the database to verify that you can access the database (user permissions) and verify that your connection information is correct. Among other things, ensure that the .DSN switches to the correct database. (Simple example below, attached as SQL Select.vi)
   
2. Ensure that your user (if using SQL Authentication) has permissions to execute the SPROC by executing the SPROC in SQL Server Management Studio (you appear to have done this already)

Example calling a SPROC without parameters (Attached as SPROC_No_Parameters.vi):

Example calling a SPROC with parameters (Attached as SPROC_Parameters.vi):

 

An article on executing SPROCs is here:

http://digital.ni.com/public.nsf/allkb/07FD130746083E0686257300006326C4

Be sure to download the example VI as well as it includes the case where you want an output paramater from the SPROC rather than a table.

I tested these against two very simple SPROCs and they worked fine (on SQL Server 2005, but 2008 should be the same).  SPROCs were:

CREATE PROCEDURE [dbo].[GetContacts]   

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT * FROM Contacts
END

and

CREATE PROCEDURE [dbo].[MultiplyAges]   
    @param1 INT = 1
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT (Age * @param1) FROM Contacts
END

Let me know if these examples work for you or if you have more questions.  Be sure to go through that KB article and the articles linked from it, they should cover other situations.

Matt

[RogerMontague]

Hi Matt,

Thank you for your detailed response.  I still have not been able to get parameters to work with both input and ourput parameters.   What I ended up doing was effectively the same as your example "without parameters."  I used string concatenation to add the parameters on to the end of the query, and the Fetch all VI to retrieve the returned integer.

When the time crunch is over, I'll experiment more with your "with parameters" example.  That's pretty much what I tried, but yesterday I always got the error.

Thanks Again!

Roger  

[MattWhitlock]

Hi Roger,

Glad you got a work-around going.  Feel free to post your VI that you're getting the errors and I'll take a look to see if I can reproduce the error and get a solution.  The example VI from the article has both input and output parameters that may be helpful.

[rscada19]

Is this possible to connect sql database with labview using labview7.0

[rolfk]

Yes you can get the Database Toolkit from NI (though might be hard to purchase it in a way still compatible to LabVIEW 7) or any of the other (sometimes free) database access libraries such as LabSQL, IBB ADO Toolkit, ODBCView, T&M Database Wizard,  XON ADO Toolkit. Not all of these will support LabVIEW 7.0 either.

Message Edited by rolfk on 03-18-2010 10:20 AM

[Eduardo Alexandre]

Hello,

I'm in doubt, to execute a stored procedure that I did for testing in labview. I am using the example above.

This is my stored procedure to test:

PROCEDURE [dbo].[Listar]

@id int

AS

select name,number from testt where id=@id

Could you show me an example of how to execute this stored procedure using the example of the linkhttp://digital.ni.com/public.nsf/allkb/07FD130746083E0686257300006326C4 ?

I use connection udl.