SQL Server Linked Server Example Query

Question

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 ?

score 220 · Accepted Answer · edited Mar 05 '20 at 01:18

220

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.

If 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.

edited Mar 05 '20 at 01:18

G S

35,511
22
84
118

answered Nov 03 '10 at 21:47

NotMe

87,343
27
171
245

13

if the databaseserver name has a hyphen, need to surround it with square brackets – bmw0128 Nov 03 '10 at 22:15
5

@bmw0128: Better yet, use double quotes: it's supported by almost every platform, unlike Microsoft's square brackets. – Jul 03 '12 at 22:29
3

You also need to use the square brackets or double quotes when the database server name has a period in it. – David Brunow May 09 '14 at 21:55
6

If you're uncertain about any of the qualifiers, drill down to a table in a Linked Server in SSMS Object Explorer, right-click, and Script Table as, SELECT To, and New Query Editor Window. The resulting SELECT statement will include the correct, fully-qualified path to the table. I had a mystery database qualifier in working with Sybase and this gave me the correct name. – John Mo Sep 29 '14 at 15:42
I think you are incorrect in saying that the entire table will be transferred. Can you provide some reference to where you got that information? I just tried joining against a table with 204 million rows (16GB data, 6.6GB index) on a linked server and it took 47ms to link to 5 of the rows, 7ms on the second query since the data were presumably cached. Maybe if your join required a table scan on the linked table it would have to transfer it all? – Jason Goemaat Mar 03 '15 at 23:45
@JasonGoemaat: Start here: http://thomaslarock.com/2013/05/top-3-performance-killers-for-linked-server-queries/ but, yes, a proper distributed query can actually be created. I'll update. – NotMe Mar 04 '15 at 02:14
I was missing my schema. Added the "dbo," and now I'm good to go. Thanks! – Sev09 Jun 06 '16 at 14:11
Just to write down something no explicitly written anywhere: `` refers to the linked server name. – Alfabravo Oct 11 '16 at 16:53
This DOES NOT WORK for some non-SQL-Server linked servers. It raises error like An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "MyLinkedServer". – brewmanz Jan 12 '17 at 21:42
1

@JohnMo This DOES NOT WORK for some non-SQL-Server linked servers - the table structure is just not available; I get around that using ... SELECT * INTO [MySchema].[MyTable] FROM openquery(MyLinkedServer, 'SELECT * FROM TheSchema.TheTable WHERE 1=0'); – brewmanz Jan 12 '17 at 21:48

score 48 · Answer 2 · answered Mar 16 '16 at 12:18

48

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

This may help you.

answered Mar 16 '16 at 12:18

Akhilesh Kamate

775
7
13

Upvoted. This works when you're linking MySQL to MS SQL. – Baz Guvenkaya Feb 07 '17 at 01:27
5

In other words, this is creating a pass-through query. Keep in mind that the query statement has to be written in the native SQL for the server. Syntax for Oracle being different than Teradata different than SQL Server etc. – AxGryndr Jul 13 '17 at 14:29
It's my understanding this is a "pass-through query" that passes SQL to the other server; and while useful for accessing other vendors' databases, it's best to use a "native" query between MS-Sql-Server instances when both are MS-SQL-Server (and maybe compatible versions). For one, quote escaping complications are avoided. Is there a performance benefit of native mode? – FloverOwe Aug 22 '23 at 16:01

score 16 · Answer 3 · edited Dec 12 '18 at 18:31

16

For those having trouble with these other answers , try OPENQUERY

Example:

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

edited Dec 12 '18 at 18:31

Nielsvh

1,151
1
18
31

answered Feb 07 '17 at 18:45

Tom Stickel

19,633
6
111
113

Works for SQL Server – Tom Stickel Feb 07 '17 at 18:46
This is the only solution which works for me. Quick translation (may be helpful to some): `SELECT * INTO NEW_DB.dbo.tblCopy FROM OLD_DB.dbo.tblData` forms to `SELECT * INTO NEW_DB.dbo.tblCopy FROM OPENQUERY([server], 'select * from OLD_DB.dbo.tblData')` – baltermia May 04 '21 at 14:55

score 11 · Answer 4 · edited Jul 13 '18 at 12:28

11

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

Enclose server name in []

edited Jul 13 '18 at 12:28

Arulkumar

12,966
14
47
68

answered Aug 13 '13 at 18:21

Mian

231
5
7

Careful: I executed a create table from select using [], and instead of being created on the Linked Server, the table was created locally with a name like `dbo.databaseserver1.db1.dbo.table1` – biscuit314 Jun 13 '17 at 15:59

Joe Stefanelli · Answer 5 · 2010-11-03T22:03:24.253

9

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

edited Nov 03 '10 at 22:03

answered Nov 03 '10 at 21:58

Joe Stefanelli

132,803
19
237
235

the inner join syntax is preferable to the implicit joins? – bmw0128 Nov 03 '10 at 22:17
3

@bmw0128: Yes, for several reasons. IMHO, the most important is that it is way too easy to accidentally write a cross product join when you have your tables and joins in two different place. – Jul 03 '12 at 22:30
Note that the 4-dotted-parts DOES NOT WORK for some non-SQL-Server linked servers. It can raise an error like ... An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "MyLinkedServer". – brewmanz Jan 12 '17 at 21:45

score 7 · Answer 6 · edited Mar 08 '16 at 19:55

7

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

edited Mar 08 '16 at 19:55

altocumulus

21,179
13
61
84

answered Dec 10 '15 at 10:11

Abhishek Jaiswal

1,161
12
6

1

This DOES NOT WORK for some non-SQL-Server linked servers. It raises error like ... An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "MyLinkedServer". – brewmanz Jan 12 '17 at 21:40

score 7 · Answer 7 · answered Jun 25 '18 at 05:14

7

right click on a table and click script table as select

answered Jun 25 '18 at 05:14

Shimon Doodkin

4,310
34
37

That is not what the OP asked – Fandango68 Aug 16 '18 at 01:26
4

this shows how to get the correct syntax for select query on a linked table. the result is like of seans answer – Shimon Doodkin Aug 16 '18 at 13:42
3

@ShimonDoodkin, an excellent example of don't give me a fish, but teach me how to fish – Amro Apr 15 '19 at 07:44

score 4 · Answer 8 · answered Jan 15 '18 at 03:22

4

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

answered Jan 15 '18 at 03:22

Jaspreet Singh

41
1

score 3 · Answer 9 · edited Mar 02 '16 at 15:29

3

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')

edited Mar 02 '16 at 15:29

Jason Roman

8,146
10
35
40

answered Mar 02 '16 at 15:12

Muhammad Yaseen

661
1
5
6

This answer doesn't include a database name – Chris Nevill Apr 22 '16 at 10:37
2

I do have provided database information while creating the linked server. For detail you can see below MSDN link: https://msdn.microsoft.com/en-us/library/ff772782(v=sql.110).aspx – Muhammad Yaseen Oct 20 '16 at 14:09
What can I do if my linked server require authentication and I'm just trying to querying from my PHP application using PDO ? – nekiala Jun 22 '17 at 08:22
1

How would you perform a join from database 1, to database on linked server, using this approach? – eaglei22 May 22 '19 at 14:24

score 2 · Answer 10 · answered Oct 24 '17 at 14:41

2

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.

answered Oct 24 '17 at 14:41

Sean Warren

21
1

ns15 · Answer 11 · 2021-06-29T06:47:53.230

In sql-server(local) there are two ways to query data from a linked server(remote).

Distributed query (four part notation):

Might not work with all remote servers. If your remote server is MySQL then distributed query will not work.
Filters and joins might not work efficiently. If you have a simple query with WHERE clause, sql-server(local) might first fetch entire table from the remote server and then apply the WHERE clause locally. In case of large tables this is very inefficient since a lot of data will be moved from remote to local. However this is not always the case. If the local server has access to remote server's table statistics then it might be as efficient as using openquery More details
On the positive side T-SQL syntax will work.

SELECT * FROM [SERVER_NAME].[DATABASE_NAME].[SCHEMA_NAME].[TABLE_NAME]

OPENQUERY

This is basically a pass-through. The query is fully processed on the remote server thus will make use of index or any optimization on the remote server. Effectively reducing the amount of data transferred from the remote to local sql-server.
Minor drawback of this approach is that T-SQL syntax will not work if the remote server is anything other than sql-server.

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

Overall OPENQUERY seems like a much better option to use in majority of the cases.

score 1 · Answer 12 · answered Oct 13 '20 at 09:28

I have done to find out the data type in the table at link_server using openquery and the results were successful.

SELECT * FROM OPENQUERY (LINKSERVERNAME, '
SELECT DATA_TYPE, COLUMN_NAME
FROM [DATABASENAME].INFORMATION_SCHEMA.COLUMNS
WHERE 
     TABLE_NAME  =''TABLENAME''
')

Its work for me

score 0 · Answer 13 · answered Jun 25 '18 at 05:43

0

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

answered Jun 25 '18 at 05:43

Vijay S

282
2
7
15

score 0 · Answer 14 · answered Jun 03 '20 at 20:23

0

PostgreSQL:

You must provide a database name in the Data Source DSN.
Run Management Studio as Administrator
You must omit the DBName from the query:

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

answered Jun 03 '20 at 20:23

Shadi Alnamrouti

11,796
4
56
54

score 0 · Answer 15 · answered Mar 01 '21 at 17:43

For MariaDB (and so probably MySQL), attempting to specify the schema using the three-dot syntax did not work, resulting in the error "invalid use of schema or catalog". The following solution worked:

In SSMS, go to Server Objects > Linked Servers > Providers > MSDASQL
Ensure that "Dynamic parameter", "Level zero only", and "Allow inprocess" are all checked

You can then query any schema and table using the following syntax:

SELECT TOP 10 *
FROM LinkedServerName...[SchemaName.TableName]

Source: SELECT * FROM MySQL Linked Server using SQL Server without OpenQuery

score 0 · Answer 16 · answered Mar 01 '22 at 15:44

0

Have you tried adding " around the first name?

like:

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

answered Mar 01 '22 at 15:44

CodePrime8

31
4

SQL Server Linked Server Example Query

16 Answers16

Linked

Related