Cannot connect to an external database in SQLCLR

Question

I am trying to create a function that will connect to an external database and run a query. When I run my function I am getting this error:

Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

I don't think that I am doing anything strange, but here is my code. Please let me know if you spot something odd. I really don't know what else to try.

 [Microsoft.SqlServer.Server.SqlFunction]
 public static SqlString createFile()
 { 
     string theQuery = "SELECT * FROM A_TABLE;";
     string theConnection = "Data Source=serverName;Initial Catalog=DatabaseBane;Persist Security Info=True;User ID=login;Password=thePassword";
     SqlConnection DBConnect = new SqlConnection(theConnection);
     try
     {
         //My code is breaking here************************************
         DBConnect.Open();
     }
     catch (Exception e)
     {
         return "Happening in the connect: " + e.Message;
     }
     SqlDataAdapter dataAdapter = new SqlDataAdapter(theQuery, DBConnect);
     DataTable HRNdata = new DataTable();
     dataAdapter.Fill(HRNdata);

     FileStream stream = new FileStream(@"C:\TestFiles\demo.xls", FileMode.OpenOrCreate);
     ExcelWriter writer = new ExcelWriter(stream);
     writer.BeginWrite();
     Dictionary<string, int> noteDict = new Dictionary<string, int>();

     foreach (DataRow r in HRNdata.Rows)
     {
         try
         {
             noteDict.Add(r["Note"].ToString(), 1);
         }
         catch
         {
             noteDict[r["Note"].ToString()] += 1;
         }

     }

     int counter = 1;
     foreach (KeyValuePair<string, int> pair in noteDict)
     {
         writer.WriteCell(1, counter, pair.Key);
         writer.WriteCell(2, counter, pair.Value);
         counter++;
     }

     writer.EndWrite();
     stream.Close();

     try
     {
         DBConnect.Close();
     }
     catch (Exception e)
     {
         return e.Message;
     }
     return "";
 }

Seems like you are going to have to annotate your method with something like `DataAccessKind.Read`. Also, this may be of help: http://stackoverflow.com/questions/591191/sqlfunction-fails-to-open-context-connection-despite-dataaccesskind-read-present. — DWright, May 06 '15 at 23:12
Wow. Thank you so much! I knew I needed an extra pair of eyes! It's always the formatting things that get me. changing this did the trick [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]] public static SqlString createFile() { — John, May 06 '15 at 23:32
@John if you have found the solution to your question, please add it as an answer! That way others who have similar issues will be able to see the solution as well. — Ben, May 06 '15 at 23:50

score 1 · Accepted Answer · answered May 06 '15 at 23:53

1

You will need to add an annotation to your method along the lines DataAccessKind.Read.

answered May 06 '15 at 23:53

DWright

9,258
4
36
53

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. – Kick Buttowski May 07 '15 at 02:23
1

@KickButtowski. Actually it did answer the question, as you can see from the history above. I originally said this in the comment above, and it addressed OP's issue, so I added it as an answer after OP stated that this had worked. – DWright May 07 '15 at 02:25

Cannot connect to an external database in SQLCLR

1 Answers1