Retrieve Stored proc name

**Rich P** · Nov 13 '05, 08:49 AM

Re: Retrieve Stored proc name

If you are in Access you can do this:

Sub GetQryInfo()
Dim DB As Database, QD As QueryDef, str1 As String
Set DB = CurrentDB
For Each QD In DB.QueryDefs
Debug.Print QD.Name
Debug.Print QD.Sql
Next
End Sub

This will print out the names of all the queries in an Access mdb and
will also print out the sql string inside each query. If you are doing
this from a VB6 app, then just make a reference to the Microsoft Access
Object Library. You can then use

Dim app As Access.Applicat ion
Set app = CreateObject("A ccess.Applicati on")
or
Set app = GetObject(, "Access.Applica tion") if Access is already running

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

**pap** · Nov 13 '05, 08:49 AM

Re: Retrieve Stored proc name

Not sure that will retrieve stored procedures from a server.

peter walker

"Rich P" <rpng123@aol.co m> wrote in message news:4224fc2e$1 _1@127.0.0.1...[color=blue]
> If you are in Access you can do this:
>
> Sub GetQryInfo()
> Dim DB As Database, QD As QueryDef, str1 As String
> Set DB = CurrentDB
> For Each QD In DB.QueryDefs
> Debug.Print QD.Name
> Debug.Print QD.Sql
> Next
> End Sub
>
> This will print out the names of all the queries in an Access mdb and
> will also print out the sql string inside each query. If you are doing
> this from a VB6 app, then just make a reference to the Microsoft Access
> Object Library. You can then use
>
> Dim app As Access.Applicat ion
> Set app = CreateObject("A ccess.Applicati on")
> or
> Set app = GetObject(, "Access.Applica tion") if Access is already running
>
>
>
> Rich
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

**Lyle Fairfield** · Nov 13 '05, 08:49 AM

Re: Retrieve Stored proc name

jw56578@gmail.c om wrote in news:1109711904 .529069.185500
@f14g2000cwb.go oglegroups.com:
[color=blue]
> what is a common method, if any, to retrieve stored query information
> from Access. MDAC does not support OleDbSchemaGuid .Procedure_Colu mns. I
> need stored procedure name and paramter information, can you get this?[/color]

You can get the ID and Name of SQL procedures with a T-SQL string like this.

"SELECT ID, name FROM SysObjects WHERE xtype='P'"

You can use the IDs returned to get the T-SQL of the Sprocs
with

"SELECT text FROM SysComments WHERE ID=" & ID

this assumes you have permissions and some programming ability.

**Trevor Best** · Nov 13 '05, 08:49 AM

Re: Retrieve Stored proc name

jw56578@gmail.c om wrote:[color=blue]
> what is a common method, if any, to retrieve stored query information
> from Access. MDAC does not support OleDbSchemaGuid .Procedure_Colu mns. I
> need stored procedure name and paramter information, can you get this?
>[/color]

BOL: sp_helptext

--
This sig left intentionally blank

**Rich P** · Nov 13 '05, 08:50 AM

Re: Retrieve Stored proc name

Wasn't sure if you wanted queryDef info or SP info from Sql Server.
Here is how to get SP info from Sql Server from Access

Make a reference to Microsoft SQLDMO object Library then

Sub GetSPList()
Dim oSqlSrv As SQLDMO.SQLServe r
Dim sp As SQLDMO.StoredPr ocedure
Dim Sps As SQLDMO.StoredPr ocedures
Dim i As Integer, j As Integer
Dim str1 As String, str2 As String

Set oSqlSrv = New SQLDMO.SQLServe r
'--set to false for Sql Server Authentication
oSqlSrv.LoginSe cure = False
oSqlSrv.Connect "yourSqlServer" , "sa", "password"

Set Sps = oSqlSrv.Databas es("yourSqlDB") .StoredProcedur es
For Each sp In Sps
If sp.SystemObject = False Then
Debug.Print sp.Name
Debug.Print sp.Script
End If
Next
End Sub

This will work remotely from Access from a workstation where your SQl SB
does not reside on the same computer. The only catch is that you have
to have the Microsoft SQLDMO object library loaded on that workstation
for this code to run (and, of course, permissions, etc).

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

**jw56578@gmail.com** · Nov 13 '05, 09:10 AM

Re: Retrieve Stored proc name

so how would this be done through ado.net?

**MGFoster** · Nov 13 '05, 09:10 AM

Re: Retrieve Stored proc name

jw56578@gmail.c om wrote:[color=blue]
> so how would this be done through ado.net?[/color]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Here's a C# example from _ADO.NET in a Nutshell_; pub: O'Reilly:

using System;
using System.Data.SQL Client;

public class GetRoutineList
{
public static void Main()
{
// Set up connection to SQL Server sample DB "Northwind"
string connectionStrin g = "Data Source=localhos t;" +
"Initial Catalog=Northwi nd;Integrated Security=SSPI";
string SQL = "SELECT ROUTINE_TYPE, ROUTINE_NAME FROM " +
"INFORMATION_SC HEMA.ROUTINES";

// Create ADO.NET objects
SqlConnection con = new SqlConnection(c onnectionString );
SqlCommand cmd = new SqlCommand(SQL, con);
SqlDataReader r;

// Execute the query
try
{
con.Open();
r = cmd.ExecuteRead er();
while (r.Read())
{
Console.WriteLi ne(r[0] + ": " + r[1]);
}
}
finally
{
con.Close();
}
}
}

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjYSUIechKq OuFEgEQKBZACeLZ xPLgQ70WoiFoS6/vgb99XcLtUAn2Zt
yNO/M6Au4Uzmpf/cUJM37ftv
=knO8
-----END PGP SIGNATURE-----

**Rich P** · Nov 13 '05, 09:11 AM

Re: Retrieve Stored proc name

I believe SQLDMO is the only way to get DB info from Sql Server (2000)
from an external app. I only run the sp's from ADO.Net. Anyway, SqlDMO
is com based. So even if you are in a VB.Net/C# app, you still have to
make a com reference to SqlDMO. Matter of fact, since SqlDMO is com
based, I don't think ADO.Net has any features for extracting info from
Sql Server. Maybe ADO.Net2 can do SqlDMO stuff with Sql Server2005
(Yukon is it?). But ADO.Net2 is way off, next year maybe (I am
anxiously waiting).

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

**jw56578@gmail.com** · Nov 13 '05, 09:13 AM

Re: Retrieve Stored proc name

But how is this done for Access?
Let me clarify: How do you retrieve the stored queries( as in the
names, sql, and parameter information) from a MDB file in asp.net using
c# or vb.net? Its easy to do in SQL server, but is it possible in a MDB
file.

**MGFoster** · Nov 13 '05, 09:13 AM

Re: Retrieve Stored proc name

jw56578@gmail.c om wrote:[color=blue]
> But how is this done for Access?
> Let me clarify: How do you retrieve the stored queries( as in the
> names, sql, and parameter information) from a MDB file in asp.net using
> c# or vb.net? Its easy to do in SQL server, but is it possible in a MDB
> file.
>[/color]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I tried this on my system & was able to see the Tables & Procedures
schemas. There's supposed to be a OleDbSchemaGuid .Views, but I got an
error when I tried it. In the Views schema table there is a
VIEW_DEFINITION column that holds the SQL. Perhaps the JET 4.0 engine
can't provide this information.

According to my ADO reference manual, SELECT queries are Views and
action and crosstab queries are Procedures.

=== begin C# code ===
using System;
using System.Data;
using System.Data.Ole Db;

public class GetSchema
{
public static void Main()
{
string connectionStrin g = @"Provider=Micr osoft.Jet.OLEDB .4.0;" +
@"Data Source=C:\Docum ents and Settings\Owner\ My Documents" +
@"\Databases\My Databases\Acces sXP\TestBed.mdb ";

// Create ADO.NET objects
OleDbConnection con = new OleDbConnection (connectionStri ng);

DataTable schema;

// Execute the query
try
{
con.Open();
// Use OleDbSchemaGuid .Procedures to get Action queries
schema = con.GetOleDbSch emaTable(OleDbS chemaGuid.Proce dures,
new object[] {null, null, null, null});
}
finally
{
con.Close();
}

// Display the schema table.
if (schema != null)
foreach (DataRow row in schema.Rows)
{
// For tables/Views use the following
/* Console.WriteLi ne(row["TABLE_TYPE "] + ": " +
row["TABLE_NAME "]) ;
*/
// For Procedures use the following
Console.WriteLi ne(row["PROCEDURE_NAME "] + ":\n" +
row["PROCEDURE_DEFI NITION"]);

}
}
}
=== end code ===

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQji0jIechKq OuFEgEQJrFwCgt1 eE6UY6STEaH5Y1P emRYb4IigIAoPcY
b+h4bqf4BHAFhx9 rbl5ZXkJm
=zIqC
-----END PGP SIGNATURE-----

**jw56578@gmail.com** · Nov 13 '05, 09:15 AM

Re: Retrieve Stored proc name

I had done that and yes JET 4.0 engine does not provide this
information. so im out of luck, oh well.