Task suspending

[tshad]

I have a piece of code that is getting suspended and I don't know why.

This Insert is inside of a Transaction but when when it gets to this insert it freezes. It is only using one table.

All I am doing is inserting one row into the BatchRevision table but I need to set the BatchRevisionNumber to either 0 or one more then the largest BatchRevisionNumber.

Shouldn't be a problem but it is.

It seems to have SPIDs running with one suspended. The other one seems to be increasing cpuTime.

I have to kill it to stop it.

     INSERT    INTO [Requirement].[BatchRevision]
                ( BatchID ,
                  BatchName ,
                  BatchRevisionNumber ,
                  [FileName]
                )
                SELECT  @BatchID ,
                        @BatchName ,
                        BatchRevisionNumber = ( SELECT  CASE WHEN MAX(BatchRevisionNumber) IS NULL
                                                             THEN 0
                                                             ELSE MAX(BatchRevisionNumber) +
                                                              1
                                                        END
                                                FROM    [Requirement].[BatchRevision]
                                                WHERE   BatchID = @BatchID
                                              ) ,
                        @FileName

Why is there 2 SPIDs? I assume 1 for the insert and one for the SELECT and the insert is waiting for the SELECT. There aren't that many rows in the table.

Thanks,

Tom

 

[genomon]

Try inserting everything but the BatchRevisionNumber, then update the record using SET BatchRevisionNumber = ( SELECT CASE WHEN...

Beir bua agus beannacht!