SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Create function udf_CheckFileStatus (@filename varchar(1000))
returns bit
as

BEGIN
DECLARE @FS int
DECLARE  @OLEResult int
DECLARE  @FileID int
--set @filename = 'D:\sqldumps\etst.bak'
--declare  @filename varchar(1000)
DECLARE @source NVARCHAR(255)
DECLARE @description NVARCHAR(255)
DECLARE @flag bit
set @source ='Exist'
set @description='Exist'
--set @filename = 'D:\sqldumps\msdb_20040916_23_00_40.BAK'
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0  
begin
EXEC sp_OAGetErrorInfo NULL, @source OUTPUT, @description OUTPUT 
goto displayerror
end
--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT,@filename , 1
IF @OLEResult <> 0  
begin
EXEC sp_OAGetErrorInfo NULL, @source OUTPUT, @description OUTPUT 
goto displayerror
end
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
DisplayError:
if @source is NULL and @description is NULL
begin
--print @filename + '  in Use'
set @flag = 1
end
else
begin
--print @filename + '  is ready'
set @flag = 0
end
return @flag
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO