February 16, 2002 at 6:57 pm
As a newcomer to SQL Server, I am confused about why DMO's "script" method of stored procedure objects is returning me the prior name of a stored procedure rather than it's current name.
I had a stored procedure named "abc". I renamed the procedure by doing the following:
(1) From VB6, opening a data link to the database
(2) locating the sp in VB's list of sp's.
(3) opening the code for the sp
(4) typing a new name for the sp (that is changing "ALTER PROCEDURE abc" to "ALTER PROCEDURE def".
I realize now, that I should have replaced "ALTER PROCEDURE abc" with "CREATE PROCEDURE def". But I didn't, and I'm confused about where SQL Server is retaining the old name.
If I open the code for the sp, I quite clearly see "ALTER PROCEDURE def", but if I use SQL-DMO to call the .Script method of the sp object, I get the sp's old name, and not its current name.
Here is the VB code I am using:
Dim params As Long
params = SQLDMOScript_Default Or _
SQLDMOScript_AppendToFile Or _
SQLDMOScript_Drops Or _
SQLDMOScript_IncludeHeaders
Dim sp As SQLDMO.StoredProcedure
For Each sp In db.StoredProcedures
Debug.Print sp.Name
Debug.Print Mid$(sp.Text, 1, 40)
sp.Script params, "C:\sp.sql", _
SQLDMOScript2_70Only
Next
When run, I get this kind of thing:
Current Name: def
Current Text: CREATE Procedure abc
even though the current code for the sp shows as:
"ALTER PROCEDURE def".
Where is SQL Server keeping this old name ("abc") for the sp, and how do I get around this problem?
Thanks,
Tim Griffin
February 18, 2002 at 10:33 am
You should have both procedures. When you open the code, the old object, still exists on the server. When you recompile into the new object, you are creating a new object. The old one still exists.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply