sp_rename failed - wtf! HELP!!
-
Ok issued
exec sp_rename dbo.proc1, 'dbo.proc1_BAK'
exec sp_rename 'dbo.proc1_new', 'dbo.proc1'
Both have disappeared from object browser, sp_helptext and sysobjects.
Looking in syscomments for proc1 I can find both procedures with their original definitions. The ids in syscomments do not track back to sysobjects, nor does object_name(id) provide an answer.
so....WTF¬!!!
also I ran back in dbo.proc1_new, and exec sp_rename 'dbo.proc1_new', 'dbo.proc1' now fails with
Server: Msg 15335, Level 11, State 1, Procedure sp_rename, Line 342
Error: The @newname value 'dbo.proc1' is already in use as a object name and would cause a duplicate that is not permitted.
Any ideas on htf to fix this?
Apparently it exists in the information schemas. :s
-
Ok, insanity passed...
looks like sp_rename 'dbo.proc1', 'dbo.proc1_bak'
decided to rename dbo.proc1, dbo.dbo.proc1_bak ijn sysobjects, and render it totally unusable.
Judicious direct updating of the system catalog remedied the situation.
But, just, really. wtf?
-
I believe when you renamed this SP to "dbo.proc1_bak' Sql Server added the SCHEMA identifier of DBO to the beginning of the name you keyed in, thus giving you 'dbo.dbo.proc1_bak'. This actually makes sense, as you are either an SA or DBO role on this server/database.
-
A little late now, but in the future please check the BOL for correct syntax. For SP_RENAME it says that the new name MUST be a ONE part name. In other words, no owner identifier.
Also, the old name must be table.column or table.index. Again, no owner identifier.
-SQLBill
-
Which is all very well, but one would imagine it would handle it a bit cleaner. Rather than happily renaming it to an unusable value.
-
Another downside of sp_rename for stored procs is that it does not alter the create-text !!!!
So when you script your db using SQLDMO or other stuff to readout the syscomments values, it will still have the "old" create statement !!
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data and code to get the best help- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply