Blocking Problems

[ceesql]

I have a procedure that is scheduled to run hourly and accesses production tables used by our order entry application. This procedure populates a table that is used by a crystal report (report runs hourly as well).

I've run into blocking problems.

I've tried to make the procedure as agressive as possible (dump data from production tables into temp tables, create cursor after temp tables are created, I've issued no lock statements after all tables). I'm not sure to where to go from here. Any help is appreciated.
Thanks

 

[Rishi146]

Are you running everything under one procedure If so Why is there any special need lke variables etc DOES it need to be under a single procedure(if you are using one)

Personally i always split long,aggresive procedures up

For now though you are right you will have to use optomistic locking or break everything down

Rishi(MCSD,MCDBA,MCP)

 

[ceesql]

Thanks Rishi146 appreciate your suggestion. I am passing variables along the way - not that many though. Maybe there's another option...