asked on

MSSQL 2008: Execute SQL Task to execute an Informix SP, with parameter

Background: I am running SQL 2008 R2 and have a IBM Informix 10.5 database setup as a linked datbase using OLEDB.
I have a similar but slightly different issue. I want to call an sp on the informix database that is a select statement. I have to pass some variable to it as well and hoping I can retrieve the result and store in a sql table. Below is the Informix SP call. How do I translate it to T-SQL and call it?
I am hoping I can setup a job that just calls this sp and stores results in a table. Much Appreciated

{call sp_csq_agent_summary('2015-09-13 05:00:00','2015-09-20 04:59:59','0','CA_Canada_Caller,ConsumerAffairs,CSC_Crossmark,CSC_eSales,CSC_InboundOrders,CSC_Outbound,CSC_SalesWarehouse,CSC_Shelter,CSC_Speciality')}

ste5an

EXECUTE linkedServer.schemaName.yourProcedure parameters;

or

EXECUTE linkedServer..schemaName.yourProcedure parameters;

robegish

ASKER

Here is my code when i do that and the error I receive:
exec [UCCX].[informix].sp_csq_agent_summary('2015-09-13 05:00:00','2015-09-20 04:59:59','0','CA_Canada_Caller,ConsumerAffairs,CSC_Crossmark,CSC_eSales,CSC_InboundOrders,CSC_Outbound,CSC_SalesWarehouse,CSC_Shelter,CSC_Speciality')

Error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '2015-09-13 05:00:00'.

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

robegish

ASKER

Awesome you have gotten me past that first error but now I get this:
OLE DB provider "MSDASQL" for linked server "UCCX" returned message "[Informix][Informix ODBC Driver][Informix]A syntax error has occurred.".
Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'sp_csq_agent_summary' on remote server 'UCCX'.

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

robegish, do you still need help with this question?