CODEWITHSUNDEEP
sqlsql-serverdatabasestored-proceduressql-server-2008-r2
MjeOsX
MjeOsX

Reputation: 405

SQL Server: connect to another instance

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

Upvotes: 1

Views: 10032

Answers (2)

iruvar
iruvar

Reputation: 23364

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

Upvotes: 1

TheQ
TheQ

Reputation: 7017

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.

Upvotes: 6

Related Questions