Technical Article

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

,

Useful for testing if file is available for manipulation prior to trying to "process" it.
From forum thread http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=19344
Thought I may as well add it to the Scripts Library.

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

Read 824 times
(9 in last 30 days)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating