CODEWITHSUNDEEP
c#sql-server
user3286012
user3286012

Reputation: 141

The Eternal File.Exist CLR C# not working when called from SQL Server but working in C#

I'm using a CLR to verify if a file exists using C# method. I've created an assembly and function in sql server and I'm Passing a 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've created the assembly in sql server:

CREATE ASSEMBLY FileExistCLR2
FROM 'C:\MSSQLTips\FileExist2.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

I've Created the function also:

CREATE FUNCTION [dbo].[fc_ChkFlCLR2](@filename [nvarchar](4000))
RETURNS nvarchar(4000) WITH EXECUTE AS CALLER
AS 
     EXTERNAL NAME [FileExistCLR2].[FileExist].[fn_FileExistCLR] 
GO

A parameter is being passed by the function to the CLR.

declare @filevar as nvarchar(4000)
set @filevar = ltrim(rtrim('T:\\Project\\XML_ErrorFile20131106.txt'))
--set @filevar = ltrim(rtrim('C:\QA_Ticket.xls'))
SELECT [PlayNet].[dbo].[fc_ChkFlCLRChk] (@filevar)   
GO

Local file is being found but not for the file in the network folder:

Result 1 - if file is in a network folder, function returns false Result 2 - if file is in a local folder, function returns TRUE.

I'm running these in a local sql server and tried adding @ also but to no avail.

To cross check. I've created a C# method that outputs the result in the command line. Curiously, the same file in the network is being detected by file.exist. Please Help

using System;
using System.IO;

class FileExist
{
   // [Microsoft.SqlServer.Server.SqlFunction]
    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();
        }
}

Upvotes: 3

Views: 1206

Answers (1)

competent_tech
competent_tech

Reputation: 44941

The issue is most likely that the user that the SQL Server process is running as does not have a T drive. It would be much better (and safer) to use UNC drive notation for this type of work (i.e. \\thefileshare\project\xml...txt).

Upvotes: 1

Related Questions