CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Toad
Toad

Reputation: 15925

can one remote execute queries from within t-sql?

First the raw question:

Given that there are 2 DBs: DB1 and DB2. Can I, being connected to DB1, perform a query on DB2 as if I'm logged on to DB2? So basically:

SO I know I can do interDB queries like: select * from DB2.somedb.dbo.sometable but this is not what I'm after.

Background:

Is have data sitting on 2 separate DB machines. I have to query the data in such a way as to combine data from tables on both machines.

I have tried lot's of scenarios and everytime it either runs very slow, or a query can't be done due to XML columns being present in one table or the other.

The solution which does work (although manually) is:

perferably I want to make this all one script so I can automate it

Upvotes: 2

Views: 3416

Answers (2)

yrushka
yrushka

Reputation: 221

First, YOU HAVE to link the two SQL Servers (SQL1 and SQL2). You need to create a link server on SQL1 towards SQL2 with a valid login having proper rights on the remote database. More info about this on MSDN - How to create a link server

Once you created the linked server you should be able to use both linked server functionality as well as openquery function.

The query executed from SQL1 using directly Linked server (Executes the specified pass-through query through the network from SQL1 to SQL2- more I/O) 

select * from [LINKED_SERVER_SQL2].[DATABASE_NAME].dbo.[TABLE_NAME]

The query executed using Openquery (Executes the specified pass-through query on the given linked server - lest I/O) 

select * from openquery (LINKED_SERVER_SQL2, 'select * from [DATABASE_NAME].dbo.[TABLE_NAME]')

Also, the answer for the xml columns particularity can be found at this post, I wrote a blog couple of days ago about the same issue I found

Upvotes: 1

Guy
Guy

Reputation: 9816

Have you looked at the OPENQUERY function in SQL Server:

EXEC sp_addlinkedserver 'OracleSvr', 
   'Oracle 7.3', 
   'MSDAORA', 
   'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles') 
GO

Or even the OPENROWSET function:

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
     'SELECT GroupName, Name, DepartmentID
      FROM AdventureWorks.HumanResources.Department
      ORDER BY GroupName, Name') AS a;

Use SQL Server books online for more detail or try out your googlefu

Upvotes: 3

Related Questions