SQL Server链接服务器示例查询

While in Management Studio, I am trying to run a query/do a join between two linked servers. Is this a correct syntax using linked db servers:

在Management Studio中，我尝试在两个链接服务器之间运行查询/执行连接。这是使用链接db服务器的正确语法吗?

select foo.id 
from databaseserver1.db1.table1 foo, 
     databaseserver2.db1.table1 bar 
where foo.name=bar.name

Basically, do you just preface the db server name to the db.table ?

基本上，您是否只是在db服务器名之前。表吗?

9 个解决方案

#1

138

The format should probably be:

格式可能应该是:

<server>.<database>.<schema>.<table>

For example: DatabaseServer1.db1.dbo.table1

例如:DatabaseServer1.db1.dbo.table1

Update: I know this is an old question and the answer I have is correct; however, I think any one else stumbling upon this should know a few things.

更新:我知道这是一个老问题，我的答案是正确的;然而，我认为任何偶然发现这一点的人都应该知道一些事情。

Namely, when querying against a linked server in a join situation the ENTIRE table from the linked server will likely be downloaded to the server the query is executing from in order to do the join operation. In the OP's case, both table1 from DB1 and table1 from DB2 will be transferred in their entirety to the server executing the query, presumably named DB3.

换句话说，当查询连接情况下的链接服务器时，连接服务器上的整个表很可能会被下载到查询执行的服务器，以便执行连接操作。在OP的情况下，从DB2的DB1和table1的table1都将全部转移到执行查询的服务器上，大概命名为DB3。

If you have large tables, this may result in an operation that takes a long time to execute. After all it is now constrained by network traffic speeds which is orders of magnitude slower than memory or even disk transfer speeds.

如果您有大的表，这可能会导致需要很长时间才能执行的操作。毕竟，它现在受到网络传输速度的限制，速度比内存甚至磁盘传输速度都要慢。

If possible, perform a single query against the remote server, without joining to a local table, to pull the data you need into a temp table. Then query off of that.

如果可能的话，对远程服务器执行单个查询，而不加入本地表，以便将所需的数据拉到临时表中。然后查询。

It that's not possible then you need to look at the various things that would cause SQL server to have to load the entire table locally. For example using GETDATE() or even certain joins. Others performance killers include not giving appropriate rights.

这是不可能的，然后您需要查看导致SQL server必须在本地加载整个表的各种事情。例如使用GETDATE()甚至某些连接。其他的绩效杀手包括不给予适当的权利。

See http://thomaslarock.com/2013/05/top-3-performance-killers-for-linked-server-queries/ for some more info.

有关更多信息，请参见http://thomaslarock.com/2013/05/top-3 performance-killers for linked-server-queries/。

#2

SELECT * FROM OPENQUERY([SERVER_NAME], 'SELECT * FROM DATABASE_NAME..TABLENAME')

This may help you.

这可能会帮助你。

#3

You need to specify the schema/owner (dbo by default) as part of the reference. Also, it would be preferable to use the newer (ANSI-92) join style.

您需要指定模式/所有者(默认情况下是dbo)作为引用的一部分。此外，最好使用较新的(ANSI-92)连接样式。

select foo.id 
    from databaseserver1.db1.dbo.table1 foo
        inner join databaseserver2.db1.dbo.table1 bar 
            on foo.name = bar.name

#4

If you still find issue with ...

如果你还觉得……

Enclose server name in []

在[]中包含服务器名

#5

For those having trouble with these other answers , try OPENQUERY

对于那些对其他答案有困难的人，请尝试OPENQUERY

Example:

例子:

 SELECT * FROM OPENQUERY([LinkedServer], 'select * from [DBName].[schema].[tablename]')

#6

select * from [Server].[database].[schema].[tablename]

This is the correct way to call. Be sure to verify that the servers are linked before executing the query!

这是正确的呼叫方式。在执行查询之前，一定要验证服务器是否链接!

To check for linked servers call:

检查连接的服务器调用:

EXEC sys.sp_linkedservers

#7

Usually direct queries should not be used in case of linked server because it heavily use temp database of SQL server. At first step data is retrieved into temp DB then filtering occur. There are many threads about this. It is better to use open OPENQUERY because it passes SQL to the source linked server and then it return filtered results e.g.

通常不应该在链接服务器的情况下使用直接查询，因为它大量使用SQL server的临时数据库。第一步将数据检索到temp DB，然后进行过滤。关于这一点有很多线索。最好使用open OPENQUERY，因为它将SQL传递给源链接服务器，然后返回过滤后的结果。

SELECT *
FROM OPENQUERY(Linked_Server_Name , 'select * from TableName where ID = 500')

#8

select name from drsql01.test.dbo.employee

drslq01 is servernmae --linked serer
drslq01是servernmae——链接的serer
test is database name
测试数据库名称
dbo is schema -default schema
dbo是模式-默认模式。
employee is table name
员工表名

I hope it helps to understand, how to execute query for linked server

我希望它有助于理解，如何执行链接服务器的查询

#9

For what it's worth, I found the following syntax to work the best:

对于它的价值，我发现下面的语法是最好的:

SELECT * FROM [LINKED_SERVER]...[TABLE]

SELECT * FROM[LINKED_SERVER]……(表)

I couldn't get the recommendations of others to work, using the database name. Additionally, this data source has no schema.

使用数据库名，我无法让其他人的推荐起作用。此外，该数据源没有模式。

#1

138