I use the CLR to check if a file exists using the C # method. I created the assembly and function in the sql server and I pass the parameter.
C # code:
using System;
using System.IO;
public class FileExist
{
[Microsoft.SqlServer.Server.SqlFunction]
public static bool fn_FileExistCLR(string FilePath){
return System.IO.File.Exists(FilePath);
}
}
I created the assembly on sql server:
CREATE ASSEMBLY FileExistCLR2
FROM 'C:\MSSQLTips\FileExist2.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
I also created a function:
CREATE FUNCTION [dbo].[fc_ChkFlCLR2](@filename [nvarchar](4000))
RETURNS nvarchar(4000) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [FileExistCLR2].[FileExist].[fn_FileExistCLR]
GO
The parameter is passed by the function to the CLR.
declare @filevar as nvarchar(4000)
set @filevar = ltrim(rtrim('T:\\Project\\XML_ErrorFile20131106.txt'))
SELECT [PlayNet].[dbo].[fc_ChkFlCLRChk] (@filevar)
GO
The local file is located, but not for the file in the network folder:
Result 1 - if the file is in a network folder, the function returns false Result 2 - if the file is in a local folder, the function returns TRUE.
I run them on a local sql server and try to add @, but to no avail.
Cross check. I created a C # method that displays the result on the command line. Curiously, the same file on the network is detected by file.exist. Please, help
using System;
using System.IO;
class FileExist
{
static void Main()
{
string FilePath = "T:\\Project\\XML_ErrorFile20131106.txt";
string retFilePath;
if (File.Exists(FilePath))
{
retFilePath = "True";
Console.WriteLine(retFilePath);
Console.ReadLine();
}
else
retFilePath = "false";
Console.WriteLine(retFilePath);
Console.ReadLine();
}
}