I run into a bug recently with SQL Server 2012. I believe this exists in SQL Server 2008 as well. When a TVP is passed to a SQL function/procedure/script through an CLR function, the second execution will cause error which will be written to SQL Server log — Process x generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. It has been filed to Microsoft Connect. If you think it’s important, please visit this bug on Microsoft Connect and vote it as important. Please download the code from here.
After SQL Client of SQL Server 2012 is installed to the client computer which also have Visual Studio 2010 installed, I am excited about deploying CLR with .NET 4.0. I launched Visual studio, started a Visual C# CLR database Project, created a simple CLR project, deploy, and then failed. Tried again, and failed again…
Functions are the most restrictive code blocks you can define in SQL Server. They perform just like stored procedures in which it can return set and output parameters. But you cannot create or modify objects and the data within the object in a function except the objects are table variables and table value type variables. Actually any transaction depended objects are not allowed to run in a function, such as
RECEIVE. You can run
exec proc within a function but only some extended procedures are allowed. One is not allowed but most developers(including me) desire is to run sp_executesql in a function. You can get around it by creating CLR functions.