Hi Everyone

I just found that in some cases the OLEAutomation check fails, giving me the "green light" when the file is still being copied

I've gone through the CLR solution with a little twist, this peace of function also tell me if a file is nonexistent

Here's the c# part

using System;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.IO;

namespace IsFileInUse

{

public class IsFileInUse

{

[SqlFunction()]

public static SqlInt32 isFileInUse(string fullFileName)

{

SqlInt32 FILE_IS_FREE = 0;

SqlInt32 FILE_IS_IN_USE = 1;

SqlInt32 FILE_IS_MISSING = 2;

SqlInt32 intResult = FILE_IS_FREE;

try

{

using (File.Open(fullFileName, FileMode.Open, FileAccess.Read, FileShare.None)) { }

}

catch (Exception e)

{

if (e.GetType() == typeof(FileNotFoundException))

{

intResult = FILE_IS_MISSING;

}

else

{

intResult = FILE_IS_IN_USE;

}

}

return intResult;

}

}

}

The SQl bit to register the assembly

CREATE ASSEMBLY [IsFileInUse]

FROM 'C:\IsFileInUse.dll'

WITH PERMISSION_SET = EXTERNAL_ACCESS

The sql bit to create the function

CREATE FUNCTION fnc_IsFileInUse(@fullFileName NVARCHAR(MAX))

RETURNS INT

EXTERNAL NAME IsFileInUse.[IsFileInUse.IsFileInUse].isFileInUse

And the example on how to use this function

DECLARE @intResult INT

SELECT @intResult = dbo.fnc_IsFileInUse('c:\bigSoonToBeImportedFile.txt')

IF @intResult = 1

BEGIN

--report file in use

END

ELSE IF @intResult = 2

BEGIN

-- report file missing

END

ELSE

BEGIN

-- import the file

END

Obviously in order to register the assebly and to use it, CLR must be enabled and the right permissions must be set

EXEC sp_configure 'clr enabled', 1

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_changedbowner 'sa'

GO

ALTER DATABASE databaseNameHere SET TRUSTWORTHY ON

GO

I'd like to thank everyone who helped me