存储过程调用链接服务器时出现语法错误？

I've got an Informix machine I've configured as a linked server in SQL Server.

我有一台Informix机器,我在SQL Server中配置为链接服务器。

I can remotely send, and receive the results of, a query for, say

我可以远程发送,并收到查询结果,比方说

SELECT * FROM linkedServer.instanceName.database.myTable

but can't run the

但是无法运行

linkedServer.instanceName.database.selectAllFromMYTABLE

stored procedure.

The error message I'm getting returned is "[Informix][Informix ODBC Driver][Informix]A syntax error has occurred." which is not massively helpful, except that it tells me that my request was received in some form...

我收到的错误消息是“[Informix] [Informix ODBC Driver] [Informix]发生语法错误。”除了它告诉我我的请求是以某种形式收到之外,这并没有大的帮助...

Could someone tell me what the correct calling syntax would be to execute an Informix stored procedure via SQL Server? Presumably I'm screwing up the stored procedure call, because the stored procedure can be verified to be working fine on the Informix server.

有人能告诉我通过SQL Server执行Informix存储过程的正确调用语法是什么?据推测,我搞砸了存储过程调用,因为可以验证存储过程在Informix服务器上正常工作。

EDIT: Adding the full text of a stored procedure I am testing, in order to verify I'm not doing something stupid in Informix which is causing a knock-on problem with the SQL Server execution:

编辑:添加我正在测试的存储过程的全文,以验证我在Informix中没有做一些愚蠢的事情,这导致了SQL Server执行的连锁问题:

CREATE FUNCTION sp_testSP()
   RETURNING char(20) as item_no

   DEFINE item_no char(20);

   FOREACH
    SELECT table_name.item_code
     INTO item_no
     FROM table_name
     WHERE table_name.item_code LIKE 'test%'
     RETURN item_no WITH RESUME;
   END FOREACH;
END FUNCTION

As I've mentioned, this appears to work fine in RazorSQL, which I have connected to Informix, but maybe seeing this will jog someone's memory with some reason why SQL Server can't work with this return method...

正如我所提到的,这似乎在RazorSQL中运行良好,我已经连接到Informix,但是可能会看到这会慢慢某人的内存,因为SQL Server无法使用这种返回方法...

2 个解决方案

#1

Have you tried using OpenQuery?

你尝试过使用OpenQuery吗?

http://msdn.microsoft.com/en-us/library/ms188427.aspx

#2

In native Informix, you would write (approximately):

在本机Informix中,您可以编写(大约):

EXECUTE PROCEDURE database@linkedServer:selectAllFromMYTABLE();

I'm not sure quite where you'd fit an instance name into that - the 'linkedServer' corresponds to an instance name (to my way of thinking). The nearest approaches would be:

我不确定你的实例名称在哪里 - “linkedServer”对应于一个实例名称(以我的思维方式)。最近的方法是:

database@linkedServer:instancename.selectAllFromMyTABLE()
instancename@linkedServer:database.selectAllFromMyTABLE()

However, that is via the native Informix interfaces. If you go via SQL Server, then the syntax probably needs to be the native SQL Server syntax for invoking a procedure. In theory, I believe, the API used (ODBC or whatever) should be able to translate to the native Informix syntax.

但是,这是通过本机Informix接口。如果您通过SQL Server,则语法可能需要是用于调用过程的本机SQL Server语法。理论上,我相信,所使用的API(ODBC或其他)应该能够转换为本机Informix语法。

#1