Lock table while DTS package runs

[rac2]

I have a table of work to do. Rows are inserted by several different processes (web forms submitted). Once an hour a DTS package reads the table, exports to a text file, then TRUNCATEs the table. The package does not take much time to run but inevitably there will be an attempt to add a row to the table during the moments that data is being exported from it, maybe the row wont be read by the SELECT query, but will be deleted by the TRUNCATE step.

Does SQL Server protect against this? Is there something I can do to hold off adding rows during the export and truncate actions?

 

[RiverGuy]

This doesn't answer your question exactly, but off the top of my head, how about this:

* Add an identity column to your table
* Select maximum identity value
* Export to text file where identity value <= your maximum identity value
* Delete from table where identity value <= your maximum identity value.

 

[rac2]

Because the todo table is somewhat like a temporary table. Once the work is done, I dont want to keep it. TRUNCATE recovers disk space and is not loggged. So it is a bit more efficient than DELETE.