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.
Leave a Reply