SQL 使用 OPENQUERY 执行存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4159465/
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
Execute stored proc with OPENQUERY
提问by ctrlalt313373
I have SQL Server 2008 with a linked Sybase server and I am trying to execute a stored procedure on the Sybase server using OPENQUERY. If I have a stored proc that doesn't take parameters it succeeds fine. If I have a stored proc with parameters it fails. I even tried a very basic stored proc that only took an int an that still failed. Below is the syntax I am using:
我有一个带有链接的 Sybase 服务器的 SQL Server 2008，我正在尝试使用 OPENQUERY 在 Sybase 服务器上执行一个存储过程。如果我有一个不带参数的存储过程，它会成功。如果我有一个带有参数的存储过程，它就会失败。我什至尝试了一个非常基本的存储过程，它只接受了一个仍然失败的 int 。下面是我使用的语法：
select * from
OPENQUERY([LINKSERVER],'exec database.user.my_stored_proc ''AT'',''XXXX%'',''1111'',1')
Msg 7357, Level 16, State 2, Line 3 Cannot process the object "exec database.user.my_stored_proc 'AT','XXXX%','1111',1". The OLE DB provider "ASEOLEDB" for linked server "LINKSERVER" indicates that either the object has no columns or the current user does not have permissions on that object.
消息 7357，级别 16，状态 2，第 3 行无法处理对象“exec database.user.my_stored_proc 'AT','XXXX%','1111',1”。链接服务器“LINKSERVER”的 OLE DB 提供程序“ASEOLEDB”指示该对象没有列或当前用户对该对象没有权限。
As the proc will execute just fine without parameters, I don't think it is a permission issue.
由于 proc 可以在没有参数的情况下正常执行，因此我认为这不是权限问题。
采纳答案by Saif Khan
Linked Servers and OPENQUERY, Gems to MS SQL Server...that are wolves in sheep clothing. I've found the following solutions to work when dealing with parameters
链接服务器和 OPENQUERY、宝石到 MS SQL Server……那是披着羊皮的狼。我发现以下解决方案在处理参数时有效
If the SP is basically just SELECT statements, the move the same to a VIEW and just pass SQL statements via OPENQUERY.
Build the OPENQUERY as a string and then use execute_sql.
如果 SP 基本上只是 SELECT 语句，则将其移至 VIEW 并仅通过 OPENQUERY 传递 SQL 语句。
将 OPENQUERY 构建为字符串，然后使用 execute_sql。
回答by mrwaim
This worked for me,
这对我有用，
SELECT * FROM OPENQUERY(LOCALSERVER, 'SET FMTONLY OFF EXEC snr.dbo.GetAllSignals @controlRunId = 25, @experimentRunId = 26')
I was creating temporary tables, and that's why i got access denied
我正在创建临时表，这就是我拒绝访问的原因
Here is more info http://www.sommarskog.se/share_data.html#OPENQUERY
回答by Amirhossein Yari
I create a sp that doesn't return any value and it doesn't work. Your SP in mysql have to return a value! for example I do this in "mysql":
我创建了一个不返回任何值的 sp 并且它不起作用。你在 mysql 中的 SP 必须返回一个值！例如我在“mysql”中这样做：
CREATE DEFINER=`root`@`localhost` PROCEDURE `MyPro`(IN `Name` VARCHAR(50), IN `Id` INT, OUT `Result` INT)
MODIFIES SQL DATA
BEGIN
DECLARE Result INT;
SET Result = 0;
INSERT into MyTable (Id,Name) VALUES(Id,Name);
SELECT Result;
END
That "Id" and "Name" is input parameter and "Result" is output parameter and create linked server in SQL SERVER and call it like this:
“Id”和“Name”是输入参数，“Result”是输出参数，并在 SQL SERVER 中创建链接服务器并像这样调用它：
select * from openquery
(
Test,'call mydb.MyPro(''Name'',''16'', @P0);'
)
It works for me :D
它对我有用：D
回答by Nandini
Try this,
尝试这个，
SELECT * FROM OPENQUERY(linked_server_name, 'SELECT postgres_procedure_name (parameters)');
SELECT * FROM OPENQUERY(linked_server_name, 'SELECT postgres_procedure_name (parameters)');
回答by Steve Kass
You could also see if it works to precede exec with SET FMTONLY ON:
您还可以查看在 exec 之前使用 SET FMTONLY ON 是否有效：
OPENQUERY([LINKSERVER],'SET FMTONLY ON; exec database.user.my_stored_proc ''AT'',''XXXX%'',''1111'',1')
OPENQUERY([LINKSERVER],'SET FMTONLY ON; exec database.user.my_stored_proc ''AT'',''XXXX%'',''1111'',1')
If you try this and it works, you should probably Google FMTONLY+OPENQUERY to get an idea of what it means.
如果你尝试这个并且它有效，你可能应该谷歌 FMTONLY+OPENQUERY 来了解它的含义。
回答by Asher
I experienced a very similar issue, but my SP wasn't taking any parameters. I tried experimenting with altering the query sent through the openquery to include 'SET NOCOUNT ON' and 'SET FMTONLY OFF' but this had no difference.
我遇到了一个非常相似的问题，但我的 SP 没有接受任何参数。我尝试尝试更改通过 openquery 发送的查询以包含“SET NOCOUNT ON”和“SET FMTONLY OFF”，但这没有区别。
The only solution that worked for my stored procedure was dropping the existing version, and altering the code to specifically 'SET NOCOUNT ON'
适用于我的存储过程的唯一解决方案是删除现有版本，并将代码更改为专门的“SET NOCOUNT ON”
After doing this I was able to successfully run my stored proc through my linked server connection.
这样做后，我能够通过我的链接服务器连接成功运行我的存储过程。
回答by Ali Hussain
First of all you have to add hard code text fields then you have to replace it by your parameters value like FromDate,TillDate,EmpID,CompCode,0,DeptID,DesgId,LocationID,AtnType
首先，您必须添加硬编码文本字段，然后您必须将其替换为您的参数值，例如 FromDate、TillDate、EmpID、CompCode、0、DeptID、DesgId、LocationID、AtnType
DECLARE @startdate varchar(255) = '2019-12-17'
DECLARE @enddate varchar(255) = '2019-12-17'
Set @SQL = 'SELECT * FROM OPENQUERY(' + quotename(@LinkedServer) + ',' + '''' +
'SET FMTONLY OFF; exec [TAP].[dbo].[GetAttendanceList] ' + 'FromDate,TillDate,EmpID,CompCode,0,DeptID,DesgId,LocationID,AtnType,1'')'
You have to replace your parameters values shown below
您必须替换如下所示的参数值
set @SQL=REPLACE(@SQL,'FromDate',+''''+''''+@startdate+''''+'''')
set @SQL=REPLACE(@SQL,'TillDate',+''''+''''+@enddate+''''+'''')
set @SQL=REPLACE(@SQL,'CompCode',+''''+''''+@CompCode+''''+'''')
set @SQL=REPLACE(@SQL,'AtnType',+''''+''''+''''+'''')
if @EmpID is Null
begin
set @SQL=REPLACE(@SQL,'EmpID','null')
end
if @DeptID is Null
begin
set @SQL=REPLACE(@SQL,'DeptID','null')
end
if @DesgId is Null
begin
set @SQL=REPLACE(@SQL,'DesgId','null')
end
if @LocationID is Null
begin
set @SQL=REPLACE(@SQL,'LocationID','null')
end
print @SQL
exec ( @SQL)
