SQL Server: connect to another instance

Question

I have a question for SQL Server. I have a stored procedure which should access another SQL Server to copy the data to another server.

Is this possible? If yes: how can I do this?

I cannot find some sample.

I use SQL Server 2008 R2

I think this could work with linked servers. See here: http://msdn.microsoft.com/en-us/library/ms190479.aspx — sofl, May 30 '13 at 11:42
possible duplicate of [SQL Server Linked Server Example Query](http://stackoverflow.com/questions/4091960/sql-server-linked-server-example-query) and http://stackoverflow.com/questions/10402197/how-to-create-the-linked-server-for-sql-server-2008-where-we-have-the-database-f — Preet Sangha, May 30 '13 at 11:58

score 6 · Answer 1 · answered May 31 '13 at 22:05

Here is an example...

Connect to the first server, and run this script

CREATE DATABASE [DatabaseA];
GO
CREATE TABLE [DatabaseA].[dbo].[TableA] (Id int, ValueA varchar(10));
INSERT INTO [DatabaseA].[dbo].[TableA] VALUES(1,'a'),(2,'b'),(3,'c');

Then connect to the second server, and run this

CREATE DATABASE [DatabaseB];
GO
CREATE TABLE [DatabaseB].dbo.[TableB] (Id int, ValueB varchar(10));
INSERT INTO [DatabaseB].dbo.[TableB] VALUES(1,'A'),(2,'B'),(3,'B');

In the connection to the first server, we create a link to the second like this

EXEC master.dbo.sp_addlinkedserver 
    @server = N'LINKTOB'
    ,@srvproduct=N'B'
    ,@provider=N'SQLOLEDB'
    ,@datasrc=N'<NAME OF SERVER CONTAINING DatabaseB>';

Note that you need to change the value of @datasrc to suit your environment.

Now we can on the first server run a query like this, using data from the two separate servers in the same query

SELECT
    a.Id
    ,a.ValueA
    ,b.ValueB
FROM
    [DatabaseA].[dbo].[TableA] AS a
    INNER JOIN [LINKTOB].[DatabaseB].[dbo].[TableB] AS b ON a.Id = b.Id

The result should look like this

Screenshot of query result

If both servers are in the same domain with the same security settings, everything should work without any further change. If not, take a look in Management Studio under Server Objects - Linked Servers - LINKTOB. Open properties for LINKTOB and have a look at the Security-tab.

score 1 · Answer 2 · answered May 31 '13 at 22:20

TheQ's answer should be your first option.

For ad-hoc MSSQL to MSSQL connection requirements, OpenDataSource is a good option. For example, to query an external table:

SELECT *
FROM OPENDATASOURCE('SQLNCLI',
'Data Source=London\Payroll;Integrated Security=SSPI')
.AdventureWorks2012.HumanResources.Employee

SQL Server: connect to another instance

2 Answers2

Linked