On Jul 22, 3:21 pm, CEduardo <CEdua...@discussions.microsoft.com>
wrote:

I'm not sure if this will work for 2005, but this scripts out linked
servers.  Run it in QA and set the output as text.  You can then copy/
paste into SQL 2005 and run.  Note all remote passwords will need to
be known, and manually entered.

Good Luck!

David Hay

use master
SET NOCOUNT ON
DECLARE @this_server VARCHAR(255),
 @server_ct INT,
 @server VARCHAR(255),
    @srvproduct VARCHAR(255),
    @provider VARCHAR(255),
    @datasrc VARCHAR(255),
    @location VARCHAR(255),
    @provstr VARCHAR(255),
    @catalog VARCHAR(255),
 @rpc INT,
 @pub INT,
 @sub INT,
 @dist INT,
 @dpub INT,
 @rpcout INT,
 @dataaccess INT,
 @collationcompatible INT,
 @system INT,
 @userremotecollation INT,
 @lazyschemavalidation INT,
 @collation VARCHAR(255)

CREATE TABLE #outputLog(
  rowId  INT IDENTITY(1, 1),
  outputData VARCHAR(1000))

CREATE TABLE #srvLogin(
  rowId  INT IDENTITY(1, 1),
  linkedServer VARCHAR(255),
  localLogin VARCHAR(255),
  isSelfMapping INT,
  remoteLogin VARCHAR(255))

SELECT @this_server = srvname FROM sysservers WHERE srvid = 0
SELECT @server_ct = 1

WHILE @server_ct <= (SELECT max(srvid) FROM sysservers)
BEGIN
select
 @server = srvname,
    @srvproduct = srvproduct,
    @provider = CASE WHEN srvproduct = 'SQL Server' THEN NULL ELSE
providername END,
    @datasrc = CASE WHEN srvproduct = 'SQL Server' THEN NULL ELSE
datasource END,
    @location = CASE WHEN srvproduct = 'SQL Server' THEN NULL ELSE
location END,
    @provstr = CASE WHEN srvproduct = 'SQL Server' THEN NULL ELSE
providerstring END,
    @catalog =  CASE WHEN srvproduct = 'SQL Server' THEN NULL ELSE
catalog END,
 @rpc = rpc,
 @pub = pub,
 @sub = sub,
 @dist = dist,
 @dpub = dpub,
 @rpcout = rpcout,
 @dataaccess = dataaccess,
 @collationcompatible = collationcompatible,
 @system = system,
 @userremotecollation = useremotecollation,
 @lazyschemavalidation = lazyschemavalidation,
 @collation = collation
from
 sysservers
WHERE
 srvid = @server_ct

INSERT INTO #outputLog
SELECT 'EXEC sp_addlinkedserver ''' + @server + ''', '
  + CASE WHEN @srvproduct IS NULL THEN 'NULL' ELSE '''' + @srvproduct
+ '''' END + ', '
  + CASE WHEN @provider IS NULL THEN 'NULL' ELSE '''' + @provider +
'''' END + ', '
  + CASE WHEN @datasrc IS NULL THEN 'NULL' ELSE '''' + @datasrc +
'''' END + ', '
  + CASE WHEN @location IS NULL THEN 'NULL' ELSE '''' + @location +
'''' END + ', '
  + CASE WHEN @provstr IS NULL THEN 'NULL' ELSE '''' + @provstr +
'''' END + ', '
  + CASE WHEN @catalog IS NULL THEN 'NULL' ELSE '''' + @catalog +
'''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'rpc'', ' +
CASE WHEN @rpc = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'pub'', ' +
CASE WHEN @pub = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'sub'', ' +
CASE WHEN @sub = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'dist'', ' +
CASE WHEN @dist = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'dpub'', ' +
CASE WHEN @dpub = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'rpc out'', '
+ CASE WHEN @rpcout = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'data
access'', ' + CASE WHEN @dataaccess = 1 THEN '''TRUE''' ELSE
'''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'collation
compatible'', ' + CASE WHEN @collationcompatible = 1 THEN '''TRUE'''
ELSE '''FALSE''' END

--  INSERT INTO #outputLog
--  SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'system'',
' + CASE WHEN @system = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'use remote
collation'', ' + CASE WHEN @userremotecollation = 1 THEN '''TRUE'''
ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'lazy schema
validation'', ' + CASE WHEN @lazyschemavalidation = 1 THEN '''TRUE'''
ELSE '''FALSE''' END

--  INSERT INTO #outputLog
--  SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' +
'collation'', ' + CASE WHEN @collation IS NULL THEN 'NULL' ELSE '''' +
@collation + '''' END + ''

INSERT INTO #srvLogin
EXEC sp_helplinkedsrvlogin @server

INSERT INTO #outputLog
SELECT 'EXEC sp_addlinkedsrvlogin @rmtsrvname = '+ CASE WHEN
linkedServer IS NULL THEN 'NULL' ELSE '''' + linkedServer + '''' END
  + ', @useself = ' + CASE WHEN isSelfMapping = 1 THEN '''TRUE'''
ELSE '''FALSE''' END
  + ', @locallogin = ' + CASE WHEN localLogin IS NULL THEN 'NULL'
ELSE '''' + localLogin + '''' END
  + ', @rmtuser = ' + CASE WHEN remoteLogin IS NULL THEN 'NULL' ELSE
'''' + remoteLogin + '''' END
  + ', @rmtpassword  = ' + CASE WHEN isSelfMapping = 1 THEN 'NULL'
ELSE '''ENTER_PASSWORD_HERE''' END

FROM  #srvLogin

DELETE #srvLogin

SELECT @server_ct = @server_ct + 1
END

SELECT outputData from #outputLog
ORDER BY rowId

DROP TABLE #outputLog
DROP TABLE #srvLogin