Posted by Tim Satterwhite on 12/06/06 22:55

Hi All,

I think this is a thorny problem, and I'm hoping you can help. I've not
found this exact issue described anywhere yet.

I have a stored procedure that calls BULK INSERT on a set of text files.
These files are FTP'd from a legacy system (a mainframe running MVS).
Sometimes, the process steps on iteslf, whereby the bulk insert is attempted
on a file whose FTP is still in progress; it's not fully written to disk on
the SQL box (it's a 100MB file that takes a long time to cross the network
to the share on the Windows box), and so the insert generates a fatal error
and the procedure stops.

I wrote a proc that calls the sp_OA* procs to use the
Scripting.FileSystemObject to test for the file's readiness. It returns an
error for me if the file does not exist. It also returns an error if I try
to run the BULK INSERT against a file which is being copied via Windows to
the SQL box. This is working just fine under those conditions; the
sp_OAMethod to call OpenTextFile bombs appropriately if the file write is
still in progress.

That's great, but it doesn't do the same thing during an FTP in progress.
It doesn't generate the same error (that is OpenTextFile has no problem
opening a partially written FTP'd file). I can also open the file in
Notepad, even though it's not fully written to disk ... something I did not
expect, but there we are. What is it about FTP that's different from a
Windows file system copy operation that makes the file look available for
reading?

If BULK INSERT is capable of detecting that it cannot do its thing on a file
whose FTP is in progress, then what can I write or call to emulate that
detection? I tried writing a COM object in VB.NET and calling that from my
SQL stored proc instead of the Scripting Engine's FSO methods. My code
simlpy tries to run a FileOpen using an Exclusive read lock; however, this
doesn't seem to work, and I'm shooting in the dark now.

Can anyone tell me what kind of file i/o request BULK INSERT makes, such
that it is capable of abending during a run against an incompletely written
file using FTP?

Thanks!
Tim

[Back to original message]