How to get the data from linked servers using queries

How to get the data from linked servers using queries

孤独患者 发布于 2021-11-28 字数 764 浏览 935 回复 6 原文

I have created a linkedserver as ravikiran-vm which is the virtual machine in my desktop.

Now I have a database called kiran which contains an employ table.
To retrieve employ data, I do the following:

select * from ravikiran-vm.kiran.employ

but it shows the error "Incorrect syntax near '-'."

Can anyone help me, please?

Thanks in advance.

Thanks guys with ur support it working fine...
Now i hav to schedule this as a new job.when i execute it as normal it shows o/p.
but when i cinfigure the same query as sqlserver agent job it gives error and query not executing...Plz help me in this regard

Thanks in advance

如果你对这篇文章有疑问,欢迎到本站 社区 发帖提问或使用手Q扫描下方二维码加群参与讨论,获取更多帮助。

扫码加入群聊

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(6

九局 2022-06-07 6 楼
Select * from likedservername.databasename.dbo(schema).tablename

Ex1:

select * from [Bse].[Bse].[dbo].Binary

Else

Select * from openquery (linkedservername, 'select * from databasename.dbo(schema).tablename');

Ex2:

select * from openquery ([Bse], 'select * from [Bse].[dbo].Binary');
百善笑为先 2022-06-07 5 楼
1- Link the server

EXEC sp_addlinkedserver 'OracleSvr', 
'Oracle 7.3', 
'MSDAORA', 
'ORCLDB'


2-SELECT
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM albert.titles')

3-UPDATE 
UPDATE OPENQUERY (OracleSvr, 'SELECT name FROM albert.titles WHERE id = 101') 

4-INSERT
INSERT OPENQUERY (OracleSvr, 'SELECT name FROM albert.titles')
VALUES ('NewTitle');

5-DELETE
DELETE OPENQUERY (OracleSvr, 'SELECT name FROM albert.titles WHERE name = ''NewTitle''')

i just copied from here (http://www.sqlservercentral.com/Forums/Topic916320-392-1.aspx)

醉梦枕江山 2022-06-07 4 楼

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

浅唱々樱花落 2022-06-07 3 楼

to get data from linked server you use 4 part notation
Server.Database.Schema.Table

since you have an invalid character in your name(-) you need to add brackets around the name

select * from [ravikiran-vm].kiran..employ

You probably also don't want all the data returned

偏闹i 2022-06-07 2 楼

You have to use OPENQUERY:

SELECT * FROM OPENQUERY([ravikiran-vm],'SELECT * FROM KIRAN..EMPLOY')
梦行七里 2022-06-07 1 楼

I think you should change the name of the linked server, as the - char is reserved in SQL.

You could try surrounding the name with brackets, but it becomes boring

Also, you should include the schema name in the query, or double point to use the default one:

so, you can try:

select * from [ravikiran-vm].kiran.dbo.employ
select * from [ravikiran-vm].kiran..employ

Or whatever your schema be.