Selecting data from two different servers in SQL Server

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 see links please register here]

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

SELECT * FROM [sys].[servers]

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**.

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]

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

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][1].

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.

[1]:

[To see links please register here]

*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][1] and [sp_addlinkedsrvlogin][2]


[1]:

[To see links please register here]

[2]:

[To see links please register here]

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.

```sql
-- 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-

```sql
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-

```sql
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-
```sql
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)