@@SERVERNAME showing up in Error Window

Question

Hi all,

I couldn't find a related topic on the web or on these forums.  Why is the following an error in the Error window of a SSDT project:

SQL70590: Must declare the variable or parameter "@@SERVERNAME"

Background:  I just started at a new company and I am trying to use the SSDT toolset with there codebase.  I have used the Visual Studio Team System Database tools in the past and have been trying to get up to speed on the new SSDT toolset.  So, I created a project from one of the company's existing databases.  There are several references throughout the project to the valid TSQL system variable @@SERVERNAME, but as indicated above this results in an ERROR in the SSDT project.

Any thoughts?

Thanks,

John Dempsey

   

Answer 1

Hi John,

Will you pass on the script that's throwing this error?  I'm not able to reproduce this behavior with a simple script with @@Servername on my side.

Thanks,

Janet Yeilding

Answer 2

I am including the script as requested.  This is just one example of the scripts that appear in this database project with the problem. 

CREATE PROCEDURE [dbo].[usp_dsh_MoveTable_V3]
(
	@Qualifier				VARCHAR(100),
	@TableName				VARCHAR(200),
	@DestinationTableName	VARCHAR(200),
	@SchemaName				VARCHAR(30) = 'dbo',
	@DropOriginalTable		INT = 0
)
AS
BEGIN
	-- Error handling variables
	DECLARE @ErrMsg				NVARCHAR(MAX),
			@ErrSev				INT,
			@Parameters			VARCHAR(MAX);
	DECLARE @IndexName			VARCHAR(200),
			@IndexColName		VARCHAR(200),
			@WorkTableName		VARCHAR(200),
			@DBName				VARCHAR(200),
			@ObjectID			INT;
	DECLARE	@IndexScript		VARCHAR(MAX);
			
	BEGIN TRY
	
		SELECT @DBName = DB_NAME();
		SET @WorkTableName = @DestinationTableName + '_WT';	
		SELECT @ObjectID = OBJECT_ID
		  FROM SYS.TABLES T
			   INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
		 WHERE T.name = @TableName
			   AND S.name = @SchemaName;
		EXEC dbo.usp_dsh_GenerateIndexesScript
			 @ObjectID = @ObjectID,
			 @DestinationTableName = @WorkTableName,
			 @IncludeFileGroup = 0,
			 @IncludeDrop = 0,
			 @IncludeFillFactor = 0,
			 @IndexScript = @IndexScript OUTPUT
		/*
		 * If it exists, drop the Working table and then copy the
		 * QC table contents to the production table
		 */
		--DELETE EXISTING DESTINATION TABLE 
		EXEC
		('
			IF EXISTS(SELECT object_id FROM ' + @Qualifier + 'SYS.TABLES WHERE name = ''' + @WorkTableName + ''') 
			BEGIN
				EXEC ' + @Qualifier + 'dbo.sp_executesql
					N''
						DROP TABLE dbo.' + @WorkTableName + '
					''
			END
		')
		-- Transfer the table.
		EXEC
			('
				EXEC ' + @Qualifier + 'dbo.sp_executesql
				N''
					SELECT *
					  INTO ' + @WorkTableName + '
					  FROM ' + @@SERVERNAME + '.' + @DBName + '.' + @SchemaName + '.' + @TableName + '
				''
			')
		-- Create the index on the destination table (if applicable)
		IF (@IndexScript IS NOT NULL AND @IndexScript <> '')
		BEGIN
		
			EXEC
			('
				EXEC ' + @Qualifier + 'dbo.sp_executesql
				N''
					' + @IndexScript + '
				''
			')
		END;
		/*
		 * If it exists, drop the production table and then copy the
		 * QC table contents to the production table
		 */
		EXEC
		('
			IF EXISTS(SELECT object_id FROM ' + @Qualifier + 'SYS.TABLES WHERE name = ''' + @DestinationTableName + ''') 
			BEGIN
				EXEC ' + @Qualifier + 'dbo.sp_executesql
					N''
						DROP TABLE dbo.' + @DestinationTableName + '
					''
			END
		');
		-- Rename the working table
		EXEC
		('
			EXEC ' + @Qualifier + 'dbo.sp_executesql 
				N''
					EXEC sp_rename ' + @WorkTableName + ',  ' + @DestinationTableName +
				'''
		');
		IF(@DropOriginalTable = 1)
		BEGIN
			EXEC('DROP TABLE ' + @TableName)
		END
	END TRY
	BEGIN CATCH
		SELECT @ErrSev = ERROR_SEVERITY(), @ErrMsg = ERROR_MESSAGE();
		SET @Parameters = '@Qualifier = ''' + @Qualifier + ''',
						   @TableName = ''' + @TableName + ''',
						   @DestinationTableName = ''' + @DestinationTableName + ''',
						   @SchemaName = ''' + @SchemaName + ''',
						   @DropOriginalTable = ''' + CAST(@DropOriginalTable AS VARCHAR)+ ''''
						   
		SET @ErrMsg = (@ErrMsg + ' Proc Call: usp_dsh_MoveTable_V2 ' + @Parameters);
		RAISERROR (@ErrMsg, @ErrSev, 1);
	END CATCH;
END;

Thanks,

John

Answer 3

Hello John

This does appear to be a bug. Please file a connect bug for this issue.

You should be able to work around this issue as the reference to @@ServerName in the dynamic SQL is not actually needed. You can replace

  FROM ' + @@SERVERNAME + '.' + @DBName + '.' + @SchemaName + '.' + @TableName + '

with

  FROM ' + @DBName + '.' + @SchemaName + '.' + @TableName + '

-Lonny

Answer 4

Hi John,

Unfortunately, SSDT has a bug in identifying undeclared variables: it doesn't understand the so-called system functions (the predefined @@ variables) if they appear inside procedure or function arguments. The general workaround for this issue is to define a variable, assign the argument expression to the variable, and use the variable as the argument. So, in addition to Lonny's suggestion, another workaround for your issue is something like this:

		-- Transfer the table.
		DECLARE @Query varchar(1000) 
		SET @Query = '
				EXEC ' + @Qualifier + 'dbo.sp_executesql
				N''
					SELECT *
					  INTO ' + @WorkTableName + '
					  FROM ' + @@SERVERNAME + '.' + @DBName + '.' + @SchemaName + '.' + @TableName + '
				''
			'
		EXEC (@Query)

I apologize for the inconvenience it has caused you and we'll try to fix it for the next release.

Thanks,
Yang

---

YangG(MSFT), SDE

Answer 5

Thanks Lonny.  When I was reviewing the code I was thinking the same thing, that the @@Servername wasn't even needed in this context.  Now, I just need to address it with the team why they may have thought it was necessary. :)

What category on connect do I put the bug under?  SQL Server or Developer Tools -> .NET?

Answer 6

Thanks for the follow up YangG.