Locking table for BCP Out 1

[jabrony76]

Hi All -

I've got a job that bcp's a table out at night but the table is quite large (4.2 million rows) and the bcp takes around 15 minutes.

I want to make sure that there are no INSERT / UPDATE / DELETE statements executed on the table during the bcp out and it seems the -h"TABLOCK" switch only works on the bcp in function.

The job already checks for executing processes at the start and will not run the bcp until they are complete.

I'm concerned about processes that that may be started after the bcp kicks off.

Anyone have any ideas how I can lock the table during the duration of the bcp?

Thanks in advance!
Andy

 

[mrdenny]

Change the out from queryout and use the with (tablockx) query hint in the select statement.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[jabrony76]

Thanks Denny, have a star.
Andy

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief