SQLTeam.com | Weblogs | Forums

Working with two servers

Hello, experts,

I am working with two servers, Serv A and Serv B. I access the two separately in SQL Management Studio (v18.8). This is how I am working right now.

  • I access Serv A through Server Authentication. here I have my own database E_DB where I may create tables, stored procs, etc.

  • Recently, I have gained access to Serv B cross the network of my company to retrieve sales information about Product A. I access through Windows authentication. Here I may only have READ access.

My question is: From my own database E_DB in Serv A, how can I get data in Serv B and save it to my own database? I used "FROM [Serv B].[DB_Name].[Table_Name]", got message:

Msg 7202, Level 11, State 2, Line 3
Could not find server 'Serv B' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Please give me some advice on this. Thank you in advance!



You will need to create a linked server object with the server name

You can create a linked server - or create a process to export and import the data. To export/import you can use SSIS, BCP, Powershell - or other tools.

With that said - before doing so you really need to make sure you are authorized to host that data on your system. If there is any type of protected data (PII, PHI, etc.) it could be an issue if you pull the data across into your system.