export sql query to a table.

[Guest]

Hi

Thanks in advance.

I have an API that looks at a sql database.

Is it possible to save all the views and stored procedures sql wording into
a table.

with a memo field storing the sql code. Of course I would have to loop
through all to get each one.

Look forward to receiving your help.

Trev

 

[Arvin Meyer [MVP]]

Depending upon the version of SQL-Server, there is a utility to save the
entire database structure and definition as text files. They all have a .sql
extension, but can be opened with notepad. If you're just interested the
text of the queries, open each of them in and either save them in individual
text files or in a memo field.

If your code allows you to loop through them, use an Insert (Append) query
to save each one to a row in a memo field.

 

[Guest]

Thanks for that.

I do not have any sql tools (Progs) I only have the API.

That is why I was looking for a code to use in the API to read the SQL code.

I want to save them and since I have over 500 views and stored procedures I
did not want to do it the long way.

All i need is the code to save 1 of each and I can do the rest.


I tried querydef but it fail without even finding the query name that's why
I need help.

Trev

 

[Arvin Meyer [MVP]]

Thanks for that.

I do not have any sql tools (Progs) I only have the API.

That is why I was looking for a code to use in the API to read the SQL
code.

I want to save them and since I have over 500 views and stored procedures
I
did not want to do it the long way.

All i need is the code to save 1 of each and I can do the rest.


I tried querydef but it fail without even finding the query name that's
why
I need help.

Trev

 

[Arvin Meyer [MVP]]

It might ne a good idea to ask in a SQL-Server group. In an Access mdb, you
can read the sql into a text file, then import those textfiles back into a
table's memo field. QueryDef does work for those:

For i = 0 To dbs.QueryDefs.Count - 1
Application.SaveAsText acQuery, dbs.QueryDefs(i).Name, "C:\Document\" &
dbs.QueryDefs(i).Name & ".txt"
Next i
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Bob Quintal]

It might ne a good idea to ask in a SQL-Server group. In an Access
mdb, you can read the sql into a text file, then import those
textfiles back into a table's memo field. QueryDef does work for
those:

For i = 0 To dbs.QueryDefs.Count - 1
Application.SaveAsText acQuery, dbs.QueryDefs(i).Name,
"C:\Document\" &
dbs.QueryDefs(i).Name & ".txt"
Next i

this works too:
Dim dbs As Database, qdfs As QueryDefs, qdf As QueryDef
Set dbs = CurrentDb
Set qdfs = dbs.QueryDefs

For Each qdf In qdfs
Debug.Print qdf.Name; qdf.SQL
Next

I've even just put the QueryDefs.SQL into the memo field.

 

[Arvin Meyer [MVP]]

Thanks, you've reminded me of some code I wrote years ago. I dug it out of
an Access 97 database that I haven't looked at in years:

Public Function WriteSQL2Table()
'**********************************************
' Name: WriteSQL2Table
' Purpose: Document Queries
' Author: Arvin Meyer
' Date: 10/16/1999
' Comment: Requires table tblQueryStrings with fields,
' QueryName, QueryType, CreateDate, LastUpdate,
' QuerySQL
'**********************************************
On Error Resume Next

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set rst = db.OpenRecordset("tblQueryStrings", dbOpenDynaset)

With rst
For Each qdf In db.QueryDefs
.AddNew
!QueryName = qdf.Name
!QueryType = qdf.Type
!CreateDate = qdf.DateCreated
!LastUpdate = qdf.LastUpdated
!QuerySQL = qdf.SQL
.Update
Next
End With

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Set qdf = Nothing

End Function