Resetting a job causes it to execute the stored procedure

RayLade · Post by **RayLade** » Tue Oct 24, 2006 11:17 pm

We have a job that has a source odbc stage that calls a SQL Server stored proc, which inserts a record into a table and returns the same record to be processed. It works! But, when one resets the job via director, it also executes the stored proc.

Any idea why the stored proc gets executed when the job is re-set?

chulett · Post by **chulett** » Wed Oct 25, 2006 6:10 am

No idea... but I'd report it as a bug to IBM/Ascential and see what they have to say about it. What version of DataStage?

kumar_s · Post by **kumar_s** » Wed Oct 25, 2006 6:15 am

Are you resetting the job after Abort. Does it shows any log that StoredProcedure been exeduted after the entry of Job reset?

ray.wurlod · Post by **ray.wurlod** » Wed Oct 25, 2006 7:37 am

The description of "reset" is "do everything except process rows".

chulett · Post by **chulett** » Wed Oct 25, 2006 8:04 am

Sure you're not thinking of 'Validate'?

ray.wurlod · Post by **ray.wurlod** » Wed Oct 25, 2006 9:03 am

Yes, Validate.

Reset would have to check the SP, however, to determine whether any transaction needs to be rolled back, cursor(s) to be dropped, and so on.

RayLade · Post by **RayLade** » Wed Oct 25, 2006 5:16 pm

It all seems a bit buggy to me.
When the task is created in a server job, and you click on reset from the director, it will execute (actualy, any user defined query seems to execute. For example, we tried a test "wait 15 minutes", and the reset did just that).

Not to be perturbed, we re-wrote the task in a parallel job. Reset was fine, but when it ran, the stored proc ran twice, about 2 seconds apart.

The stored proc itself runs fine, its just Ds ODBC executes it twice.

I understand that DS 7.5.1 does not support SQL Server Stored Proc stage, and that an ODBC stage should be used.

Well, we ended up using a Dynamic RDBMS stage, and this worked. Now we are going to document "If you want to use a SQL Server Stored Proc, use a Dynamic RDBMS stage, or ELSE"...

Thank you gentlemen, for your prompt response. I am happy to get it working and to leave the "bugs" as other traps for young players.