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

回答1:

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.

回答2:

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

回答3:

You can use:

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

回答4:

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

回答5:

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