how do you use native LABVIEW to Generate a list of MSSQL DB Stored _Procederes

[jollybandit]

Need assistance in resolving an issue with calling MSSQL 2008 server side user stored_Procedures.

I have currently been writing and executing MSSQL code to call the procedure names of the user stored procedures which i then use with the LabVIEW DB Tools Create Parameterized Query VI.

 

Is there a native LabVIEW coding which is able to get the list of the user stored procedures from the MSSQL database?

 

i have attached code segment that i am currnetly using. Once i have the user Stored Procedure names i have already worked out how to call the associated input /output/ parameters with there details

 

Thanking you in advance for your assistance

 

[LogMAN]

Is there a native LabVIEW coding which is able to get the list of the user stored procedures from the MSSQL database?

 

No. Your current implementation is as native as it can get.

 

[jollybandit]

Whilst that is fine i was endevouring  for a more Labview refined way of doing this such that it wasn't dependant onembedded  'sql' coding in my vi(more Generic for any db).

 

I also notce (as shown in the third labview code snipett)  that if i pass in the name of the stored procedure i get all the elements as part of the stored user procedure plus an additional parameter called @return_Value - which is not in my written stored procedure  .

 

Is this something Labview inserts /  generates or is it some function of the setup of the database itself???

[LogMAN]

You should be able to view the code of the toolkit. All VIs are basically wrapper around an ADODB Connection. The function you use to receive parameters does that on purpose (using ADODB functions!), but only if the database supports that (pros & cons are listed in that VI too). There is a whitepaper from NI that describes the DCT:

http://www.ni.com/white-paper/3563/en/

 

One statement says:

 

The Database Connectivity Toolkit can connect to any database with an ADO-compliant OLE DB provider or ODBC driver.

 

Now if there is a native way to receive a list of stored procedures using 'ADO-compilant OLE DB provider or ODBC driver', you could write an idea in the Idea Exchange.

[Phillip Brooks]

I had (but lost) some VIs that were knock-offs of the DB Tools List Table and the related Schema VIs. I recreated them fairly quickly this morning.

 

These schema queries return all of a type for a DB, which includes internal. Your tables and views will normally be of a type "dbo". You will have to filter for those in LV. I tried to query my DB for procedures and I did not see a column to filter between system and my own.

 

Google the selected Schema type to get the column names, but most are fairly obvious.

 

If you need to create typdefs for a collection of tables, you can use this scripting VI that I uploaded on the NI Community forums a while back. 

 

https://decibel.ni.com/content/docs/DOC-19512

 

ADO Schema.llb (LV2012)

Edited February 12, 2014 by Phillip Brooks