How can we help you today? How can we help you today?

BUG: Renamed Objects have original name in DDL Pane

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
0

Comments

5 comments

Sort by
  • 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
    David Atkinson
    0
  • DWolford1234
    SSMS 2005 9.00.4035.00
    DWolford1234
    0
  • 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
    David Atkinson
    0
  • 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
    DWolford1234
    0
  • 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
    David Atkinson
    0

Add comment

Please sign in to leave a comment.