Routine to Call SQL Server Stored Proc?

[zbethem]

Anyone done this? I saw a previous post from awhile ago of a user trying to call a stored procedure through a routine. He was doing this in order to support input/output arguments. I have a similar problem:

I have a job that is similar in flow to:

Code: Select all

OCI --> StoredProc --> OCI

The important things to note:
1. The stored procedure has input args and returns output args
2. The stored procedure takes values from Oracle OCI, but lives in SQL Server
3. SQL Server stored procedures aren't supported in the standard DataStage StoredProc stage, so ODBC is the way

I'm thinking I could call the custom routine in a transformer thus making it flow like:

Code: Select all

OCI --> Transformer --> OCI

Thoughts?

[ray.wurlod]

Why?

[zbethem]

Hehehe... good question.

We're trying to re-use code that has already been built on our transactional database (SQL Server). The procedure inserts records into several tables with logic that would have to be rebuilt otherwise. After it's all said and done, we then have to synchronize a table (Oracle) with the generated IDs.

So, to sum it up:
1. code reuse
2. transactional integrity between databases

[ramabbm]

Yes, you can write a routine, which internally executes the SQL server stored procedure through ODBC and get the results. It is very much possible.

[kcbland]

You're going to have licensing issues because Ascential supplied drivers are licensed only for internal use by DataStage. Unless you supply your own drivers, your ODBC method won't work.

What's the issue with command line calls to sqlplus and osql/isql to run scripts that do this work? You could easily use a Sequencer or a Batch job or a simple script to do everything you're talking about with more extensive error handling capability and auditability.