I often restore my Testing database from Production db, but this time I noticed something odd:
we have a stored procedure called (USP_AddTS), one of its variable names have slightly a different name in both databases (which I capitalized in example below):
USE [ProductionDB]
GO
ALTER PROCEDURE [dbo.USP_AddTS]
( @tsNum varchar(75),
@tsVersion varchar(75),
@tsTitle varchar(450),
@systemID tinyint,
@RELEASEDATE smalldatetime,
…………
……
)
AS
INSERT into TransmittalSheet(TransmittalNumber,TransmittalVersion,TransmittalTitle,TransmittalReleaseDate,……………..
values (@tsNum,@tsVersion,@tsTitle,@releasedate,….
2) USE [TestingDB]
GO
ALTER PROCEDURE [dbo.USP_AddTS]
( @tsNum varchar(75),
@tsVersion varchar(75),
@tsTitle varchar(450),
@systemID tinyint,
@TSRELEASEDATE smalldatetime,
…………
……
)
AS
INSERT into TransmittalSheet(TransmittalNumber,TransmittalVersion,TransmittalTitle,TransmittalReleaseDate,……………..
values (@tsNum,@tsVersion,@tsTitle,@TSreleasedate,….
We recently added a new column named (TransmittalReleaseDate) to the table (TransmittalSheet), so we updated the (USP_AddTS) to include the new variable (@ReleaseDate) like I’m showing above , but for some reason after I restored my Testing DB from a recent copy from ProductionDB, the variable name got updated to @TSReleaseDate only in Testing DB, unlike the ProductionDB that shows it as @ReleaseDate, is this possible or am I mistaken?
There was another variable line from the declared list above that I commented out from the ProductionDB and I made sure it was saved, but from the TestingDB was not commented out and still available.
I will keep an eye when I refresh or restore again, as I may incorrectly chose the prior date’s backup copy to restore from, but I thought of asking here too.
I'm using SQL Server 2008R2, Thanks.
Best Answer
Its not possible for the variable name to change by itself, its likely you restored from a different (older? newer?) backup than what you thought. You can check
sys.objects
for the stored procedure create/modified dates and compare across the 2 databases.