July 13, 2005 at 3:31 am
Hi All,
I have a SQL Server A which has 20 linked servers attached to it.
I have another SQL Server B where I want the same 20 linked servers which are in server A.
All of them are sql servers.
Is there a way that I can get these linked servers on Server B from Server A.?
I mean some kind of shortcut method.?
Thanks.
--Kishore
July 13, 2005 at 6:28 am
Run this on your source server to generate a script for your target server.
This is a script we use. It won't do passwords though.
We use Security Account Delegation for pass-through NT auth.
I've tweaked it slightly but can't check my results in QA
CREATE FUNCTION dbo.ufn_GoString ()
RETURNS char(6)
AS
BEGIN
RETURN '
' + CHAR(ASCII('G')) + CHAR(ASCII('O')) + '
'
END
GO
SELECT '
EXEC dbo.sp_addlinkedserver ''' + s.srvname + ''', ''sql server''' + dbo.ufn_GoString () +
'EXEC dbo.sp_setnetname ''' + s.srvname + ''', ''' + RTRIM(srvnetname) + '''
EXEC dbo.sp_droplinkedsrvlogin ''' + s.srvname + ''', NULL
' +
CASE WHEN l.[name] IS NULL THEN
'EXEC dbo.sp_addlinkedsrvlogin ''' + s.srvname + ''', ''true'''
ELSE
'EXEC dbo.sp_addlinkedsrvlogin ''' + s.srvname + ''', ''false'', NULL, ''' + l.[name] + ''', ''password'''
END + dbo.ufn_GoString ()
FROM
master.dbo.sysservers s JOIN master.dbo.sysxlogins l ON s.srvid = l.srvid
WHERE
s.providername = 'SQLOLEDB' AND s.srvname <> @@SERVERNAME
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply