BUG: Renamed Objects have original name in DDL Pane

DWolford1234

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

David Atkinson

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

DWolford1234

SSMS 2005 9.00.4035.00

David Atkinson

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

DWolford1234

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

David Atkinson

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