CODEWITHSUNDEEP
sql-serverlinked-server
Renju
Renju

Reputation:

Querying a linked sql server

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

Upvotes: 25

Views: 203324

Answers (7)

Alireza Abdollahnejad
Alireza Abdollahnejad

Reputation: 1037

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.

Upvotes: 2

Ryano
Ryano

Reputation: 495

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

Upvotes: 3

user57508
user57508

Reputation: 

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.

Upvotes: 51

user1477388
user1477388

Reputation: 21430

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

Upvotes: 3

Guoliang
Guoliang

Reputation: 895

You can use:

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

Upvotes: 6

UV.
UV.

Reputation: 492

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

Upvotes: 2

BTB
BTB

Reputation: 2146

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

Upvotes: 28

Related Questions