CREATE FUNCTION [dbo].[fn_FileExists] (@FileName nvarchar(255)) RETURNS bit AS BEGIN DECLARE @FS int DECLARE @FileID int DECLARE @OLEResult int DECLARE@Exists bit EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT IF @OLEResult <> 0 BEGIN RETURN 0 END EXECUTE @OLEResult = sp_OAMethod @FS, 'FileExists', @Exists OUT, @FileName IF @OLEResult <> 0 BEGIN return 0 END EXECUTE @OLEResult = sp_OADestroy @FS return @Exists END
Important things to remember.
- Existence of the file will be checked on the server in which it is created. If you are running this stored procedure on remote machine, it will check for file on remote machine and not on local machine.
- To call a function, you should write
if dbo.fn_FileExists ('Filename.txt') = 1 print 'exist'
Here “dbo.” must be appended to call the function, otherwise it will give error.
Vishal Monpara is a full stack Solution Developer/Architect with 13 years of experience primarily using Microsoft stack. He is currently working in Retail industry and moving 1’s and 0’s from geographically dispersed hard disks to geographically dispersed user leveraging geographically dispersed team members.