Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

John Huang’s Blog

John Huang, Microsoft Certified Master in SQL Server, SQL Server MVP, is an independent SQL Server consultant in Vancouver BC, Canada. He started using SQL Server for his projects in year 1994. He has architected and implemented many SQL and BI applications serving different industrial areas. He loves talking about SQL Server and discussing SQL Server technologies with others. He blogs at http://www.sqlnotes.info.

Bug? Can’t Pass TVP to SQL Function Twice

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.

LinkedInEmailBlogger PostShare/Bookmark

Comments

Leave a comment on the original post [www.sqlnotes.info, opens in a new window]

Loading comments...