468,720 Members | 1,675 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,720 developers. It's quick & easy.

BULK INSERT - does it lock the file while reading?

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

Jul 23 '05 #1
3 2943

"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.
Jul 23 '05 #2
Not a bad idea, though I may just use tail for simplicity, assuming it
doesn't lock the file as well. Thanks

Jul 23 '05 #3
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.

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Jim Geissman | last post: by
5 posts views Thread by me | last post: by
6 posts views Thread by pk | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by
11 posts views Thread by Ted | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Oskars | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.