Selecting data from two different servers in SQL S

Question

How can I select data in the same query from two different databases that are on two different servers in SQL Server?

Answer 1

You can do it using Linked Server.

Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel.

Linked servers offer the following advantages:

• The ability to access data from outside of SQL Server.
• The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
• The ability to address diverse data sources similarly.

Read more about Linked Servers.

Follow these steps to create a Linked Server:

1. Server Objects -> Linked Servers -> New Linked Server

2. Provide Remote Server Name.

3. Select Remote Server Type (SQL Server or Other).

4. Select Security -> Be made using this security context and provide login and password of remote server.

5. Click OK and you are done !!

Here is a simple tutorial for creating a linked server.

OR

You can add linked server using query.

Syntax:

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] 
     [ , [ @provider= ] 'provider_name' ]
     [ , [ @datasrc= ] 'data_source' ] 
     [ , [ @location= ] 'location' ] 
     [ , [ @provstr= ] 'provider_string' ] 
     [ , [ @catalog= ] 'catalog' ] 

Read more about sp_addlinkedserver.

You have to create linked server only once. After creating linked server, we can query it as follows:

select * from LinkedServerName.DatabaseName.OwnerName.TableName

Answer 2

These are all fine answers, but this one is missing and it has it's own powerful uses. Possibly it doesn't fit what the OP wanted, but the question was vague and I feel others may find their way here. Basically you can use 1 window to simultaneously run a query against multiple servers, here's how:

In SSMS open Registered Servers and create a New Server Group under Local Server Groups.

Under this group create New Server Registration for each server you wish to query. If the DB names are different ensure to set a default for each in the properties.

Now go back to the Group you created in the first step, right click and select New Query. A new query window will open and any query you run will be executed on each server in the group. The results are presented in a single data set with an extra column name indicating which server the record came from. If you use the status bar you will note the server name is replaced with multiple.

Answer 3

SELECT
        *
FROM
        [SERVER2NAME].[THEDB].[THEOWNER].[THETABLE]

You can also look at using Linked Servers. Linked servers can be other types of data sources too such as DB2 platforms. This is one method for trying to access DB2 from a SQL Server TSQL or Sproc call...

Answer 4

Yes you can.

I think you're asking how, so I'll answer that.

What you are looking for are Linked Servers. You can get to them in SSMS from the following location in the tree of the Object Explorer:

Server Objects-->Linked Servers

or you can use sp_addlinkedserver.

You only have to set up one. Once you have that, you can call a table on the other server like so:

select
    *
from
    LocalTable,
    [OtherServerName].[OtherDB].[dbo].[OtherTable]

Note that the owner isn't always dbo, so make sure to replace it with whatever schema you use.

Answer 5

I had same issue to connect an SQL_server 2008 to an SQL_server 2016 hosted in a remote server. Other answers didn't worked for me straightforward. I write my tweaked solution here as I think it may be useful for someone else.

An extended answer for remote IP db connections:

Step 1: link servers

EXEC sp_addlinkedserver @server='SRV_NAME',
   @srvproduct=N'',
   @provider=N'SQLNCLI',   
   @datasrc=N'aaa.bbb.ccc.ffffd';

EXEC sp_addlinkedsrvlogin 'SRV_NAME', 'false', NULL, 'your_remote_db_login_user', 'your_remote_db_login_password'

...where SRV_NAME is an invented name. We will use it to refer to the remote server from our queries. aaa.bbb.ccc.ffffd is the ip address of the remote server hosting your SQLserver DB.

Step 2: Run your queries For instance:

SELECT * FROM [SRV_NAME].your_remote_db_name.dbo.your_table

...and that's it!

Syntax details: sp_addlinkedserver and sp_addlinkedsrvlogin

Answer 6

try this:

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=YOUR SERVER;Trusted_Connection=yes;','SELECT * FROM Table1') AS a
UNION
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=ANOTHER SERVER;Trusted_Connection=yes;','SELECT * FROM Table1') AS a