Database deployment script for SQL Server 2008 has different behaviors 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.

The statement 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 10.0.2531

The local SQL Server database is version 10.50.2500

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 &lt;&gt; 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
Why are you using ##global temp tables instead of #local? It is possible that when you are changing the procedure there is already a global temp table in existence with the same name - they're global so it could have been created by anyone. Use a local temp table and that problem will go away.

Also, please don't use the sp_ prefix. Stored procedures shouldn't really need to have a prefix - what is the point of adding sp_ to the beginning of every stored procedure? They still sort the same (e.g. in Object Explorer), it just makes it that much harder to find the one you're after. If you absolutely must use a prefix for some reason, pick something other than sp_.