Syntax error on stored procedure call to linked server?

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

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

  • Creating a SQL Server table from a C# datatable
  • Many-to-many relationship left and right keys flipped after Entity Framework 5 upgrade
  • SQL Query to get a total value for each week across years
  • TableAdapter exception with joins
  • Is there a way in SQL Server Management Studio to number/identify query results?
  • How to get the last month data and month to date data
  • SELECT * FROM linkedServer.instanceName.database.myTable

    but can’t run the


    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…

    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.

    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:

       RETURNING char(20) as item_no
       DEFINE item_no char(20);
        SELECT table_name.item_code
         INTO item_no
         FROM table_name
         WHERE table_name.item_code LIKE 'test%'
         RETURN item_no WITH RESUME;

    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…

    2 Solutions collect form web for “Syntax error on stored procedure call to linked server?”

    In native Informix, you would write (approximately):

    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:


    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.