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.

[TommyVercetti]

Just using the SP name alone as the query input, without the "{call sp...(?,?...)}" format, fixed the problem.  I got that "{call sp...}" stuff from an old NI PDF titled "Database Connectivity Toolkit User Manual" and hadn't thought to question that detail.  Thanks for the help!

[KevinHarter]

When I try to implement this solution I get an error that the:

Procedure or function 'sp_setapprole' expects parameter '@rolename', which was not supplied...

I have tried a few different things, like making the name of my parameter rolename (as shown in the examples), @rolename, and '@rolename' but none of those options work.  I keep getting the same error.

I feel like I am missing something obvious, but just don't see it.  Do I have to do something special to get it to recognize rolename as the parameter it wants to be '@rolename'? 

Any help would be appreciated!

[aputman]

There is a bug associated with the database toolkit that NI hasn't fixed in many years.  I just modify the toolkit code myself.  If you ever upgrade versions, you'll have to remember to modify the code again.  Very frustrating.  

https://forums.ni.com/t5/LabVIEW/2013-Database-Toolkit-Create-Parameterized-Query-Postgres-Bug/m-p/2...

[KevinHarter]

You know, I saw the discussion on that earlier in the thread and had assumed that by 2023 it was fixed...  That has alleviated the error that I was getting, thank you!  Now I just need to figure out what the LabVIEW equivalent of a varbinary(8000) is.