Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Please advice me as I'm trying to move the linked server (Providers with the tables in Catalogs) from SQL server 2005 to another upgraded sqlserver 2008, I scripted out everything from there, but not sure what's the next step or how to import them back in the new server?

Thanks in advance,

share|improve this question

1 Answer 1

I scripted out everything from there, but not sure what's the next step or how to import them back in the new server?

Just run the scripted out version (from 2005 servers) on the new sql 2008 server.

You need to manually key-in the passwords as when you script out linked servers, passwords are not scripted out - they are grabbled - ########.

You can check the linked server properties on old server and then match them on the new server using below t-sql :

SELECT ss.server_id 
          ,ss.name 
          ,'Server ' = Case ss.Server_id 
                            when 0 then 'Current Server' 
                            else 'Remote Server' 
                            end 
          ,ss.product 
          ,ss.provider 
          ,ss.catalog 
          ,'Local Login ' = case sl.uses_self_credential 
                            when 1 then 'Uses Self Credentials' 
                            else ssp.name 
                            end 
           ,'Remote Login Name' = sl.remote_name 
           ,'RPC Out Enabled'    = case ss.is_rpc_out_enabled 
                                   when 1 then 'True' 
                                   else 'False' 
                                   end 
           ,'Data Access Enabled' = case ss.is_data_access_enabled 
                                    when 1 then 'True' 
                                    else 'False' 
                                    end 
           ,ss.modify_date 
      FROM sys.Servers ss 
 LEFT JOIN sys.linked_logins sl 
        ON ss.server_id = sl.server_id 
 LEFT JOIN sys.server_principals ssp 
        ON ssp.principal_id = sl.local_principal_id
share|improve this answer
    
Thank you Kin, I will try it for sure as soon as I go back to work on Friday, since I'm used to do most things through the gui or the management studio, I'm trying to understand the above T-sql code, can you explain what is it doing before I run it in the production server? I have to replace the actual server name for the current the remote right? I scripted out the current linked server: –  Shayma Ahmad Dec 12 '13 at 7:37
    
There are System tables in the default catalog that's associated with this linked server, that I cannot just open a new connection to it too. –  Shayma Ahmad Dec 12 '13 at 7:49
    
Not sure how can I attach a copy of the scripted out file –  Shayma Ahmad Dec 12 '13 at 7:50
    
EXEC master.dbo.sp_addlinkedserver @server = N'ExcelFacDocReport', @srvproduct=N'Jet 4.0', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'\\Brorporap1\local\Document Control\Reports\FacDocReport.xls', @provstr=N'Excel 5.0' /* For security reasons the linked server remote logins password is changed with ######## */ EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ExcelFacDocReport',@useself=N'True',@locallogin=NULL,@rmtuser=NULL‌​,@rmtpassword=NULL –  Shayma Ahmad Dec 12 '13 at 7:51
    
I used your code in both servers and I got the same result exactly now for the linked server as I created it there too, now what about the system tables that was linked to in the Catalog then default... it seems that i cannot re-create them there. –  Shayma Ahmad Dec 12 '13 at 22:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.