how to export Linked servers in SQL Server 2000

  • U J

    SSCrazy

    Points: 2420

    I want to shift my server from one machine to other.I have around 60 Linked servers created on the server.Does anybody know is there any method available in SQL server to to export Linked servers in SQL Server 2000..Tried to find about this but failed..

    Thnks in advance for any Help.....

     

     

     

  • Sugesh Kumar

    One Orange Chip

    Points: 27311

    Use the tool given in link below they are useful to scriupt and move your linked servers.

    http://www.codeproject.com/cs/database/ScriptLinkedServers.asp

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • Maverick100877

    SSCertifiable

    Points: 5240

    Use this script. I got it from on this site only.

     

     

    --Script to script out all linked/remote servers

    --Works on 7.0 and 2000 servers

    --remote password decrypt only works on 7.0

    declare

        @status     smallint,      -- server status

        @server         sysname,         -- server name

        @srvid     smallint,      -- server id

        @srvproduct     nvarchar(128),   -- product name (dflt to ss)

        @allsetopt      int,      --sum of all settable options

        @provider       nvarchar(128),   -- oledb provider name

        @datasrc        nvarchar(4000),  -- oledb datasource property

        @location       nvarchar(4000),  -- oledb location property

        @provstr        nvarchar(4000),  -- oledb provider-string property

        @catalog        sysname,         -- oledb catalog property

        @netname     varchar(30),     -- Server net name

        @srvoption     varchar(30),     -- server options

        @loclogin       varchar(30),     -- Local user

        @rmtlogin       varchar(30),     -- Remote user

        @selfstatus     smallint,      -- linked server login status

        @rmtpass        varbinary(256),  -- linked server login password

        @pwdtext        nvarchar(128),   -- linked server decrypted password

        @i              int,             -- linked server pswd decrypt var

        @lsb            tinyint,         -- linked server pswd decrypt var

        @msb            tinyint,         -- linked server pswd decrypt var

        @Tmp            varbinary(256)   -- linked server pswd decrypt var

    select @allsetopt=number from master.dbo.spt_values

      where type = 'A' and name = 'ALL SETTABLE OPTIONS'  -- Only 7.0 else use 4063

       

    declare d cursor for SELECT srvid,srvstatus, srvname, srvproduct, providername, datasource,

     location, providerstring, catalog, srvnetname

     from master..sysservers

     where srvid > 0  -- Local Server

    open d  

       fetch next from d into @srvid, @status, @server, @srvproduct, @provider, @datasrc,

     @location, @provstr, @catalog, @netname

    SET NOCOUNT  ON 

     

    while (@@FETCH_STATUS<>-1) begin

      

    PRINT '--------------------------------'

    Print '--      ' + @server

    PRINT '--------------------------------'  

    If @status in (64,65)               --Remote Server

    Begin

     Print 'sp_addserver'

     Print '  @server = '''+ @server + ''''

     Print '  GO'

      

     If @status = 64

     Begin

      Print 'sp_serveroption'

      Print '  @server = '''+ @server + ''','

      Print '  @optname = ''rpc'','

      Print '  @optvalue = ''false'''

      Print '  GO'

     End

     exec ('declare  r cursor for

     select l.name, r.remoteusername from

     sysremotelogins r join sysservers s on

     r.remoteserverid = s.srvid

     join syslogins l on

     r.sid = l.sid

     where s.srvname = '''+ @server + '''')

     open r

      fetch next from r into @loclogin, @rmtlogin

     while (@@FETCH_STATUS<>-1)

     begin

     Print 'sp_addremotelogin'

     Print '  @remoteserver = '''+ @server + ''','

     Print '  @loginame  = '''+ @loclogin + ''','

     Print '  @remotename = '''+ @rmtlogin + ''''

     Print '  GO'

      fetch next from r into @loclogin, @rmtlogin

     end

     close r

     deallocate r

     

     

    End

    Else   --Linked server

    Begin

     If exists (select * from tempdb..sysobjects where name like '#tmpsrvoption%')

     Begin

     drop table #tmpsrvoption

     End

     Create Table #tmpsrvoption

     (

     srvoption  varchar(30)

    &nbsp

     insert #tmpsrvoption

     select v.name

      from master.dbo.spt_values v, master.dbo.sysservers s

      where srvid = @srvid

       and (v.number & s.srvstatus)=v.number

       and (v.number & isnull(@allsetopt,4063)) <> 0

       and v.number not in (-1, isnull(@allsetopt,4063))

       and v.type = 'A'

     PRINT 'sp_addlinkedserver'

     Print '  @server = '''+ @server + ''''

     Print ',  @srvproduct = ''' + @srvproduct + ''''

     If @srvproduct <> 'SQL Server'   --Cannot specify additional info for SQL Server Product

     Begin

      Print ',  @provider = ''' + @provider + ''''

      Print ',  @datasrc = ''' + @datasrc + ''''

      Print ',  @location = ''' + @location + ''''

      Print ',  @provstr = ''' + @provstr + ''''

      Print ',  @catalog = ''' + @catalog + ''''

     End

      Print '  GO'

         

     -- Set all servers options to false, then reset correct server options

     Print 'sp_serveroption'

      Print '  @server = '''+ @server + ''','

      Print '  @optname = ''rpc'','

      Print '  @optvalue = ''false'''

      Print '  GO'

     Print 'sp_serveroption'

      Print '  @server = '''+ @server + ''','

      Print '  @optname = ''rpc out'','

      Print '  @optvalue = ''false'''

      Print '  GO'

     Print 'sp_serveroption'

      Print '  @server = '''+ @server + ''','

      Print '  @optname = ''data access'','

      Print '  @optvalue = ''false'''

      Print '  GO'

      

     declare s cursor for SELECT srvoption

     from #tmpsrvoption

     

     open s 

        fetch next from s into @srvoption

     

     while (@@FETCH_STATUS<>-1)

     begin

      Print 'sp_serveroption'

      Print '  @server = '''+ @server + ''','

      Print '  @optname = '''+ @srvoption + ''','

      Print '  @optvalue = ''true'''

      Print '  GO'

      fetch next from s into @srvoption

     End

     close s

     deallocate s

     

    --Script linked server logins

    If exists (select * from tempdb..sysobjects where name like '#tmplink%')

     Begin

     drop table #tmplink

     End

    create table #tmplink

    (

    rmtserver sysname,

    loclogin sysname null,

    selfstatus smallint,

    rmtlogin sysname null

    )

    insert #tmplink

    exec ('sp_helplinkedsrvlogin '''+ @server + '''')

    declare ll cursor for

    select loclogin, selfstatus, rmtlogin from #tmplink order by rmtlogin

    open ll

    fetch next from ll into @loclogin, @selfstatus, @rmtlogin

    while (@@FETCH_STATUS<>-1)

    begin

        -- Use self no remote user/password

    If (@selfstatus = 1 and @loclogin is null)

    Begin

     Print 'sp_addlinkedsrvlogin'

     Print '  @rmtsrvname = '''+ @server + ''','

     Print '  @useself = ''true'''

     Print '  GO'

    End

    Else

    If (@selfstatus = 1 and @loclogin is not null) Begin

    Print 'sp_addlinkedsrvlogin'

     Print '  @rmtsrvname = '''+ @server + ''','

     Print '  @useself = ''true'','

     Print '  @locallogin = '''+ @loclogin + ''','

     Print '  @rmtuser = NULL,'

     Print '  @rmtpassword = NULL'

     Print '  GO'

    End

    Else

    If (@selfstatus = 0 and @rmtlogin is null) Begin

    Print 'sp_addlinkedsrvlogin'

     Print '  @rmtsrvname = '''+ @server + ''','

     Print '  @useself = ''false'','

     Print '  @locallogin = NULL,'

     Print '  @rmtuser = NULL,'

     Print '  @rmtpassword = NULL'

     Print '  GO'

    End

    Else

    If (@selfstatus = 0) Begin  -- Check for Use self mappings

     exec ('declare pwd cursor for

     select l.password from master..sysservers s

     join master..sysxlogins l on s.srvid = l.srvid --where l.sid is not null

     where s.srvname = '''+ @server + ''' and l.name = '''+ @rmtlogin + '''')

    -- Decrypt passwords

    -- Only works for 7.0 server

    -- Encrypt algorithm changed in 2000

     open pwd

     fetch next from pwd into @rmtpass

     while @@fetch_status = 0

         begin

         set @i = 0

         set @pwdtext = N''

         while @i < datalength(@rmtpass)

            begin

            set @Tmp = encrypt(@pwdtext + nchar(0))

            set @lsb = convert(tinyint, substring(@tmp, @i + 1, 1))

                ^ convert(tinyint, substring(@rmtpass, @i + 1, 1))

            set @i = @i + 1

            set @Tmp = encrypt(@pwdtext + nchar(@lsb))

            set @msb = convert(tinyint, substring(@tmp, @i + 1, 1))

                ^ convert(tinyint, substring(@rmtpass, @i + 1, 1))

            set @i = @i + 1

            set @pwdtext = @pwdtext + nchar(convert(smallint, @lsb)

                + 256 * convert(smallint, @msb))

            end

         Print 'sp_addlinkedsrvlogin'

     Print '  @rmtsrvname = '''+ @server + ''','

     Print '  @useself = ''false'','

     If (@loclogin is null)

     Begin

      Print '  @locallogin = NULL,'

     End

     Else

      Begin

       Print '  @locallogin = '''+ @loclogin + ''','

      End

     If (@rmtlogin is null)

     Begin

      Print '  @rmtuser = NULL,'

     End

     Else

      Begin

       Print '  @rmtuser = '''+ @rmtlogin + ''','

      End

     If (@pwdtext is null)

     Begin

      Print '  @rmtpassword = NULL'

     End

     Else

      Begin

       print '  @rmtpassword = '''+ @pwdtext + ''''

      End

     Print '  GO'

         fetch next from pwd into @rmtpass

        end

        close pwd 

        deallocate pwd

    End

     fetch next from ll into @loclogin, @selfstatus, @rmtlogin

    End

    close ll 

    deallocate ll

     

     

     

    End

    If @netname <> @server   -- If the srvnetname.sysservers is different from srvname.sysservers

     Begin

      Print 'sp_setnetname'

      Print '  @server = '''+ @server + ''','

      Print '  @network_name = '''+ @netname + ''''

     End

    fetch next from d into @srvid,@status, @server, @srvproduct, @provider, @datasrc,

     @location, @provstr, @catalog, @netname

     

    End

    close d

    deallocate d

     

     

     

  • U J

    SSCrazy

    Points: 2420

    Thnks a lot.. Sugesh,Arun...

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply