call to TSQL Stored Proc in Before SQL of ODBC Stage

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
Posts: 3
Joined: Mon Apr 14, 2014 10:27 am

call to TSQL Stored Proc in Before SQL of ODBC Stage

Post by emaher »

Hello- Seems to be a few threads on here related to this topic tho I think my use-case is unique.
I am loading data from SQL server DB (source) into a Netezza DB (target).
Very little transformation (most of the time none) is being done.

Our vendor has supplied us with an 'API' in the form of a Stored Proc that will generate a global temp table [##tmp_tbl] with data indicating what rows in the 'source' table are either new/altered/deleted.
I can then use the results from the temp table to update the target on Netezza.

Job Design: ODBC Connector ---> Peek

I am to call the SP giving several arguments: begin_dt, end_dt, source_tbl_nm, temp_tbl_nm.

I am thinking that I can execute the SP in the 'before sql' portion of the ODBC stage and then utilize a 'select * from [##tmp_tbl]' in the SQL Select statement section of the same stage.

When I run my job and look at the log, I get a couple initial warnings:
* Invalid object name ##tmp_tbl
* The metadata could not be determined because every code path results in an error. see previous errors for some of these
note:there are no previous errors...

The log then tells me:
* Before SQL command executed successfully
* Connected to Microsoft SQL Server, version...blah blah blah

then it hangs.
While the job is 'running' I am unable to browse the ##temp table DB objects via SSMS; where normally I can... I get a timeout when attempting to refresh the temp table folder list.
As soon as I stop the process from running the lock is released and I am then able to browse these temp table objects (although the one I created with the SP, if there ever was one) is not there.

I have tried various configurations of this: using a CALL instead of EXEC which produces an error. Wrapping the EXEC with BEGIN/END, which makes no difference. Attempted to put the EXEC and SELECT all in the primary Select Statement field, which gives me "invalid cursor state" error.
I have also attempted to use the Stored Procedure Stage- which appears to successfully call the SP, but since the result of the SP is a temp table and not Data, I still need another step to capture the data- which I couldn't figure out how to accomplish with the SP Stage.

Does anyone have insight on how to call a SP in the Before SQL and then reference a resulting global temp table- or have any ideas on another approach?

Post Reply