adminorama (9/12/2012)
Wandering around the web I found that OLE Automation could be a solution, so I want to share thisI created a function that returns "0" if the file is free and "1" if it's used by another process or deas not exists
CREATE FUNCTION fnc_IsFileInUse(@filename VARCHAR(1000))
RETURNS BIT
AS
BEGIN
DECLARE @FS INT
DECLARE @OLEResult INT
DECLARE @FileID INT
DECLARE @source NVARCHAR(255)
DECLARE @description NVARCHAR(255)
DECLARE @flag BIT
SET @source = 'Exist'
SET @description = 'Exist'
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo NULL, @source OUTPUT, @description OUTPUT
GOTO displayerror
END
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
SET @flag = 1
END
ELSE
BEGIN
SET @flag = 0
END
RETURN @flag
END
I've tested this function on SQL Server 2008 and SQL Server 2005, it needs OLE Automation to be enabled and the user permission on the included stored procedures, so this might not be a solution suitable for everyone
To by pass OLE Automation which you would typically want to do anyway you can put code along these
lines in a SQL CLR Function to test for the readiness of a file to be read:
try
{
using (File.Open(filepath, FileMode.Open, FileAccess.Read, FileShare.None)) { }
}
catch (Exception)
{
return false;
}
return true;