Using Visual Studio 2008, I have compared schemas between remote SQL Server database with scripts hosted in a TFS server.
The goal was to generate a migration script that would update the remote database with developers latest script changes located in TFS.
The next step was to get a full backup from remote database, and restore it in my local database, and then execute the migration script in order to test it.
After small tweeks (dealing with constraints) and I have managed to execute the migration script locally and my local database (that is a copy from remote database).
The last step was to get this script and run it in remote database, but for my surprise, the script gets interrupted in a procedure:
Altering dbo.sp_my_procedure_here... Msg 207, Level 16, State 1, Procedure sp_my_procedure_here, Line 616 Invalid column name 'code'.
Within this mentioned procedure, there is a
CREATE TABLE ##TEMP (code INT), a execute command to run a SQL statement contained in a string variable for executing
INSERT INTO ##TEMP VALUES (999), a
SELECT @myReturnCode = code FROM ##TEMP and a
DROP TABLE ##TEMP.
SELECT @myReturnCode = code FROM ##TEMP is not working in the remote database. Since there is no procedure execution (this is only a script modifying database structure). Why the migration script would fire "invalid column" on the remote database?
The remote SQL Server database is version
The local SQL Server database is version
Both databases are set to compatibility mode for "SQL Server 2000". The same migration script was executed in SSMS 2008.
ALTER PROCEDURE [dbo].[sp_my_procedure] AS SET NOCOUNT ON CREATE TABLE ##TEMP (code INT) DECLARE @sql Varchar(4000) SET @sql = 'DECLARE @myReturnCode int SET @myReturnCode = 999 INSERT INTO ##TEMP VALUES (@myReturnCode)' EXEC (@sql) IF (@@ERROR <> 0) BEGIN RAISERROR('There is something wrong with insert',1,1) DROP TABLE ##TEMP RETURN -1 END SELECT @myReturnCode = code FROM ##TEMP DROP TABLE ##TEMP SET NOCOUNT OFF