Returning Stored Proceduers via ADO Schema Rowsets

Hello,
I've been trying to get Stored Procedures and parameter information
via the ADO Schema Rowsets.
However, it seems that the MS SQL driver does not support this.
Can someone confirm this?
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com
> However, it seems that the MS SQL driver does not support this.
The following VBScript works fine for me using ADO 2.8 and SQLOLEDB.
Set conn = CreateObject("ADODB.Connection")
ConnectionString = "Provider=SQLOLEDB" & _
";Data Source=MyServer" & _
";Initial Catalog=MyDatabase" & _
";Integrated Security=SSPI"
conn.Open connectionString
MsgBox ListProcParameters("MyDatabase", "dbo", "MyProcedure")
conn.Close
Function ListProcParameters(Database, Schema, ProcName)
Dim result, filter(3)
filter(0) = Database
filter(1) = Schema
filter(2) = ProcName
Set rs = conn.OpenSchema(26, filter)
Do While rs.EOF = False
result = result & _
"PROCEDURE_SCHEMA" & "=" & _
rs.Fields("PROCEDURE_SCHEMA") & vbCrLf & _
"PROCEDURE_NAME" & "=" & _
rs.Fields("PROCEDURE_NAME") & vbCrLf & _
"PARAMETER_NAME" & "=" & _
rs.Fields("PARAMETER_NAME") & vbCrLf & _
vbCrLf
rs.MoveNext
Loop
rs.Close
ListProcParameters = result
End Function
Hope this helps.
Dan Guzman
SQL Server MVP
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:%23B6mxWZRFHA.2744@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I've been trying to get Stored Procedures and parameter information
> via the ADO Schema Rowsets.
> However, it seems that the MS SQL driver does not support this.
> Can someone confirm this?
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
> SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
|||Hello Dan,

> The following VBScript works fine for me using ADO 2.8 and SQLOLEDB.
Can you list procedures as well?
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com

> Set conn = CreateObject("ADODB.Connection")
> ConnectionString = "Provider=SQLOLEDB" & _
> ";Data Source=MyServer" & _
> ";Initial Catalog=MyDatabase" & _
> ";Integrated Security=SSPI"
> conn.Open connectionString
> MsgBox ListProcParameters("MyDatabase", "dbo", "MyProcedure")
> conn.Close
> Function ListProcParameters(Database, Schema, ProcName)
> Dim result, filter(3)
> filter(0) = Database
> filter(1) = Schema
> filter(2) = ProcName
> Set rs = conn.OpenSchema(26, filter)
> Do While rs.EOF = False
> result = result & _
> "PROCEDURE_SCHEMA" & "=" & _
> rs.Fields("PROCEDURE_SCHEMA") & vbCrLf & _
> "PROCEDURE_NAME" & "=" & _
> rs.Fields("PROCEDURE_NAME") & vbCrLf & _
> "PARAMETER_NAME" & "=" & _
> rs.Fields("PARAMETER_NAME") & vbCrLf & _
> vbCrLf
> rs.MoveNext
> Loop
> rs.Close
> ListProcParameters = result
> End Function
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
> news:%23B6mxWZRFHA.2744@.TK2MSFTNGP10.phx.gbl...
>
|||Hello Dan,

> Can you list procedures as well?
Gave it another try -- it seems that the first filter parameter "catalog"
needs
to be set, else it will return an error.
Works for other drivers though :-)
Thanks for the reality check.
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com
|||> > > > However, it seems that the MS SQL driver does not support this.
> Gave it another try -- it seems that the first filter parameter "catalog"
> needs
> to be set, else it will return an error.
> Works for other drivers though :-)
> Thanks for the reality check.
Funny, that wasn't it ... I did a filter (4th parameter) on DB_PT_PROCEDURE
which seems to fail...
This way, I should get procedures only and no functions...
Hmmm...
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com
|||> This way, I should get procedures only and no functions...
It seems both procedures and functions return DB_PT_PROCEDURE. To
distinguish between procs and functions, you can instead use the ANSI
standard INFORMATION_SCHEMA.ROUTINES view in a SQL query.
In my experience, schema rowset behavior can be inconsistent among various
providers and provider versions. If you need to retrieve meta-data from a
variety of DBMS vendors, you might consider developing your own interface so
that you can use the best technique depending on the underlying DBMS and
version.
Hope this helps.
Dan Guzman
SQL Server MVP
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:eBLVI7jRFHA.4028@.tk2msftngp13.phx.gbl...
> Funny, that wasn't it ... I did a filter (4th parameter) on
> DB_PT_PROCEDURE
> which seems to fail...
> This way, I should get procedures only and no functions...
> Hmmm...
>
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
> SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
|||> > This way, I should get procedures only and no functions...
> It seems both procedures and functions return DB_PT_PROCEDURE. To
Apparently so.

> distinguish between procs and functions, you can instead use the ANSI
> standard INFORMATION_SCHEMA.ROUTINES view in a SQL query.
Hmm... given that this isn't as supported as it should ... :-/

> In my experience, schema rowset behavior can be inconsistent among various
> providers and provider versions. If you need to retrieve meta-data from a
> variety of DBMS vendors, you might consider developing your own interface
so
> that you can use the best technique depending on the underlying DBMS and
> version.
Yes I know ... but for this particular tool, this is a no-go area...
I'll settle for somewhat weirdish results.
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com