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: stackoverflow.com/questions/591191/…. — DWright
– DWright, Commented May 6, 2015 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
– John, Commented May 6, 2015 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
– Ben, Commented May 6, 2015 at 23:50

DWright · Accepted Answer · 2015-05-06 23:53:23Z

1

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

answered May 6, 2015 at 23:53

DWright

9,5594 gold badges39 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kick Buttowski Over a year ago

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post.

DWright Over a year ago

@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.

Collectives™ on Stack Overflow

Cannot connect to an external database in SQLCLR

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related