sp_rename Issue, I wasn't aware of

  • I came across an issue, I'd like to share with you concerning sp_Rename. (I know there are lots and lots of articles about how sp_rename affects your database, but I did not realize it can break your application down with ease.)

    I had to point several objects to a new linked server and needed to check if everything works ok afterwards, because I knew i had conflicting collations with several databases.

    so I used sp_refreshsqlmodule to find that issues. everything was ok, until i realised the application using the database did not work any more. Here's a little script to illustrate what i found out. Check out and be aware.

    [edit]: This applies to SQL 2008R2 sp1, sorry wrong forum i think.

    CREATE PROCEDURE dbo.testProc1

    AS

    SELECT 2

    ;

    GO

    CREATE PROCEDURE dbo.TestProc2

    AS

    SELECT 1

    ;

    GO

    -- No1 Returns 2

    -- No2 Returns 1

    GO

    EXEC dbo.TestProc1 --[edit] returns 2

    EXEC dbo.TestProc2 --[edit] returns 1

    GO

    -- Rename , so 1 returns 1 and 2 returns 2

    EXEC sys.sp_rename @objname = N'dbo.testproc1' , @newname = 'TestProc2xx'

    EXEC sys.sp_rename @objname = N'dbo.testproc2' , @newname = 'TestProc1'

    EXEC sys.sp_rename @objname = N'dbo.testproc2xx' , @newname = 'TestProc2'

    GO

    EXEC dbo.TestProc1 --[edit] returns 1

    EXEC dbo.TestProc2 --[edit] returns 2

    GO

    -- here's the problem. I used refreshmodule to find out problems with procedures

    -- metadata refresh!!

    EXEC sys.sp_refreshsqlmodule @name = N'dbo.TestProc1'

    EXEC sys.sp_refreshsqlmodule @name = N'dbo.TestProc2'

    -- And then things went wrong, because now both return 1!!

    GO

    EXEC dbo.TestProc1 --[edit]Returns 1 !!!!

    EXEC dbo.TestProc2 --[edit] Returns 1

    GO

  • Sorry, but I don't see what's wrong. Everything works fine for me.

    Can you please clarify what you're getting?

    -- Gianluca Sartori

  • sorry, i did not make that clear!

    the script works fine, but after rename and refreshmodule both of the Stored Procedures return 1

    and not as expected 1 and 2

  • Not for me. Everything works as expected.

    Which version are you on?

    -- Gianluca Sartori

  • Really?

    i'm on:

    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)

    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    seems there are some more issues with sp_refreshsqlmodule

    see : http://www.sqlservercentral.com/Forums/Topic1425656-391-1.aspx

  • JohnyRotten (3/31/2015)


    Really?

    i'm on:

    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)

    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    seems there are some more issues with sp_refreshsqlmodule

    see : http://www.sqlservercentral.com/Forums/Topic1425656-391-1.aspx

    Hmm, ok. I can reproduce it in 2008R2 10.50.4033 (SP2).

    Since this is the 2012 forum, I tried it on 2012 and the issue is fixed there.

    I'm downloading 2008R2 SP3 to see if it fixes. Will keep you posted.

    -- Gianluca Sartori

  • Nope, SP3 doesn't fix.

    I guess you're out of luck then. SP3 is the final service pack for 2008R2 and no more updates are coming for this version.

    -- Gianluca Sartori

  • This issue has been reported and fixed for 2012, but not backported to 2008 and 2008R2.

    See here: https://connect.microsoft.com/SQLServer/Feedback/Details/757526

    -- Gianluca Sartori

  • Thanks, that made some problems yesterday.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply