Retrieve Stored proc name

[jw56578]

what is a common method, if any, to retrieve stored query information
from Access. MDAC does not support OleDbSchemaGuid.Procedure_Columns. I
need stored procedure name and paramter information, can you get this?

[Rich P]

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.Application
Set app = CreateObject("Access.Application")
or
Set app = GetObject(, "Access.Application") if Access is already running

Rich

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

[pap]

Not sure that will retrieve stored procedures from a server.

peter walker

"Rich P" <rp*****@aol.com> wrote in message news:42**********@127.0.0.1...

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.Application
Set app = CreateObject("Access.Application")
or
Set app = GetObject(, "Access.Application") if Access is already running

Rich

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

[Lyle Fairfield]

jw*****@gmail.com wrote in news:1109711904.529069.185500
@f14g2000cwb.googlegroups.com:

what is a common method, if any, to retrieve stored query information
from Access. MDAC does not support OleDbSchemaGuid.Procedure_Columns. I
need stored procedure name and paramter information, can you get this?

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]

jw*****@gmail.com wrote:

what is a common method, if any, to retrieve stored query information
from Access. MDAC does not support OleDbSchemaGuid.Procedure_Columns. I
need stored procedure name and paramter information, can you get this?

BOL: sp_helptext

--
This sig left intentionally blank

[Rich P]

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.SQLServer
Dim sp As SQLDMO.StoredProcedure
Dim Sps As SQLDMO.StoredProcedures
Dim i As Integer, j As Integer
Dim str1 As String, str2 As String

Set oSqlSrv = New SQLDMO.SQLServer
'--set to false for Sql Server Authentication
oSqlSrv.LoginSecure = False
oSqlSrv.Connect "yourSqlServer", "sa", "password"

Set Sps = oSqlSrv.Databases("yourSqlDB").StoredProcedures
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]

so how would this be done through ado.net?

[MGFoster]

jw*****@gmail.com wrote:

so how would this be done through ado.net?

-----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.SQLClient;

public class GetRoutineList
{
public static void Main()
{
// Set up connection to SQL Server sample DB "Northwind"
string connectionString = "Data Source=localhost;" +
"Initial Catalog=Northwind;Integrated Security=SSPI";
string SQL = "SELECT ROUTINE_TYPE, ROUTINE_NAME FROM " +
"INFORMATION_SCHEMA.ROUTINES";

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

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

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

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

iQA/AwUBQjYSUIechKqOuFEgEQKBZACeLZxPLgQ70WoiFoS6/vgb99XcLtUAn2Zt
yNO/M6Au4Uzmpf/cUJM37ftv
=knO8
-----END PGP SIGNATURE-----

[Rich P]

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]

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]

jw*****@gmail.com wrote:

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.

-----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.OleDb;

public class GetSchema
{
public static void Main()
{
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source=C:\Documents and Settings\Owner\My Documents" +
@"\Databases\MyDatabases\AccessXP\TestBed.mdb";
// Create ADO.NET objects
OleDbConnection con = new OleDbConnection(connectionString);

DataTable schema;

// Execute the query
try
{
con.Open();
// Use OleDbSchemaGuid.Procedures to get Action queries
schema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures ,
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.WriteLine(row["TABLE_TYPE"] + ": " +
row["TABLE_NAME"]) ;
*/
// For Procedures use the following
Console.WriteLine(row["PROCEDURE_NAME"] + ":\n" +
row["PROCEDURE_DEFINITION"]);

}
}
}
=== end code ===

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

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

iQA/AwUBQji0jIechKqOuFEgEQJrFwCgt1eE6UY6STEaH5Y1PemRYb 4IigIAoPcY
b+h4bqf4BHAFhx9rbl5ZXkJm
=zIqC
-----END PGP SIGNATURE-----

[jw56578]

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