Using sp_setapprole SQL Server stored procedure

[TommyVercetti]

Has anyone successfully used the sp_setapprole and sp_unsetapprole SQL Server stored procedures with LabVIEW?  I'm struggling to get the parameters right, apparently.  I can make it work by building a SQL query string and sending that straight to an Execute Query VI, but I want to return the cookie value, and I'm not sure how to do that.  I'd like to use the Create Parameterized Query with the Set Parameter Value and Get Parameter Value VIs, but haven't had any luck making that work.  Thanks!

[aputman]

I've no idea what that SP does so I can't help you with that part.  For executing stored procedures, something like this is necessary.  The number of parameters must match the SP definition.  Use the Get Parameter function to return the cookie parameter.

If this doesn't work, you may need to refer to this link.  There was a bug in earlier versions.  I modified the "Create Parameterized Query" VI as I explained in this link and it worked.  This may be fixed in later versions...I'm not sure.  Modify at your own risk. 

[TommyVercetti]

Thanks very much for the quick reply.  I understand how the VIs are supposed to work, and I have made the modification to the Create Parameterized Query vi (the bug is still there in 2017). 

My question is very specific to the sp_setapprole SP, which is built in to SQL Server (you can see its definition on Microsoft's site).  It's giving me a "No value given for one or more required parameters" error, even though I have defined the input and output parameters and provided values to the inputs.  I'm hoping someone has worked through implementing this particular SP before and could give me some pointers.

[aputman]

Please post code.  Your question may be specific to a certain SP but based on the error, you aren't setting up the parameters correctly.  You say the SP works using the Execute Query.  So really your question is about how the parameterized query VIs are supposed to work.  

[TommyVercetti]

I can't get the attached "sp test" to work.

The attached "sp test2" does work, but I'm not sure how read the output cookie value using that method.

[aputman · Accepted solution]

If the cookie is the only thing returned, there won't be a recordset like there is with a SELECT query.  I think you have to use the parameterized query in this situation.

I have never used the {call procedure(?,?)} format.  I only use the name of the SP as the query.  This is documented in the help file that way.  And I also create the parameters as shown here, rather than using the SET function.  This is a bit cleaner IMHO and it's really easy to add another parameter if necessary.  

What happens if you use string for the cookie value? VARBINARY does not correlate to any datatype in LabVIEW so I would try string format.  

Since this procedure belongs to the master database, you may have to set your default catalog to master (in connection settings).  I've never had to call a system SP to know for sure.  Maybe the server automatically recognizes it as a system SP.