Output Stored Procedures as text problem

I'm using the function below to output all of my stored procedures into

a text file. Fice, except that the output file does not reflect the
names of the stored procedures correctly if the name has been changed.


For example, I create a stored procedure named: "sp123" and then rename

it to "sp123DELETE" or "sp123 DELETE" or "sp123OLD" or "sp123 OLD" and
what I end up with is four entries in the output file all having the
stored procedure name "sp123."


I stop the service and restart before outputting the file.


Any help is appreciated.


lq


Function ExportSP(myPath As String)


Dim objSQLServer As New SQLDMO.SQLServer
Dim dbs As New SQLDMO.Database
Dim sp As SQLDMO.StoredProcedure
Dim sptext As String


objSQLServer.Connect <Servername>, <Username>, <Password>
Set dbs = objSQLServer.Databases(<databasename>)


Open myPath For Output As #1


For Each sp In dbs.StoredProcedures
sptext = sp.Text


Print #1, sptext & _
vbCrLf & vbCrLf & vbCrLf & _
"*******" & _
vbCrLf & vbCrLf & vbCrLf
Next


End Function

.

Relevant Pages

  • Output stored procedures as text problem
    ... I'm using the function below to output all of my stored procedures into ... except that the output file does not reflect the ... Dim objSQLServer As New SQLDMO.SQLServer ... Dim dbs As New SQLDMO.Database ...
    (comp.databases.ms-sqlserver)
  • Return Results of sp_helptext via ADO
    ... I am trying to write code to document stored procedures for an Access ... Dim daorsStoredProcs As DAO.Recordset ... Dim strSQLStoredProcBase As String ... Set adoconnStoredProcText = New ADODB.Connection ...
    (microsoft.public.data.ado)
  • Re: ADP - ADO beginner Search Records with a Sp ~ help please
    ... need additional stored procedures for updating/adding/deleting. ... > Dim MyMsgBox As VbMsgBoxStyle ... >Dim cnn As ADODB.Connection ... >Set rst = New ADODB.Recordset ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Losing characters with sql parameterized insert query
    ... We use Stored Procedures for all of ... our database ops. ... My guess would be that when you create a parameterized query, ... > Dim cmdSqlCommand As SqlCommand ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Moving User Defined Functions between Databases
    ... Stefan Berglund gave a great answer. ... You can include all your functions, triggers, and stored ... user defined functions and stored procedures, ... Dim oServer As SQLDMO.SQLServer2 ...
    (microsoft.public.sqlserver.programming)