2

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 <> 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
  • Can you post the full script for the procedure with the error? – Mark Sinkinson May 28 '14 at 12:55
  • 1
    I don't understand why you're using an EXEC with that SQL. Or why you're using a global temporary table, which could be accidentally altered by another proc. Remove the EXEC and change the global temp table to a local one (remove one '#' symbol from the name, so call it #TEMP) – Mark Sinkinson May 28 '14 at 13:36
4

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_.

  • The procedure is not mine. It was produced by other developers. Anyway these thoughts would help them to improve their scripts in TFS – Junior M May 28 '14 at 13:53
  • 1
    Also, to add, when you start the name of a stored procedure with SP_ this means that sql will first look in the master db for that stored procedure, regardless of where you installed it. Which means if you create one in each database, the one in master is the only one being triggered, with all the resulting possible issues. – Reaces May 28 '14 at 14:14
  • 1
    @Reaces That's why I posted this link which explains what happens and how it can impact performance. :-) – Aaron Bertrand May 28 '14 at 14:20
  • @Reaces If you read the link that Aaron Bertrand posted, it already covers that. – Mark Sinkinson May 28 '14 at 14:21
  • Ah, apologies I did not see the link on the first read :) – Reaces May 28 '14 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.