|
|
Posted by Jonathan Roberts on 12/07/06 03:40
Tim Satterwhite wrote:
> 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
>
>
Can your FTP routine move the file(s) to another directory once it
completes an upload? This would seem easier than the above. Am I
missing something that prevents this possibility?
Navigation:
[Reply to this message]
|