使用 OPENQUERY 执行存储过程 [英] Execute stored proc with OPENQUERY

问题描述

我有一个带有链接的 Sybase 服务器的 SQL Server 2008，我正在尝试使用 OPENQUERY 在 Sybase 服务器上执行一个存储过程.如果我有一个不带参数的存储过程，它会成功.如果我有一个带有参数的存储过程，它就会失败.我什至尝试了一个非常基本的存储过程，它只接受了一个仍然失败的 int .以下是我使用的语法:

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:

select * from 
OPENQUERY([LINKSERVER],'exec database.user.my_stored_proc ''AT'',''XXXX%'',''1111'',1')

消息 7357，级别 16，状态 2，第 3 行无法处理对象exec database.user.my_stored_proc 'AT','XXXX%','1111',1".链接服务器LINKSERVER"的 OLE DB 提供程序ASEOLEDB"指示该对象没有列或当前用户对该对象没有权限.

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.

由于 proc 可以在没有参数的情况下正常执行，我认为这不是权限问题.

As the proc will execute just fine without parameters, I don't think it is a permission issue.

推荐答案

Linked Servers 和 OPENQUERY，Gems 到 MS SQL Server...就是披着羊皮的狼.我发现以下解决方案在处理参数时有效

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

1. 如果 SP 基本上只是 SELECT 语句，则将其移至 VIEW 并仅通过 OPENQUERY 传递 SQL 语句.

1. If the SP is basically just SELECT statements, the move the same to a VIEW and just pass SQL statements via OPENQUERY.

将 OPENQUERY 构建为字符串，然后使用 execute_sql.

Build the OPENQUERY as a string and then use execute_sql.

这篇关于使用 OPENQUERY 执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！