Incorrect Stored Procedure Name generated

MarcoEnsing

Hi,

When a stored procedure in Sql 2005 is renamed, the Sql Compare generates the old name to create the stored proc. but uses the new name as message that the proc is generated!

Is this a known issue? Is there a patch? Are we doing something wrong?

Regards,

Marco

/*
Script created by SQL Compare from Red Gate Software Ltd at 4/12/2006 8:26:09 AM
*/
PRINT N'Creating [dbo].b]CreditRatingSelectAll[/b'
GO
CREATE proc CreditRatingList
AS
SELECT b]CreditRatingID[/b
,[Name]
,[Description]
FROM [dbo].[CreditRating] where [CreditRatingID] <> 0

tferreira

My guess is that SQL Compare is using the syscomments table to generate the procedure script.

When you rename a procedure , the original procedure is removed from sysobjects and a new object is created in sysobjects with the new name and a new object_id. The object_id is related to syscomments.id which stores the text of the original stored procedure.

The problem occurs when the syscomments text column fails to get updated with the rename. When doing a sp_rename, or renaming from within Management Studio, the syscomments table text data will exist as it did under the orginal object_id.

An example can be seen below:

if object_id('original') is not null begin
	drop procedure original
end
go

create procedure original
as

select 1

go

select	[text]
from	syscomments
where	id = object_id('original')

go

if object_id('notoriginal') is not null begin
	drop procedure notoriginal
end
go


exec sp_rename 'original', 'notoriginal'
go



select	[text]
from	syscomments
where	id = object_id('notoriginal')

go

In order to avoid this issue, you need to script the renamed stored procedure and execute it against the database.

So, the renaming procedure would be as follows:

1. rename in management studio, or execute sp_rename
2. in management studio, right click and script to a new window.
3. execute the script.

syscomments should now be updated with the proper text.

Brian Donahue

You're right. SQL Compare is getting the script from syscomments. Enterprise manager and sp_rename don't update syscomments. This causes no end of problems for SQL Compare when updating views and stored procedures!

tonyb

I've written a SQL Query that helps you identify the items in sysobjects and syscomments that are out of sync as a result of renaming an object.

Run the query below to identify the items, then generate create scripts for each of the relevant items, delete them and then recreate them with script. This should bring them back into line again.
There may be more automated ways of doing this, but it works.

USE dbname
GO

SELECT so.id, so.name, so.type, sc.text, PATINDEX ( '%' + so.name + '%' , sc.text )
FROM
sysobjects as so
join syscomments as sc on so.id = sc.id
WHERE PATINDEX ( '%' + so.name + '%' , sc.text ) < 1
AND sc.colid = 1
and so.type <> 'D'
ORDER BY so.name

Brian Donahue

SQL Compare version 6, so I've been told, will reconcile this issue.
*And the crowd cheered!*