Red Gate forums :: View topic - BUG: Renamed Objects have original name in DDL Pane
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Search 1
SQL Search 1 forum

BUG: Renamed Objects have original name in DDL Pane

Search in SQL Search 1 forum
Post new topic   Reply to topic
Jump to:  
Author Message
DWolford1234



Joined: 22 Nov 2005
Posts: 34
Location: Orlando, FL U.S.

PostPosted: Mon Mar 08, 2010 9:31 pm    Post subject: BUG: Renamed Objects have original name in DDL Pane Reply with quote

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
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1120
Location: Twitter: @dtabase

PostPosted: Mon Mar 08, 2010 10:48 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
DWolford1234



Joined: 22 Nov 2005
Posts: 34
Location: Orlando, FL U.S.

PostPosted: Mon Mar 08, 2010 10:59 pm    Post subject: Reply with quote

SSMS 2005 9.00.4035.00
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1120
Location: Twitter: @dtabase

PostPosted: Mon Mar 08, 2010 11:41 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
DWolford1234



Joined: 22 Nov 2005
Posts: 34
Location: Orlando, FL U.S.

PostPosted: Tue Mar 09, 2010 5:03 pm    Post subject: Reply with quote

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. Smile

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
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1120
Location: Twitter: @dtabase

PostPosted: Tue Mar 09, 2010 6:07 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group