Calling SQLServer StoredProc

[htrisakti3]

Question:
when I call using EXEC StoredProc1 in After-SQL of SQLServer stage, does DataStage WAIT for completion of it ??

In my case, StoredProc1 calls 6 other storedprocs (storedproc1a, 1b, 1c...)
I saw that the first 2 storedproc's ran & not the rest.. I suspect DataStage only send Execute on main StoredProc1 and then exit .. ??
(which then stopped the unfinished storedproc from running)

[sud]

.. I suspect DataStage only send Execute on main StoredProc1 and then exit .. ??

That's right but

(which then stopped the unfinished storedproc from running)

this is not correct. Datastage will issue an execute and terminate and all database errors have to be treated inside the procedure itself. Datastage will handle errors related to executing the proc but not it's runtime errors. Hence, there must have been an error in the stored proc which didn't allow the remaining procs to be called. Test the stored proc and see if it executes properly.

[htrisakti3]

Yes, I did test it from within SQLServer console & the main calling routine calls and completed all stored procs that needed to be called..

One more thing i found, in the documentation about RDBMS plug-in, in before/after SQL, it mentions that calling stored proc is NOT supported.

I guess this means, it may or may NOT work, no guarantee from Datastage?

[sud]

Yes, I did test it from within SQLServer console & the main calling routine calls and completed all stored procs that needed to be called..

One more thing i found, in the documentation about RDBMS plug-in, in before/after SQL, it mentions that calling stored proc is NOT supported.

I guess this means, it may or may NOT work, no guarantee from Datastage?

Okay !!

[newtier]

There is also a stored procedure stage that you can put inline in your job flow.

[chulett]

... depending on your exact 7.x version of DataStage. Initially it was only available for Oracle, with others like SQL Server added in a more recent release.

[DSguru2B]

If your stored procedure doesnt have OUT or RETURN parameters then look into the odbc stage. It works pretty good for calling stored procedures.