|
|
Posted by Simon Hayes on 09/13/05 23:29
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
Navigation:
[Reply to this message]
|