Posted by Tim Satterwhite on 12/06/06 23:01

Sorry, in paragraph 2, I meant to say " It also returns an error if I try
to run OpenTextFile against a file which is being copied via Windows to
the SQL box."

Tim
"Tim Satterwhite" <timothy.satterwhite@nospamucsfmedctr.org> wrote in
message news:el7hon$1krq$1@itssrv1.ucsf.edu...
> 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]