I'm having a lot of difficulty in creating a user defined function in vs2005.
why I'm doing it in c# instead of vb or tsql is that I know C# better than the others (that and tsql won't let you make temp tables in functions
But as far as this goes, I can't seem to get this working.
I' trying to execute a simple select statement and get a datatable variable before I run it through the creative algorithm I have planned.
But I can't seem to get the data into a table.
I get this exception when I execute the function on the server:
System.InvalidOperationException: 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.
This is the code I use to get the data:
using(SqlConnection sqlcnct = new SqlConnection("Context Connection=true"))
{ SqlCommand sqlcmd = new SqlCommand(sqlstrng, sqlcnct);
sqlcnct.Open();
SqlDataReader sqldr = sqlcmd.ExecuteReader();
while (sqldr.Read())
{
//inserts into datatable here (I don't yet have code here because i've been trying to figure out how to get it to read first. )
}
}
But unfortunately SQL server 2005 doesn't like that.
Can anyone point me in the right direction or shoot down my hopes right here?
I'm trying to use a function to avoid having to run a sproc on every employee on the employee table and storing it in an actual field /table every time I need to run this function.Two things:
1. In order to access database data from a CLR UDF you need to set the DataAccess and SystemDataAccess properties of the SqlFunction attribute to DataAccessKind.Read, and SystemDataAccessKind.Read:
[SqlFunction(DataAccess=DataAccessKind.Read, SystemDataAccess.SystenDataAccessKind.Read)]
your method name
2. You can not update/insert/delete data from an UDF
HTH
Niels
|||Thanks! It works perfectly now. I'm new to writing functions for sqlserver in visual studio so I had no idea i needed to do that. Fortunately I didn't need to update/insert/delete any data. =)
The reason I needed to do this was because the data I need to work with needs to be picked out of a table and sorted and then calculated from but sqlserver wasn't letting me make a temporary table in a function.
