Reputation:

Selecting data from two different servers in SQL Server

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

Upvotes: 437

Answers (17)

Kyle Wellendorff

Reputation: 1

Most of these will not work if trying to bring over CLR data types from one server to another (i.e. GEOMETRY GEOGARAPHY - which cannot be unioned or select distinct from but can use UNION ALL). Can you use OPENROWSET to get it to work, but requires linked servers. Or can use STAsText() to change CLR type to TEXT which is able to passed from server to server in the ways mentioned above.

You could also create a linked server data catalog. Query both servers Sys. tables using full outer joins, and find column names that only exist on both servers. tables, and then dynamically create a column in that view that is "SELECT STRING_AGG(COMMON_COLUMNS) FROM Table.column"

Then from central management servers, you could query any table that exist in both regardless of schema changes. (May have to account for data types, but could always use a CASE stement in your data catalog view to look at each sta type and CAST to the lowest possible data type that will work for both columns.

But then create procedure that takes one parameter for @TABLE_NAME and selects the SQL column from view you created where the Table_name = @TABLE_NAME and the dynamically executes it. Should always be able to query any common tables then

Upvotes: 0

quasar

Reputation: 474

I hope the clarifications all mentioned above, have answered the OP's original question. I just want to add a code snippet for adding SQL Server as a linked server.

At most basic, we can simply add SQL Server as a linked server by executing sp_addlinkedserver with only one parameter @server, i.e.

-- using IP address
exec sp_addlinkedserver @server='192.168.1.11' 
-- PC domain name 
exec sp_addlinkedserver @server='DESKTOP-P5V8JTN'

SQL Server will automatically fill SRV_PROVIDERNAME, SRV_PRODUCT, SRV_DATASOURCE etc. with default values. By doing this, we've to write the IP or PC domain name in the 4 part table address in the query (example below). This can be more annoying or less readable when the linked server will not have a default port or instance, the address will look similar to this 192.168.1.11,1430 or 192.168.1.11,1430\MSSQLSERVER2019.

So, to keep 4 part address short and readable, we can add an alias name for the server instead of the full address by specifying other parameters as follows-

exec sp_addlinkedserver
    @server='ReadSrv1',
    @srvproduct='SQL Server',
    @provider='SQLNCLI',
    @datasrc='192.168.1.11,1430\MSSQLSERVER2019'

But when you'll execute the query, the following error will show- You cannot specify a provider or any properties for product 'SQL Server'. If we keep the server product property value empty '' or any other value, the query will execute successfully.

Next step, make login to the remote linked server by executing the following query-

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'ReadSrv1', @useself = 'false', @locallogin = NULL, @rmtuser = 'sa', @rmtpassword = 'LinkedServerPasswordForSA'

Finally, use the linked server with 4 part address, the syntax is-
[ServerName].[DatabaseName].[Schema].[ObjectName]
Example-

SELECT TOP 100 t.* FROM ReadSrv1.AppDB.dbo.ExceptionLog t

To list existing linked servers execute:
exec sp_linkedservers
To delete a linked server execute:
exec sp_dropserver @server = 'ReadSrv1', @droplogins='droplogins' (delete login as well) OR
exec sp_dropserver @server = 'ReadSrv1', @droplogins='NULL' (keep login)

Upvotes: 3

MarcM

Reputation: 2251

I had the 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.ddd';
   
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.ddd 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

Upvotes: 15

Eric

Reputation: 95223

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.

Upvotes: 388

Muhammad Ashikuzzaman

Reputation: 3153

As @Super9 told about OPENDATASOURCE using SQL Server Authentication with data provider SQLOLEDB . I am just posting here a code snippet for one table is in the current sever database where the code is running and another in other server '192.166.41.123'

SELECT top 2 * from dbo.tblHamdoonSoft  tbl1 inner JOIN  
OpenDataSource('SQLOLEDB','Data Source=192.166.41.123;User ID=sa;Password=hamdoonsoft')
.[TestDatabase].[dbo].[tblHamdoonSoft1] tbl2 on tbl1.id = tbl2.id

Upvotes: 3

irfandar

Reputation: 1810

Simplified solution for adding linked servers

First server

EXEC sp_addlinkedserver @server='ip,port\instancename'

Second Login

EXEC sp_addlinkedsrvlogin 'ip,port\instancename', 'false', NULL, 'remote_db_loginname', 'remote_db_pass'

Execute queries from linked to local db

INSERT INTO Tbl (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM [ip,port\instancename].[linkedDBName].[linkedTblSchema].[linkedTblName]

Upvotes: 6

Paul

Reputation: 1059

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.

Upvotes: 27

user3586922

Reputation: 59

Server 2008:

When in SSMS connected to server1.DB1 and try:

SELECT  * FROM
[server2].[DB2].[dbo].[table1]

as others noted, if it doesn't work it's because the server isn't linked.

I get the error:

Could not find server DB2 in sys.servers. Verify that the correct server name was specified. If necessary, execute stored procedure sp_addlinkedserver to add the server to sys.servers.

To add the server:

reference: To add server using sp_addlinkedserver Link: [1]: To add server using sp_addlinkedserver

To see what is in your sys.servers just query it:

SELECT * FROM [sys].[servers]

Upvotes: 6

Sameh

Reputation: 19

Server Objects---> linked server ---> new linked server

In linked server write server name or IP address for other server and choose SQL Server In Security select (be made using this security context ) Write login and password for other server

Now connected then use

Select * from [server name or ip addresses ].databasename.dbo.tblname

Upvotes: 0

user4934422

Reputation:

I know this is an old question but I use synonyms. Supposedly the query is executed within database server A, and looks for a table in a database server B that does not exist on server A. Add then a synonym on A database that calls your table from server B. Your query doesn't have to include any schemas, or different database names, just call the table name per usual and it will work.

There's no need to link servers as synonyms per say are sort of linking.

Upvotes: 1

Masum

Reputation: 119

 select * 
 from [ServerName(IP)].[DatabaseName].[dbo].[TableName]

Upvotes: 4

ugio

Reputation: 39

sp_addlinkedserver('servername')

so its should go like this -

select * from table1
unionall
select * from [server1].[database].[dbo].[table1]

Upvotes: 0

Raging Bull

Reputation: 18767

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.

Follow these steps to create a Linked Server:

Server Objects -> Linked Servers -> New Linked Server
Provide Remote Server Name.
Select Remote Server Type (SQL Server or Other).
Select Security -> Be made using this security context and provide login and password of remote server.
Click OK and you are done !!

Here is a simple tutorial for creating a linked server.

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' ]

Selecting data from two different servers in SQL Server

Answers (17)

Follow these steps to create a Linked Server:

Related Questions