MS SQL Server Stored Proc & DataStage

[aaronej]

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

[datastage]

You might want to see if you have more luck with an OLE/DB stage connecting to SQL Server.

As far as the second error, I think this message is typical of when nothing in a job is running but the status is still running. It should return to a compiled state with nothing to worry about (except the original problem)

(deleted text)
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.

(deleted text)

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

[vmcburney]

I would try to identify which part of the stored procedure is hanging the job. Create a copy of the procedure and point your DataStage job at it. Start by reducing the procedure back to a dummy output and run that, gradually add in the original procedure code and run each scenario until you reach the piece of code that hangs your job.