SQL Server Linked Server Example Query

问题:

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:

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 ?

回答1:

The format should probably be:

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

For example: 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.

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.

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

回答2:

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

This may help you.

回答3:

If you still find issue with <server>.<database>.<schema>.<table>

Enclose server name in []

回答4:

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.

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

回答5:

For those having trouble with these other answers , try 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.

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
• test is database name
• dbo is schema -default schema
• employee is table name

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

回答9:

Following Query is work best.

Try this Query:

SELECT * FROM OPENQUERY([LINKED_SERVER_NAME], 'SELECT * FROM [DATABASE_NAME].[SCHEMA].[TABLE_NAME]')

It Very helps to link MySQL to MS SQL

回答10:

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

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

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

回答11:

right click on a table and click script table as select