Dynamic SQL gives Scalar Variable Must Be Declared

1

Hi all,
I'm attempting to create 100 linked server dynamically by reading instance names from a table and looping thru:

SET NOCOUNT ON;
Declare @Query nvarchar(1000)
Declare @Product varchar (20)
Declare @LinkedServer varchar(100)
DECLARE @server_name_cursor as Cursor;
SET @server_name_cursor = CURSOR FOR SELECT [ServerInstance] from dbo.[LinkedServers] where isAdded = 'False';
OPEN @server_name_cursor

FETCH Next From @server_name_cursor Into @LinkedServer

WHILE @@Fetch_status = 0
BEGIN

Set @Product = 'SQL Server'
set @Query = 'exec master.sys.sp_addlinkedserver
@server = @LinkedServer,
@srvproduct=@Product;'

exec sp_executesql @Query

Fetch Next from @server_name_cursor Into @LinkedServer;

End

CLOSE @server_name_cursor
DEALLOCATE @server_name_cursor

I'm getting error: Must declare the scalar variable "@LinkedServer".

Any ideas on recoding this, please? Thanks

2

First cursor variables have no @ and they aren't declared that way in SQL Server

DECLARE @server_name_cursor as Cursor;
SET @server_name_cursor = CURSOR FOR SELECT [ServerInstance] from dbo.[LinkedServers] where isAdded = ‘False’;

Should be

DECLARE @LinkedServer nvarchar(128);
DECLARE server_name_cursor Cursor
FOR SELECT [ServerInstance] from dbo.[LinkedServers] where isAdded = ‘False’;
OPEN  server_name_cursor;

FETCH  server_name_cursor INTO @LinkedServer;

WHILE @@FETCH_STATUS = 0
BEGIN
   EXEC sys.sp_addlinkedserver  @LinkedServer,  N'SQL Server'; 

   FETCH  server_name_cursor INTO @LinkedServer;
END
CLOSE server_name_cursor;
DEALLOCATE server_name_cursor;
GO
1 Like
3

Thank you, jotorre. Your code worked perfectly!

4

There's no problem with using a variable for a cursor. The issue was that you used undeclared variables -- @LinkedServer and @Product -- in the dynamic SQL, and the dynamic batch is separate and thus can't access variables from the calling code.

SET NOCOUNT ON;
Declare @Query nvarchar(1000)
Declare @Product varchar (20)
Declare @LinkedServer varchar(100)
DECLARE @server_name_cursor as Cursor;
SET @server_name_cursor = CURSOR FOR SELECT ..;
OPEN @server_name_cursor

FETCH Next From @server_name_cursor Into @LinkedServer

WHILE @@Fetch_status = 0
BEGIN

Set @Product = 'SQL Server'
set @Query = replace(replace('exec master.sys.sp_addlinkedserver
@server = @LinkedServer,
@srvproduct=@Product;', 
    '@LinkedServer', '''' + @LinkedServer + ''''), 
    '@Product', '''SQL Server''')

--print @query
EXEC sp_executesql @Query

Fetch Next from @server_name_cursor Into @LinkedServer;

End

CLOSE @server_name_cursor
DEALLOCATE @server_name_cursor