Unable to read text file used in CLR function when deployed to SQL Server 2008

Question

I created a CLR user-defined function in C# for DID you mean implementation on my website and deployed the DLL in SQL Server 2008.

But the dictionary after reading the text file is returned blank.

Below is some part of the code but the problem is in reading text file:

public Spelling()
{
        try
        {
            string fileContent = File.ReadAllText("C:/Bgtext/big.txt");
            List<string> wordList = fileContent.Split('\n').ToList();

            foreach (var word in wordList)
            {
                string trimmedWord = word.Trim().ToLower();
                if (_wordRegex.IsMatch(trimmedWord))
                {
                    if (_dictionary.ContainsKey(trimmedWord))
                        _dictionary[trimmedWord]++;
                    else
                        _dictionary.Add(trimmedWord, 1);
                }
            }
        }
        catch (Exception ex)
        {
            ex.Message.ToString();
        }

    }

    [SqlFunction(Name = "CorrectWords", DataAccess = DataAccessKind.Read)]
    public static string correctwords(string words)
    {
        string[] arr = words.Split(' ');

        for (int i = 0; i <= arr.Length - 1; i++)
        {
            arr[i] = Correct(arr[i]);
        }

        StringBuilder correctedwords = new StringBuilder();

        foreach (string value in arr)
        {
            correctedwords.Append(value);
            correctedwords.Append(' ');
        }

        return correctedwords.ToString();
    }

Is there any particular location where I need to put my big.txt file after deploying the DLL to SQL Server or is it a permission issue or is there some other way to read the text file?

Perhaps you should do something with that exception instead of `ex.Message.ToString();` which achieves what, exactly? — ta.speot.is, Aug 23 '13 at 02:54

Gary Walker · Answer 1 · 2013-08-23T16:26:15.653

1

Have you granted permision for the database to do file I/O

Here is an example from one of my cls projects (obviously my dev machine)

create assembly MB from 'C:\Projects_DotNet\ABC\adlib\adlib\bin\Debug\adlib.dll'
  with permission_set = external_access

You can also run the equivalent alter assembly command

To Check Security, Add a hello world stored proc similar to

  [SqlProcedure]
  public static void hello(SqlString yousaid)
  {
    if (yousaid.IsNull)
    {
      yousaid = "Not too chatty are you " + Environment.UserDomainName + "\\" + Environment.UserName;
      SqlContext.Pipe.Send("Mercedes Benz says, '" + yousaid.ToString() + "'\n");
    }
    else
    {
      SqlContext.Pipe.Send("The CLR proc says, you said '" + yousaid.ToString() + "'\n");
    }
  }

Call hello with "something to echo" -- make sure it works, then call hello with a null ans see the security context that the CLR is running with. You can then verify NT permissions for the file, etc.

You can also use the SqlContext.Pipe.Send method as a simple way of showing debug info.

edited Aug 23 '13 at 16:26

answered Aug 23 '13 at 02:52

Gary Walker

8,097
2
14
39

Also does the SQL Server process have access to `C:/Bgtext/big.txt`? – ta.speot.is Aug 23 '13 at 02:54
i tried with all three permission_Set = SAFE, UNSAFE, EXTERNAL_ACCESS but it does not work. – user1087769 Aug 23 '13 at 02:55
how to set the access to sql server to the text file, i tried sharing the folder with everyone and will all access permission – user1087769 Aug 23 '13 at 02:57
Stop eating the exection and maybe you will get useful info from the exception. If that does not solve it, you will probably have to debeg it either using the debugger (usually easiest) or logging detail to allow you to debug (if not permission to run the debugger) -- lots of possibilities exist, maybe your regex is always failing, who knows. Can't tell for sure; you have not posted full code (you never even refer to dictionary in your sql method) and of course, we can't reproduce your environments and something is clearly environmental if it works on one machine but not another. – Gary Walker Aug 23 '13 at 16:14
I added debugging suggestion to my answer too long for a comment – Gary Walker Aug 23 '13 at 16:27
When i run the procedure or function just to display text or calculate totals, it works fine after registering it to sql. The problem is rreading the text file because the code below returns 0 in dictionary // known() if (_dictionary.ContainsKey(word)) return word; I am not sure what permission to give to file as i shared the whole folder with everyone and also if any thing needs to be done so that disctionary has all text from text file – user1087769 Aug 23 '13 at 20:27
This is why I said to stop eating the exception. You can see if you are in fact getting a file read error and what the read error is and why I showed you how to verify the security context. Please do these things. – Gary Walker Aug 23 '13 at 21:04
i added exception of filenotexist as well as IO exception in catch block and redeployed the code in sql. run the function and i dont see anythign coming in the Manager->SQL SERVER LOGS in sql management studio. Is ther any other location i need to check. More and more i am nt able to solve this issue, it feels like it is something more than permissions problem. but then why my code when tried through window form application works just perfect. :( – user1087769 Aug 24 '13 at 02:49
I even tried embedding the file into the dll and reading it using stramreader, it again works fine in my application. do u still think it is a permission error or is it something els because it looks something else now. – user1087769 Aug 24 '13 at 04:26
If you stop trapping the exception, you will know if it is a file I/O problem if you call in from SQL Managment Studio -- if no exception, the problem is not a permissions problem. This is the 3rd time I have told you to do this first. – Gary Walker Aug 26 '13 at 13:29

Unable to read text file used in CLR function when deployed to SQL Server 2008

1 Answers1