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)
Calling SQLServer StoredProc
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 36
- Joined: Thu Jun 10, 2004 11:22 pm
Re: Calling SQLServer StoredProc
That's right buthtrisakti3 wrote:.. I suspect DataStage only send Execute on main StoredProc1 and then exit .. ??
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 wrote:(which then stopped the unfinished storedproc from running)
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
-
- Charter Member
- Posts: 36
- Joined: Thu Jun 10, 2004 11:22 pm
tested from within sqlserver
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?
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?
Re: tested from within sqlserver
Okay !!htrisakti3 wrote: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?
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
Re: tested from within sqlserver
There is also a stored procedure stage that you can put inline in your job flow.
Rick H
Senior Consultant
Senior Consultant