Querying a linked sql server

Question

I added a linked server, which is showing in the linked server list, but when I query it, it throws an error with the db server name.

EXEC sp_helpserver
EXEC sp_addlinkedserver 'aa-db-dev01'
Select * from openquery('aa-db-dev01','Select * from TestDB.dbo.users')

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'aa-db-dev01'.

score 51 · Answer 1 · edited Aug 10 '17 at 15:55

51

SELECT * FROM [server].[database].[schema].[table]

This works for me. SSMS intellisense may still underline this as a syntax error, but it should work if your linked server is configured and your query is otherwise correct.

edited Aug 10 '17 at 15:55

joelmdev

11,083
10
65
89

answered Jan 30 '09 at 14:47

From the testing I've done, while 4part naming does work, it ends up being slower than using openquery. – Nick Haslam Jun 19 '12 at 15:13
Also this approach won't work if your table definition contains `xml` column types. – user692942 Jan 15 '14 at 14:07
@Hack-R is the server registered as a linked one? – Nov 25 '14 at 07:00
@AndreasNiedermair It was... now that you mention it, apparently someone deleted it. Thanks. – Hack-R Nov 25 '14 at 12:38
This DOES NOT WORK non some -SQL-Server linked servers, resulting in ... An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "MyLinkedServer". – brewmanz Jan 13 '17 at 00:05
@brewmanz thanks for your input, but the question is explicitly tagged with [tag:sql-server], indicating a mssql context. Furthermore, you have to register the server as a linked server. – Jan 13 '17 at 08:25
@brewmanz furthermore, how should relational-queries work in a non-relational context?! – Jan 13 '17 at 13:13
3

@AndreasNiedermair A) SQL Server is only *half* of the Linked Server relationship - the server you're directly accessing. The 'other' linked server may or may not be SQL Server, and to announce that the 4-dotted-part is the way to reference it it will leave those who are linking non-SQL Server scratching their heads wondering why it's not working for them B) I don't understand what non-relational context you are talking about. A linked server can still be relational; it's just that processing a query may not be done completely at the Linked Server location – brewmanz Jan 14 '17 at 20:58

score 28 · Answer 2 · answered Feb 06 '09 at 13:14

28

You need to remove the quote marks from around the name of the linked server. It should be like this:

Select * from openquery(aa-db-dev01,'Select * from TestDB.dbo.users')

answered Feb 06 '09 at 13:14

BTB

2,126
3
21
22

You lose intellisense this way so go the other way if you can from a development standpoint but you have to use this way if the provider doesn't expose the catalog or schema. – Tony L. Aug 08 '17 at 15:17
With this option, there's an 8000 character limit for the query. Still trying to find a workaround for that. `exec (@Query) at LinkedServerName` seems like a work around for that but I cannot join the result with other tables. Tried using temp tables without luck. – Baz Guvenkaya Aug 23 '17 at 03:37

score 6 · Answer 3 · edited Apr 06 '16 at 14:37

6

You can use:

SELECT * FROM [aa-db-dev01].[TestDB].[dbo].[users];

edited Apr 06 '16 at 14:37

interesting-name-here

1,851
1
20
33

answered Sep 14 '12 at 06:11

Guoliang

885
2
12
20

This DOES NOT WORK non some -SQL-Server linked servers, resulting in ... An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "MyLinkedServer". – brewmanz Jan 13 '17 at 00:03

score 3 · Answer 4 · answered May 11 '17 at 14:09

3

I use open query to perform this task like so:

select top 1 *
INTO [DATABASE_TO_INSERT_INTO].[dbo].[TABLE_TO_SELECT_INTO]
from openquery(
    [LINKED_SERVER_NAME],
    'select * from [DATABASE_ON_LINKED_SERVER].[dbo].[TABLE_TO_SELECT_FROM]'
)

The example above uses open query to select data from a database on a linked server into a database of your choosing.

Note: For completeness of reference, you may perform a simple select like so:

select top 1 * from openquery(
    [LINKED_SERVER_NAME],
    'select * from [DATABASE_ON_LINKED_SERVER].[dbo].[TABLE_TO_SELECT_FROM]'
)

answered May 11 '17 at 14:09

user1477388

20,790
32
144
264

1

What could be done if the string has more than 8000 characters (OPENQUERY limitation)? – José Ricardo Júnior Oct 18 '18 at 16:05
Good question - you might want to ask it to the community as I'm not really sure :) – user1477388 Oct 18 '18 at 20:52

score 3 · Answer 5 · answered Mar 12 '19 at 23:35

The accepted answer works for me.

Also, in MSSQLMS, you can browse the tree in the Object Explorer to the table you want to query.

[Server] -> Server Objects -> Linked Servers -> [Linked server] -> Catalogs -> [Database] -> [table]

then Right click, Script Table as, SELECT To, New Query Window

And the query will be generated for you with the right FROM, which you can use in your JOIN

score 2 · Answer 6 · answered Nov 11 '13 at 05:50

2

try Select * from openquery("aa-db-dev01",'Select * from users') ,the database connection should be defined in he linked server configuration

answered Nov 11 '13 at 05:50

UV.

492
6
9

score 2 · Answer 7 · answered Feb 12 '20 at 13:00

2

If linked server name is IP address following code is true:

select * from [1.2.3.4,1433\MSSQLSERVER].test.dbo.Table1

It's just, note [] around IP address section.

answered Feb 12 '20 at 13:00

Alireza Abdollahnejad

921
8
9

Querying a linked sql server

7 Answers7

Linked