一个远程查询可以从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:

假设有2个DBs: DB1和DB2。连接到DB1的我是否可以在DB2上执行查询，就像登录到DB2一样?所以:

I'm connected to DB1
我和DB1相连
From within TSQL I somehow connect to DB2
从TSQL中，我以某种方式连接到DB2
I perform the query locally (and wait)
我在本地执行查询(并等待)
I close the connection and am back on DB1
我关闭连接，回到DB1
and I do the remainder of the query.
然后我做剩下的查询。

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

因此，我知道我可以执行interDB查询，比如:从DB2.somedb.dbo中选择*。但是这不是我想要的。

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.

Is将数据放在两个独立的DB计算机上。我必须以这样的方式查询数据，以便从两台机器上的表中合并数据。

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.

我尝试过很多场景，每次运行都很慢，或者查询无法完成，因为XML列出现在一个表或另一个表中。

The solution which does work (although manually) is:

有效的解决方案(虽然手动)是:

log on to DB2 and do half of the query into some temp table
登录到DB2，并将查询的一半执行到某个临时表中。
copy the temptable over to DB1
将temptable复制到DB1
logon to db1 and perform the second part of the query.
登录到db1并执行查询的第二部分。

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

毫无疑问，我想把这全部做成一个脚本，这样我就可以自动化了

2 个解决方案

#1

Have you looked at the OPENQUERY function in SQL Server:

您是否看过SQL Server中的OPENQUERY函数:

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:

甚至是OPENROWSET函数:

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

在网上使用SQL Server books获得更多细节，或者尝试使用googlefu

#2

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

首先，您必须链接这两个SQL服务器(SQL1和SQL2)。您需要在SQL1上创建一个指向SQL2的链接服务器，其有效登录在远程数据库上具有适当的权限。关于MSDN的更多信息-如何创建一个链接服务器。

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

创建链接服务器后，您应该能够同时使用链接服务器功能和openquery功能。

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)

使用直接链接服务器从SQL1执行的查询(通过网络从SQL1到SQL2执行指定的传递查询——更多的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)

使用Openquery执行的查询(在给定的链接服务器上执行指定的传递查询—以免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

同样，在本文中可以找到xml列的特殊性的答案，几天前我在博客上写了关于我发现的相同问题的博客。

#1