BULK INSERT - does it lock the file while reading?

[M Bourgon]

Howdy, all. We have a (log-type) file that's constantly being written
to. We need to grab the latest rows out of this file, insert them into
a table, and then process them. We've found out the hard way that BCP
locks the file while it's inserting, so rows can't be written to the
file while the BCP is running.

Our current workaround is to make a copy of the file (using ftp), but
we're running into other problems.

I'm trying to find a better way to do this. We've narrowed down a
couple.
1) use TAIL or something similar to grab the records since we last ran.
2) use BULK INSERT, provided it doesn't lock the file.
3) get suggestions off of USENET. :)
Any suggestions or comments?
Thanks,
Michael

[David Rawheiser]

"M Bourgon" <bo*****@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Howdy, all. We have a (log-type) file that's constantly being written
to. We need to grab the latest rows out of this file, insert them into
a table, and then process them. We've found out the hard way that BCP
locks the file while it's inserting, so rows can't be written to the
file while the BCP is running.

Our current workaround is to make a copy of the file (using ftp), but
we're running into other problems.

I'm trying to find a better way to do this. We've narrowed down a
couple.
1) use TAIL or something similar to grab the records since we last ran.
2) use BULK INSERT, provided it doesn't lock the file.
3) get suggestions off of USENET. :)
Any suggestions or comments?
Thanks,
Michael

Would a half baked suggestion do?

Write an ActiveX Script to open the file shared read only ,
find your starting point and loop to the end doing either
1. a write out of the lines as is to a scratch file that you can then
BulkCopy in
2. a parse of the log file lines and then update the DB directly.

[M Bourgon]

Not a bad idea, though I may just use tail for simplicity, assuming it
doesn't lock the file as well. Thanks

[M Bourgon]

Answering my own question - yes, bulk insert DOES lock the file -
couldn't append to the file, got "The process cannot access the file
because it is being used by another process.". Looks like I'll
tail/etc the file. Thanks, guys.