BUG: Renamed Objects have original name in DDL Pane

A free Management Studio add-in to search for SQL in your databases

Moderators: JonathanWatts, David Atkinson, Anu Deshpande, Paul Stephenson, Michelle Taylor, Mike Upton, Adam, justin.caldicott

BUG: Renamed Objects have original name in DDL Pane

Postby DWolford1234 » Mon Mar 08, 2010 9:31 pm

I stumbled acrossed this earlier, was able to reproduce the issue, and thought I'd share.

I haven't tested if this is true with all object types, but it was reproducable with Stored Procedures.

First, I create a simple SP:
CREATE PROCEDURE dantest1
AS
SELECT *
FROM dbo.ImportLog
WHERE date = '03/01/2010'

Then, I used the Object explorer to right click the dantest1 proc and clicked rename. I renamed it to dantest1_old.

Next I run the Create script again:
CREATE PROCEDURE dantest1
AS
SELECT *
FROM dbo.ImportLog
WHERE date = '03/01/2010'


Now, if I do a search on 03/01/2010, both dantest1 and dantest1_old are returned in the results.

However, if you look at the DDL, they both say
CREATE PROCEDURE dantest1

Now, If i run an
ALTER PROCEDURE dantest1_old
AS
SELECT *
FROM dbo.ImportLog
WHERE date = '03/01/2010'

everything works.

Somehow, using the explorer to rename the objects isn't updating a reference that SQL Search uses to build the DDL.

I'll let you see if it happens on tables, etc.

Thanks for the tools!
Dan
DWolford1234
 
Posts: 30
Joined: Tue Nov 22, 2005 2:31 pm
Location: Orlando, FL U.S.

Postby David Atkinson » Mon Mar 08, 2010 10:48 pm

I don't think SQL Search is at fault here. I think you've stumbled across Microsoft's sp_rename bug whereby it doesn't update the syscomments entry with the new definition. I'm fairly sure that this was fixed in SSMS 2008. I thought this was fixed in a later edition of SSMS 2005, although I may be wrong there. Can you confirm which version you're using?

David Atkinson
Red Gate Software
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby DWolford1234 » Mon Mar 08, 2010 10:59 pm

SSMS 2005 9.00.4035.00
DWolford1234
 
Posts: 30
Joined: Tue Nov 22, 2005 2:31 pm
Location: Orlando, FL U.S.

Postby David Atkinson » Mon Mar 08, 2010 11:41 pm

Hmmm... maybe it was only fixed by Microsoft in SSMS 2008?

I've been trying to search in Google for "sp_rename bug SSMS fix" and suchlike to find a fix reference but to no avail. I did, however, come across a blog posting that explains the issue:

http://www.adrianbanks.co.uk/?p=24

I think the conclusion is that the best approach is to avoid the rename feature. Red Gate's SQL Refactor has a 'Smart Rename' feature that does this properly (as well as renaming all references throughout the schema), although this is a commercial tool.

David
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby DWolford1234 » Tue Mar 09, 2010 5:03 pm

I agree. It's interesting that Microsoft obviously saw the problem, because they worked around it when you have SSMS create a script.

Oh well. Thankfully, I just purchased the Developer Bundle, which includes SQL Refactor, so I'm good to go. :)

Unfortunately, I think you'll have others seeing this as a problem, even though it may not, technically, be your fault.

Good luck.

Thanks,
Dan
DWolford1234
 
Posts: 30
Joined: Tue Nov 22, 2005 2:31 pm
Location: Orlando, FL U.S.

Postby David Atkinson » Tue Mar 09, 2010 6:07 pm

We've already had a number of SQL Compare users point it out to us, so we've built in a feature in SQL Compare that corrects the problem after it synchronizes!

David
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase


Return to SQL Search 1

Who is online

Users browsing this forum: No registered users and 0 guests