I have a SP reading a .txt file from File System and using Bulk Insert. I just wanna make sure if file exists before executing bulk insert command. How do i do that?
+1
A:
try
xp_fileExist ' <file Name> '
The above returns a result set. If you want just a variable, use
declare @FileOK INT
exec xp_fileExist 'c:\autoexec.bat' ,@FileOK OUTPUT
Print @FileOK
Sparky
2010-01-14 19:37:41
Just a note that this procedure is undocumented and unsupported, meaning tomorrow Microsoft could break it or remove it entirely, so use at your own risk. There are also security concerns with opening up access to XPs for non-SA users. Ideally this would be a good reason to introduce CLR functionality, which keeps you out of undocumented/unsupported territory, also gives you a lot more power and flexibility.
Aaron Bertrand
2010-01-14 19:56:16