Hi All,

I'm attempting to push data into an Oracle database by calling a parameterised stored procedure, (hopefully contained within a package) with data returned from a "Get" stored procedure in a SQL Server database.

I planned to do this using a data-driven query task where the source was (for example) "exec p_getData" and the query being "BEGIN p_putData (?,?); END;".

I tried this first against a destination database in SQL Server, and provided the syntax was changed to t-sol, it worked fine. However when the destination was the Oracle database it failed. Due I think to the fact that it couldn't verify the parameters for the called stored procedure. (The PL/SQL worked fine in SQLPlus).

Any Ideas? Should I be using a data-driven query task or perhaps writing a vb-script would be better...

Has anyone had any experience of doing this?

In article <6051A47D-B443-4234-A047-CC145813B1E5 (AT) microsoft (DOT) com>, Alex
Roberts <alroberts (AT) elsevier (DOT) com> writes
I tried this first against a destination database in SQL Server, and
provided the syntax was changed to t-sol, it worked fine. However when
the destination was the Oracle database it failed. Due I think to the
fact that it couldn't verify the parameters for the called stored
procedure. (The PL/SQL worked fine in SQLPlus).

Any Ideas? Should I be using a data-driven query task or perhaps writing
a vb-script would be better...

Has anyone had any experience of doing this?


Try to use the ODBC canonical syntax-

{Call myfunc}
{Call myfunc()}
{Call myfunc( 1, 2 )}


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org