In SQL Server 2000 DTS How do I call and oracle stored procedure? I've tried using the Execute SQL task with the Exec <my procedure name> ; and it errors. I've been searching for the answer on how to execute Oracle Stored procedures from DTS without any luck. The stored procedure creates the table and data that I want to pump into SQL Server. Any help would be greatly appreciated. Thanks!

What ConnectionType are you using? What error message are you getting?

Connection type is Oracle provider for ole db When I put the following syntax in an execute sql task, I recieve an invalid SQL statement error: exec domainName.<my procedure name>

Use this syntax: SQLStatement={call <procedure name>}

Hi, I tried this syntax, but I'm still with this problem. How did you resolve this issue ? Did you have success using this syntax ? Thanks

Hi Ewata, The syntax changes depending on the connection type you're using. What type of connection are you using? ~Matt

Hi Matt, I'm using "Oracle Provider for OLE DB" and I've tried the syntax: SQLStatement = { call<sp_name> }. My Oracle Stored Procedure runs efficiently at Oracle client, but I've taken error messages when calling from DTS. Thanks for reply.

I don't have access to an oracle server right now to confirm, butI believe thatsyntax should work. The SQLStatement property should be set to: { call my_sp() } Make sure SQLSource Type is set to "Direct Input". What is the exact error you are getting? ~Matt

That's right. I've tested that syntax with a simple code in the stored procedure and the DTS worked successifully. When I tried a more "complex" code like "insert into <table> select <field list> from ..." the following message error occurs: ORA-01861: literal does not match format string ORA-06512: at <procedure name>, line 9 ORA-06512: at line 1

Hi, Where do we set this SQLSourceType to 'Direct Input'? is it in ExecuteSQLTask? or Transform Data Task? I am getting error as "Provider cannot derive parameter information and SetParameterInfo has not been called" when I try like this. {call schema_name.proc_name(?,?,?,?,?)}