Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dariopalermo
Starting Member
16 Posts |
 Posted - 2011-06-17 : 06:42:48
|
| Hi guys,Here's my problem: sometimes when I try to import a file using openrowset(bulk...), placed in a "try" block to handle possible errors, it fails with:Cannot bulk load because the file "<filename>" could not be openedThe file should exist because I do: EXEC Master.dbo.xp_fileexist @timbfile, @timbfileexist OUT IF @timbfileexist = 1 BEGINbefore using openrowset.So the error should happen when the file exists, but cannot be opened, and this could happen when the file it's still being uploaded via ftp.Is there a way to avoid this? Some way to check if the file exist and can be opened before trying to actually open it? Or should I simply manipulate the "catch" block for this specific error, issuing a specific procedure for handling it?Thanks,Dario |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-17 : 07:17:01
|
| You can use a dir command or file scripting to check the folder.http://www.nigelrivett.net/SQLTsql/CheckIfFileExists.htmlBe careful - often ftp releases the lock before the transfer is complete and you can load an incomplete file without error.It's better to have a control file transferred after the ftp or set the ftp process to rename the file or move to another folder on completion.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
dariopalermo
Starting Member
16 Posts |
Posted - 2011-06-17 : 07:32:36
|
| The rename after completion idea seems the most effective to me... SQL will not try to import the file at all during the ftp transfer, then.thanks!Dario |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-17 : 08:13:47
|
| It's nice to get a control file indicating the number of records in the file so you can check - often it's difficult for the source system to provide it though.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Check if existing file can be opened or is locked