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.

Reproducing it is not hard

1. Define a tabe type

create type Array as table (ID int, Value varchar(20))

2. Create CLR function

Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
public static SqlBoolean test1()
{
 SqlConnection connection = new SqlConnection("Context Connection = True");
 connection.Open();
 SqlCommand cmd = connection.CreateCommand();
 cmd.CommandText = @"declare @id int
select top 1 @ID=ID from @p z ";
 cmd.Parameters.Add("@p", SqlDbType.Structured).TypeName = "Array";
 DataTable dt = new DataTable();
 dt.Columns.Add("ID", typeof(int));
 dt.Columns.Add("Value", typeof(string));
 dt.Rows.Add(1, "a");
 cmd.Parameters[0].Value = dt;
 cmd.ExecuteNonQuery();
 //cmd.ExecuteNonQuery();
 connection.Close();
 return new SqlBoolean(true);
}

3. Run following code, you will not see any problem.

select dbo.test1() 

4. Now, let’s uncomment the code in CLR.

[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
public static SqlBoolean test1()
{
 SqlConnection connection = new SqlConnection("Context Connection = True");
 connection.Open();
 SqlCommand cmd = connection.CreateCommand();
 cmd.CommandText = @"declare @id int
select top 1 @ID=ID from @p z ";
 cmd.Parameters.Add("@p", SqlDbType.Structured).TypeName = "Array";
 DataTable dt = new DataTable();
 dt.Columns.Add("ID", typeof(int));
 dt.Columns.Add("Value", typeof(string));
 dt.Rows.Add(1, "a");
 cmd.Parameters[0].Value = dt;
 cmd.ExecuteNonQuery();
 cmd.ExecuteNonQuery();
 connection.Close();
 return new SqlBoolean(true);
}
<.pre>
5. Run following code again, you WILL see the error message <pre class="brush: sql; gutter: true; first-line: 1; highlight: []; html-script: false">select dbo.test1() 

The error returned

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "test1": 
System.Data.SqlClient.SqlException: 
System.Data.SqlClient.SqlException: 
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
 at System.Data.SqlClient.SqlCommand.SetUpSmiRequest(SqlInternalConnectionSmi innerConnection)
 at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)
 at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
 at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
 at hahaha.test1()

Interesting enough, event though you close and reopen and connection between calls, the error will still exist. The error information in the server log is

Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

Dump file is generated as well.

Share/Bookmark