"Jesper Stocholm" <j@stocholm.invalid> wrote in message
news:Xns97105AA34EC40stocholmdk@207.46.248.16...
> We are experiencing some problems with deadlocks in our system. We call
> Sql server 2k from a .Net applicationlayer.
>
> The flow is
>
> Create transaction object in .Net
> FOR each table in a list (e.g. 11 tables in total)
> DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
> (done in stored procedure with no cursors)
> // END FOR
>
> FOR each table in a list (same list af before, but in opposite sequence)
> BULK INSERT MyTable ..
> (SQL-statement created in .Net and sent to Sql svr)
> // END FOR
>
> transactionsobject.Commit();
>
> We receive data from an external system once every week. The external
> system cannot give os delta-information, e.g. only new data, så they send
> us their entire data. This is the reason for why we delete all data in
> our system and insert them again.
>
> The problem occurs at BULK INSERT, but odly not always. The database in
> question is offline for the rest of the system, so nothing takes place on
> the server apart from this job. The transaction object is sent to the
> server using Microsoft Practices Enterprise Library's data-block to
> enable us to roll-back the transaction if one of the statements fail.
>
> We are kind of blank with regards to how to solve this - do you guys have
> an idea to a solution?
>
> Thanks,
>
> :o)
>
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://www.sony.com">evil</a>
> Findes din kiosk på nettet? Se http://ekiosk.dk

> I have two suggestions. (1) change the mechanism so that the deletes
> are committed prior to starting the bulk inserts, or (2) generate a
> temporary stored procedure or dynamic SQL to execute the entire job in
> a single batch, complete with error handling, etc.

> My preference is to migrate transaction processing to the data tier
> whenever possible. It's a lot easier to modify a stored procedure
> than to recompile and redeploy a middle-tier component or God forbid,
> a client application.