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'.
This question is related to
sql-server
linked-server
try Select * from openquery("aa-db-dev01",'Select * from users') ,the database connection should be defined in he linked server configuration
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.
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.
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')
try Select * from openquery("aa-db-dev01",'Select * from users') ,the database connection should be defined in he linked server configuration
You can use:
SELECT * FROM [aa-db-dev01].[TestDB].[dbo].[users];
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')
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]'
)
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
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]'
)
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.
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.
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
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.
You can use:
SELECT * FROM [aa-db-dev01].[TestDB].[dbo].[users];
Source: Stackoverflow.com