T-SQL UDF to test for 'File In Use' ect.

Create Function fn_FileAvailable 
(@FileName VarChar(255)) 
Returns Int 
As 
Begin 
/* 
Return Values 
= 0 FileSystemObject can open the file, so deemed "available" 
= -1 means FileSystemObject could not be created 
= -2146828235 (0x800A0035) File Not Found 
 = -2146828218 (0x800A0046) Permission Denied (in use) 
...etc 
*/ 
DECLARE @fso int, @hr int, @file int, @Result Int 
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT  
IF @hr <> 0 BEGIN  
EXEC sp_OAGetErrorInfo @fso -- Error Processing
Return -1 
END  
EXEC @hr = sp_OAMethod @fso, 'OpenTextFile', @file OUT, @FileName, 1 
SELECT @Result = @hr 
IF @hr = 0 BEGIN 
EXEC @hr = sp_OAMethod @file, 'Close' 
EXEC @hr = sp_OADestroy @file 
END 
EXEC @hr = sp_OADestroy @fso 
Return @Result 
End