EXEC(query) AT linksServer with Oracle DB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/595350/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
EXEC(query) AT linkedServer With Oracle DB
提问by David.Chu.ca
I am using Microsoft SQL server 2005. I need to sync data between SQL server and an Oracle db. First thing I need is to find out if the count of data on Oracle side with certain filters(here I use ID as a simple example).
我使用的是 Microsoft SQL Server 2005。我需要在 SQL Server 和 Oracle 数据库之间同步数据。我需要做的第一件事是找出 Oracle 端的数据计数是否带有某些过滤器（这里我使用 ID 作为一个简单的例子）。
SELECT COUNT(*) FROM oracleServer..owner.table1 WHERE id = @id;
The problem I have is that the table on the lined server or Oracle is very big with 4M rows of data. The above query took about 2minutes to get data back. This code is just a simplied piece. Actually my SP has some other queries to update, insert data from the lined server to my SQL server. The SP took hours or 10+ hours to run with large Oracle db. Therefore T-SQL with lined server is not good for me.
我的问题是内联服务器或 Oracle 上的表非常大，有 4M 行数据。上面的查询需要大约 2 分钟才能取回数据。这段代码只是一个简单的片段。实际上，我的 SP 有一些其他查询要更新，将数据从内衬服务器插入到我的 SQL 服务器。SP 需要几个小时或 10 多个小时才能运行大型 Oracle 数据库。因此，带有内衬服务器的 T-SQL 对我不利。
Recently I found OPENQUERY and EXEC (...) AT linedServer. OPENQUERY() is very fast. It took about 0 time to get the same result. However, it does not support variable query or expressions. The query has to be a literal constant string.
最近我发现 OPENQUERY 和 EXEC (...) AT linedServer。OPENQUERY() 非常快。得到相同的结果花了大约 0 时间。但是，它不支持变量查询或表达式。查询必须是文字常量字符串。
EXEC() is in the same way to pass-through query to Oracle. It is fast as well. For example:
EXEC() 与传递查询到 Oracle 的方式相同。它也很快。例如：
EXEC ('SELECT COUNT(*) FROM owner.table1 WHERE id = ' + CAST(@id AS VARCHAR))
AT oracleServer
The problem I have is how to pass the result COUNT(*) back. I tried to google examples in web and msdn. All I can find are SQL or ExpressSQL linedServer examples like:
我遇到的问题是如何将结果 COUNT(*) 传回。我尝试在 web 和 msdn 中搜索示例。我能找到的只是 SQL 或 ExpressSQL 内衬服务器示例，例如：
EXEC ('SELECT ? = COUNT(*) FROM ...', @myCount OUTPUT) AT expressSQL
This query does not work for Oracle. It seems in Oracle, you can set value as output in this way:
此查询不适用于 Oracle。似乎在 Oracle 中，您可以通过这种方式将值设置为输出：
SELECT COUNT(*) INTO myCount ...
I tried this:
我试过这个：
EXEC ('SELECT COUNT(*) INTO ? FROM ...', @myCount OUTPUT) AT oracleServer
EXEC ('SELECT COUNT(*) INTO : FROM ...', @myCount OUTPUT) AT oracleServer
EXEC ('SELECT : = COUNT(*) FROM ...', @myCount OUTPUT) AT oracleServer
None of those working. I got error message saying query not executable on Oracle server.
没有人工作。我收到错误消息说查询在 Oracle 服务器上不可执行。
I could write a .Net SQL Server project to do the job. Before that, I just wonder if there is anyway way to pass value out as oupput parameter so that I put the better performance T-SQL codes in my SP?
我可以编写一个 .Net SQL Server 项目来完成这项工作。在此之前，我只是想知道是否有任何方法可以将值作为输出参数传递出去，以便我将性能更好的 T-SQL 代码放入我的 SP 中？
回答by David.Chu.ca
Just a quick update on this. I think I got the solution. I found it in a discussion on a similar issue at Dev NewsGroup. Based on the information, I tried this:
只是对此的快速更新。我想我得到了解决方案。我在Dev NewsGroup 的一个类似问题的讨论中找到了它。根据信息，我尝试了这个：
DECLARE @myCount int;
DECLARE @sql nvarchar(max);
set @sql =
N'BEGIN
select count(*) into :myCount from DATAPARC.CTC_MANUAL_DATA;
END;'
EXEC (@sql, @myCount OUTPUT) AT oracleServer;
PRINT @myCount; -- 3393065
Wa! I got the result back in 3 seconds comparing T-SQL query directly on Orable DB (+2minutes). The important thing is to use "BEGIN" and "END;" to wrap the query as anonymous block and don't miss ";" after END
哇！我在 3 秒内得到了结果，直接在 Orable DB 上比较了 T-SQL 查询（+2 分钟）。重要的是使用“BEGIN”和“END”；将查询包装为匿名块，不要错过“;” 结束后
You need anonymous block for output parameters. If you only have input or no parameters, you don't need the block and the query works fine.
您需要匿名块作为输出参数。如果您只有输入或没有参数，则不需要块并且查询工作正常。
Enjoy it! By the way, this is a quick update. If you don't see me again, I would not have any trouble on this issue.
好好享受！顺便说一下，这是一个快速更新。如果你不再见到我，我就不会在这个问题上遇到任何麻烦。
回答by Hennie van Dyk
With Linked Services the biggest issue is performance (IMHO)
[linkedserver]...[dbo.RemoteTable] vs OPENQUERY(linkedserver, 'Select * from dbo.RemoteTable')always use the second one
对于链接服务，最大的问题是性能（恕我直言）
[linkedserver]...[dbo.RemoteTable] vs OPENQUERY(linkedserver, 'Select * from dbo.RemoteTable')总是使用第二个
Now to answer the question. OPENQUERYand EXEC() ATis much quicker.
EXEC(Select * from dbo.RemoteTable) AT linkedserverwill show the results, but there is no way to re-use.
现在来回答这个问题。OPENQUERY并且EXEC() AT要快得多。
EXEC(Select * from dbo.RemoteTable) AT linkedserver会显示结果，但是没有办法重用。
My simple solution:
我的简单解决方案：
SELECT * INTO LocalTable FROM OPENQUERY(linkedserver, 'Select * from dbo.RemoteTable')
OR
或者
INSERT INTO LocalTable SELECT * FROM OPENQUERY(linkedserver, 'Select * from dbo.RemoteTable')
much^10 faster than
比
SELECT * INTO LocalTable FROM [linkedserver]...[dbo.RemoteTable]
