March 31, 2015 at 12:12 am
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
March 31, 2015 at 2:28 am
Sorry, but I don't see what's wrong. Everything works fine for me.
Can you please clarify what you're getting?
-- Gianluca Sartori
March 31, 2015 at 3:31 am
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
March 31, 2015 at 3:42 am
Not for me. Everything works as expected.
Which version are you on?
-- Gianluca Sartori
March 31, 2015 at 3:51 am
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
March 31, 2015 at 4:07 am
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
March 31, 2015 at 4:38 am
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
March 31, 2015 at 4:43 am
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
March 31, 2015 at 4:54 am
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