473,554 Members | 3,237 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Retrieve Stored proc name

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?

Nov 13 '05 #1
11 2672
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!
Nov 13 '05 #2
pap
Not sure that will retrieve stored procedures from a server.

peter walker

"Rich P" <rp*****@aol.co m> 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.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!

Nov 13 '05 #3
jw*****@gmail.c om wrote in news:1109711904 .529069.185500
@f14g2000cwb.go oglegroups.com:
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?


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.
Nov 13 '05 #4
jw*****@gmail.c om wrote:
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?


BOL: sp_helptext

--
This sig left intentionally blank
Nov 13 '05 #5
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!
Nov 13 '05 #6
so how would this be done through ado.net?

Nov 13 '05 #7
jw*****@gmail.c om 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.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-----
Nov 13 '05 #8
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!
Nov 13 '05 #9
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.

Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
5109
by: xAvailx | last post by:
I have a requirement that requires detection of rows deleted/updated by other processes. My business objects call stored procedures to create, read, update, delete data in a SQL Server 2000 data store. I've done a fair amount of research on concurrency handling in newsgroups and other resources. Below is what I've come up as a standard for...
4
3042
by: Learner | last post by:
Hi there, I have a storec proc that schedules a Sql job and finally it returns 0 then it was successfull and if it returns 1 then its unsuccessful. Now when i run the stored proc in the query analyzer its returning SQLServerAgent is not currently running so it cannot be notified of this action. Stored Procedure:...
6
2152
by: Paul M | last post by:
Hi All, I'm currently writing a z/OS DB2 Stored Proc in C, using an example from the IBM Stored Procedure guide (SG24-7083-00). The database calls to read and update the database work fine...however, I can't seem to figure out how to pass parms to the C Program. The compile, bind, and run using DB2BATCH all work fine, however, when I...
4
4407
by: hicks | last post by:
I'm trying to invoke a DB2 stored procedure. The stored proc is coded in C and compiled to a shared library, which has been placed in the <DB2 dir>/functions directory. The platform is Solaris. >From the debug log it seems that the stored procedure can't be found, although I don't know why. Using the control centre, I can see that the...
3
13487
by: Fran | last post by:
(SQL 2005) I'm looking to create a stored procedure to first "select name from sys.databases where name like '%site'" then pass each name to the following using some kind of loop "USE @name select name from sys.tables where type = 'U'" I tried a while statement, but the master sys.databases recordset doesn't change.. It's the loop I...
0
2542
by: ravindrag | last post by:
Hi, I am getting error SQL1131N during sqlj.install_jar(...). There is no useful message in the diag.log (even with diag level 4). I am giving the diag.log entries at the end of this posting (would have been ideal if there was an option to attach the file). command: db2 call sqlj.install_jar('file:/home/xyz/abc.jar','def.abc') response:...
0
2579
by: db2user24 | last post by:
I'm trying to invoke a DB2 stored procedure. The stored proc is coded in C and compiled to a shared library, which has been placed in the <DB2 dir>/functions directory. The platform is Linux (using 64 bit DB2 UDB). From the debug log it seems that the stored procedure can't be found, although I don't know why. I can see that the procedure name...
4
1854
by: davinski | last post by:
Hello, it's been a while since my last post, hope everyone is fine :P I'm stuck with what seems to be a simple task, but I'm getting confused on how to complete this. Basically, I have been given a stored procedure which nests itself within itself and uses a temporary table to store the data while writing. The nested stored procedure is...
0
1978
by: mirandacascade | last post by:
Questions toward the bottom of the post. Situation is this: 1) Access 97 2) SQL Server 2000 3) The Access app: a) sets up pass-thru query b) .SQL property of querydef is a string, the contents of which comprise the call to a stored proc
0
7596
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7519
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7798
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8039
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
6140
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5431
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5152
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3556
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3545
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.