sp_rename Issue, I wasn't aware of

Question

Post reply

sp_rename Issue, I wasn't aware of

JohnyRotten

Ten Centuries

Points: 1014
More actions
March 31, 2015 at 12:12 am

#398260

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

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

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

Can you please clarify what you're getting?

-- Gianluca Sartori

JohnyRotten Ten Centuries Points: 1014 More actions · Answer 2

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Not for me. Everything works as expected.

Which version are you on?

-- Gianluca Sartori

JohnyRotten Ten Centuries Points: 1014 More actions · Answer 4

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

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

JohnyRotten Ten Centuries Points: 1014 More actions · Answer 8

JohnyRotten

Ten Centuries

Points: 1014

March 31, 2015 at 4:54 am

#1787631

Thanks, that made some problems yesterday.