MS SQL Server Stored Proc & DataStage
Posted: Wed Nov 19, 2003 8:16 am
All,
I am using an ODBC stage to call a Stored Procudure in SQL server 2000. The proc does a bunch of updates to a temp table and then returns all the results back to the job. The problem:
The proc executes fine when executed in Query Analyzer (runs in about 50 seconds), however, when the proc is executed in the DataStage job, it hangs, never returns any records, and leaves a process running on the database server which has to be manually killed.
I have tried many, many different combinations of things to resolve this issue including:
Changing the transaction level
Executing the proc from a SQL query
Adding indexes to the temp table
Also: After this process has run its course and the database process has been killed, I get the following error when I try to run 'Cleanup Resources' for the job:
ERROR: Cannot find any process numbers for stages in job trgFactAcademicTermGPAAgg
Any one have anyother ideas or know what the heck is going on?
Thanks!!
Aaron
I am using an ODBC stage to call a Stored Procudure in SQL server 2000. The proc does a bunch of updates to a temp table and then returns all the results back to the job. The problem:
The proc executes fine when executed in Query Analyzer (runs in about 50 seconds), however, when the proc is executed in the DataStage job, it hangs, never returns any records, and leaves a process running on the database server which has to be manually killed.
I have tried many, many different combinations of things to resolve this issue including:
Changing the transaction level
Executing the proc from a SQL query
Adding indexes to the temp table
Also: After this process has run its course and the database process has been killed, I get the following error when I try to run 'Cleanup Resources' for the job:
ERROR: Cannot find any process numbers for stages in job trgFactAcademicTermGPAAgg
Any one have anyother ideas or know what the heck is going on?
Thanks!!
Aaron