Competition: What’s your favorite Redgate tool? Enter now.

Incorrect Stored Procedure Name generated

MarcoEnsingMarcoEnsing Posts: 2
edited June 20, 2007 6:27PM in SQL Compare Previous Versions
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

Comments

  • 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&#40;'original'&#41; is not null begin
    	drop procedure original
    end
    go
    
    create procedure original
    as
    
    select 1
    
    go
    
    select	&#91;text&#93;
    from	syscomments
    where	id = object_id&#40;'original'&#41;
    
    go
    
    if object_id&#40;'notoriginal'&#41; is not null begin
    	drop procedure notoriginal
    end
    go
    
    
    exec sp_rename 'original', 'notoriginal'
    go
    
    
    
    select	&#91;text&#93;
    from	syscomments
    where	id = object_id&#40;'notoriginal'&#41;
    
    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 DonahueBrian Donahue Posts: 6,590 New member
    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!
  • 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 DonahueBrian Donahue Posts: 6,590 New member
    SQL Compare version 6, so I've been told, will reconcile this issue.
    *And the crowd cheered!*
Sign In or Register to comment.