\nI came across the following interesting behavior while testing a SQLCLR table-valued function that did work in SQL Server 2005 SP2 but doesn't work at all in SQL Server 2008. It appears to be by design, because the error message (in 2008) clearly indicates what's wrong. But the code worked in SQL Server 2005 and if you depend on this code behavior, it's a breaking change. And I haven't seen this in a readme file or BOL "What's New" section.\n<\/p>\n
\nFirst, some background. .NET procedures are allowed to do any kind of "data access" including getting the Windows Identity, accessing the local database instanace, etc. .NET user-defined functions, however, are not permitted data access unless they are marked with a SqlFunction attribute specifying DataAccess=DataAccessKind.Read. Accessing certain session information requires SystemDataAccess=SystemDataAccessKind.Read as well.\n<\/p>\n
\n.NET table-valued functions consist of a UDF function method and also a FillRowMethod. The UDF function method must return an instance of a .NET type that implements IEnumerable or IEnumerator. This can be a class that you provide or one of the build-in .NET types such as System.Array. SQL Server will call its Enumerator and call back to FillRowMethod once for every time the enumerator returns true.\n<\/p>\n
\nIn SQL Server 2005, you can do "data access" in the UDF method, the FillRowMethod, or any of the other methods in the class (like the enumerator's MoveNext method). Only the UDF method must be marked DataAccess=DataAccessKind.Read and only the UDF method CAN be marked with the SqlFunction attribute and produce the desired effect.\n<\/p>\n
\nIn SQL Server 2008, attempting to do data access in the FillRowMethod now throws an exception. Perhaps the behavior change was required to implement a new SQLCLR feature, ordered TVFs, but I'm only guessing that ordered TVFs are the reason. Perhaps it was never intended to work. The error message in 2008 is pretty clear:\n<\/p>\n
\n"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."\n<\/p>\n
\nThere's no workaround for this if you choose a table-valued function, except to do all your data access in the TVF method only. But SQLCLR provides another way to way to stream a rowset of data you synthesize yourself, using SqlMetaData, SqlDataRecord, and SqlPipe methods in a SQLCLR stored procedure. You can get almost the same result (streamed rowset) in such a stored procedure and "data access" is always allowed in SQLCLR stored procedure code.<\/p>\n","protected":false},"excerpt":{"rendered":"
I came across the following interesting behavior while testing a SQLCLR table-valued function that did work in SQL Server 2005 SP2 but doesn't work at all in SQL Server 2008. It appears to be by design, because the error message (in 2008) clearly indicates what's wrong. But the code worked in SQL Server 2005 and […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29,38],"tags":[],"class_list":["post-680","post","type-post","status-publish","format-standard","hentry","category-sql-server-2008","category-sqlclr"],"yoast_head":"\n