Hi,
I have a problem with a SP running (or not) on SQL Server 7.
The purpose of the routine is to translate data from an old system, to a new one. The database has canged considerably, so much processing of the old data is required, hence I cannot use a simple DTS package.
The SP is basically a FETCH in a loop around a DECLARED cursor, with each record being processed. Processing includes declaring other cursors, being looped through these in the same way. I also create and drop 1 temporary table each time around the main loop.
The problem - I run the SP as a scheduled job, and most times (not always) the job fails because a deadlock occurred and my job was chosen as the deadlock victim.
Now, I can understand the deadlock mechanism. but where the source and destination databases are concerned, I AM THE ONLY USER. Can a SP deadlock with itself???