Output stored procedures as text problem

[laurenq uantrell]

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

[Simon Hayes]

laurenq uantrell wrote:

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

See sp_rename in Books Online - it mentions that renaming a proc does
not change the text in syscomments, so you may have problems scripting
the proc; the solution is to drop and create instead of renaming.

As a further comment, I suggest you check out the SQLDMO Script method -
it will give you a lot more control over the output and scripting
options when you generate DDL for an object.

Simon

[John Bell]

Hi

From sp_rename documentation in BOL:
"Renaming a stored procedure, view or trigger will not change the name of
the corresponding object name in the syscomments table. This may result in
problems generating a script for the object as the old name will be inserted
from the syscomments table into the CREATE statement. For best results, do
not rename these object types. Instead, drop and re-create the object by its
new name."

John

"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

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

[laurenq uantrell]

Ouch! This wan't a problem until I started outputting as text.

[John Bell]

Hi

Hopefully this is a one off and you will be working with a source code
control system, therefore depending on the need for the older versions you
may want to delete or recreate them manually.

John

"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Ouch! This wan't a problem until I started outputting as text.