February 14, 2011 at 1:39 pm
Hi folks
Is it possible to pass variables to sp_addlinkedserver?
I want to loop thru a database containing a list of servernames and port numbers and then link them using sp_addlinkedserver.
This code will add linked server SRVRABC: EXEC sp_addlinkedserver ‘SRVRABC,1433’.
I want to replace SRVRABC and 1433 with a variable: EXEC sp_addlinkedserver ‘@srvname,@portnum’ but what actually happens is ‘@server,@portnum’ is added to sys.servers.
Any suggestions?
Thanks!
February 14, 2011 at 2:06 pm
you have to declare and assign the variables for TSQL:
if this is all in TSQL, you might use a cursor like this:
--#################################################################################################
--Linked server Syntax for SQL Server With Alias
--#################################################################################################
declare
@svname Nvarchar(64)
declare c1 cursor for
--my list of servers
SELECT 'DBSQL2K5' As ServerName UNION ALL
SELECT 'DBSQL2K5\SQLEXPRESS' UNION ALL
SELECT 'DBSQL2K8' UNION ALL
SELECT 'DBSQL2K8\SQL2008' UNION ALL
SELECT 'DEV223'
open c1
fetch next from c1 into @svname
While @@fetch_status <> -1
begin
EXEC master.dbo.sp_addlinkedserver @server = @svname, @srvproduct = N'', @datasrc = @svname, @provider = N'SQLOLEDB';
--are you adding a login?
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = @svname,
@useself = N'False',
@locallogin = NULL,
@rmtuser = N'sa',
@rmtpassword = 'NotARealPassword';
fetch next from c1 into @svname
end
close c1
deallocate c1
Lowell
February 14, 2011 at 2:23 pm
Here is my code. When this code is executed it inserts '@ServerName,@Port_nbr'into the sys.servers table. Currently all the servers are Kerberos so I dont need a login but I do need the port number.
DECLARE @id INT
,@ServerName nvarchar(250)
,@Port_nbr nvarchar(5)
,@isLinked nvarchar (5)
DECLARE ServerList CURSOR LOCAL FAST_FORWARD READ_ONLY
FOR SELECT PK_ID
, ServerName
, Port_nbr
, isLinked
FROM dbo.HOSTSERVERS
WHERE isLinked <> 'Y'
and Port_nbr is not NULL
OPEN ServerList
-- get very first record
FETCH NEXT FROM ServerList
INTO @id
, @ServerName
, @Port_nbr
, @isLinked
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
EXEC master.dbo.sp_addlinkedserver @server = '@ServerName,@Port_nbr'
, @srvproduct = N''
, @datasrc = @ServerName
, @provider = N'SQLOLEDB';
END
-- get next record
FETCH NEXT FROM ServerList
INTO @id
, @ServerName
, @Port_nbr
, @isLinked
END
February 14, 2011 at 2:48 pm
ahh i see it.
anything between two single quotes is a literal string...
so this part never gest substituted with your values:
EXEC master.dbo.sp_addlinkedserver @server = '@ServerName,@Port_nbr'
try this instead see how i added a variable and append the two together for the server?:
DECLARE @id INT
,@ServerName nvarchar(250)
,@Port_nbr nvarchar(5)
,@isLinked nvarchar (5)
DECLARE @ServerPlusPort varchar(256)
DECLARE ServerList CURSOR LOCAL FAST_FORWARD READ_ONLY
FOR SELECT PK_ID
, ServerName
, Port_nbr
, isLinked
FROM dbo.HOSTSERVERS
WHERE isLinked <> 'Y'
and Port_nbr is not NULL
OPEN ServerList
-- get very first record
FETCH NEXT FROM ServerList
INTO @id
, @ServerName
, @Port_nbr
, @isLinked
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
SET @ServerPlusPort = @ServerName + ',' + @Port_nbr
EXEC master.dbo.sp_addlinkedserver @server = @ServerPlusPort
, @srvproduct = N''
, @datasrc = @ServerName
, @provider = N'SQLOLEDB';
END
-- get next record
FETCH NEXT FROM ServerList
INTO @id
, @ServerName
, @Port_nbr
, @isLinked
END
Lowell
February 15, 2011 at 9:46 am
IT WORKS! THANK YOU VERY MUCH!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply