如何使用查询从链接服务器获取数据

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

我创建了一个linkserver作为ravikiran-vm，它是我桌面上的虚拟机。

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

现在我有一个名为kiran的数据库，其中包含一个雇用表。要检索雇用数据，我执行以下操作：

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

谢谢你们支持它工作得很好......现在我要安排这个作为一个新工作。当我按照正常情况执行它时显示o / p。但是当我配置与sqlserver代理作业相同的查询时，它会给出错误并且查询没有执行... Plz在这方面帮助我

Thanks in advance

提前致谢

5 个解决方案

#1

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

我认为您应该更改链接服务器的名称，因为 - char在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.

或者无论你的架构是什么。

#2

You have to use OPENQUERY:

你必须使用OPENQUERY：

SELECT * FROM OPENQUERY([ravikiran-vm],'SELECT * FROM KIRAN..EMPLOY')

#3

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

要从链接服务器获取数据，请使用4部分表示法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

您可能也不希望返回所有数据

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

通常在链接服务器的情况下不应使用直接查询，因为它大量使用SQL Server的临时数据库。首先，将数据检索到临时DB，然后进行过滤。关于这一点有很多线索。最好使用open OPENQUERY，因为它将SQL传递给源链接服务器，然后返回过滤结果，例如

SELECT * FROM OPENQUERY(Linked_Server_Name , 'select * from TableName where ID = 500')

SELECT * FROM OPENQUERY（Linked_Server_Name，'select * from TableName，其中ID = 500'）

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

我刚刚从这里复制（http://www.sqlservercentral.com/Forums/Topic916320-392-1.aspx）

#1