Sql-server – Passing NULL xml value to a certain CLR stored function crashes it when called in a certain way

crashsql-clrsql-server-2008xml

Consider a CLR stored procedure that accepts an XML parameter and calls another stored procedure in the same schema:

[SqlFunction(DataAccess = DataAccessKind.Read,
     IsDeterministic = false, IsPrecise = true,
     SystemDataAccess = SystemDataAccessKind.Read)]
public static SqlBoolean TestTest(SqlXml Data)
{
    using (var c = new SqlConnection("context connection=true"))
    {
        c.Open();

        using (var cmd = new SqlCommand(@"select case when exists(select 0 from [testing].WillBeCalledByCLR(@d)) then 1 else 0 end;", c))
        {
            var p = cmd.Parameters.Add("@d", SqlDbType.Xml);

            p.Direction = ParameterDirection.Input;
            if (Data.IsNull)
                { p.Value = DBNull.Value; }
            else
                { p.Value = Data; }  // Or Data.Value

            return (bool)cmd.ExecuteScalar();
        }
    }
}

This CLR function is visible in SQL as:

create function [testing].[TestTest] ( @data xml )
returns bit
WITH CALLED ON NULL INPUT
AS
EXTERNAL NAME [Test].[Test.Test].[TestTest]

The stored function it calls is this:

create function testing.WillBeCalledByCLR (@x xml = null)
returns table
as
return (
  select 1 as one, 2 as two, 'three' as three
);

In this setup, if I call the CLR function like this:

if testing.TestTest(null) = 1
begin
  select 'Meaningful actions';
end;

or like this:

declare @res bit = testing.TestTest(null);

or like this:

declare @res bit;
set @res = testing.TestTest(null);

or like this:

declare @res bit;
select @res = testing.TestTest(null);

then I get:

Msg 2905, Level 25, State 1, Line 6

Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

But if I call it like this:

select testing.TestTest(null);

or like this:

declare @res bit;
set @res = (select testing.TestTest(null));

I get a proper value back (e.g. 1).

If, instead of null, I pass an empty string '', the function is successfully called in all cases.

Why? Did I do something wrong in my CLR function?

Microsoft SQL Server 2008 (SP2) – 10.0.4000.0 (X64)
Sep 16 2010 19:43:16
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2) (VM)

Best Answer

Overall, no, you did not do anything wrong with your .NET code. There is a minor technical issue, but we will get to that in a moment.

I was able to reproduce this (both error and non-error scenarios) in SQL Server 2008 R2 RTM. At first, in the "non-error" scenario, I was getting another error for:

specified cast is invalid

This error was a result of the query passing back an INT and the C# code attempting to cast it to a bool. I changed the query to be:

... then CONVERT(BIT, 1) else CONVERT(BIT, 0) end ...

That forces the datatype of the value to be returned to be something that can be cast into bool. This was the "minor error" that I was referring to, and I consider it "minor" and not really an error because I suspect your code originally attempted to cast into int and returned a SqlInt32 since you claim that passing in an empty string allowed it to return successfully. There is no way this would ever return successfully, even with an empty string instead of NULL for the input value, if your code was attempting to cast to bool and return SqlBoolean. And yes, I could have changed the cast to be (int) and the return type to be SqlInt32, but this was less of a structural change.

I tested several types of changes: removing the execution, removing the @d parameter and hard-coding the null into the query but still having the SqlParameter defined, removing the @d parameter and the SqlParameter, etc. What it finally came down to is this: merely the existence of a SqlParameter of a LOB type (i.e. XML, VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) ), even if unused in the query, caused the "severe error" when passing in a NULL. However, if any of those MAX types were changed to be their maximum non-MAX lengths (i.e. 8000, 4000, and 8000 respectively), then there was no error when passing in a NULL. Hence the following reduced code should be enough to reproduce the error when passing in a NULL to the SQLCLR scalar function:

using (var cmd = new SqlCommand(@"SELECT 1;", c))
{
    var p = cmd.Parameters.Add("@d", SqlDbType.NVarChar, (int)SqlMetaData.Max);

    cmd.ExecuteScalar();
}

Now, I was not able to reproduce the error with the original code in SQL Server 2012 SP3 or SQL Server 2014 SP1 :-). Therefore, I suspect that this was an issue with CLR 2.0 and/or the .NET Framework versions associated with CLR 2.0 (i.e. "2.0", "3.0", and "3.5"). OR, another aspect to consider is that it with this failing ( declare @res bit; set @res = testing.TestTest(null); ) while this works ( declare @res bit; set @res = (select testing.TestTest(null)); ) that could point to a problem with the Query Optimizer and not with the CLR version. Or maybe it is a combination of the two. Or perhaps it is Descartes's pesky Evil Demon. Either way. ^{( ** please see comment at the end )}

If you are able to move up to SQL Server 2012 or newer, then the problem should "magically" disappear. BUT, if stuck on SQL Server 2008 / 2008 R2, I was able to work around the issue by declaring the parameter to be a non-MAX type if the input is NULL, which should be fine since there won't be any risk of truncation when passing through a NULL. And, in the case of the input parameter datatype of the T-SQL object being called being XML, it still works to define the parameter datatype in the .NET code as NVARCHAR(4000) as it will implicitly convert that into XML when called. The following code worked for me in SQL Server 2008 R2 when passing in a NULL:

SqlParameter _XmlData;

if (Data.IsNull)
{
    // Parameter can't be a LOB type if input is NULL
    _XmlData = new SqlParameter("@d", SqlDbType.NVarChar, 4000);
    _XmlData.Value = DBNull.Value;
}
else
{
    _XmlData = new SqlParameter("@d", SqlDbType.Xml);
    _XmlData.Value = Data.Value;
}

cmd.Parameters.Add(_XmlData);

** @MartinSmith left this illuminating comment:

Specifically the error seems to be "Access violation reading location 0x0000000000000000" inside sqlservr.exe!CClrXvarProxy::EnsureBufferInit so looks like a null pointer bug but googling those method names doesn't return any KB article.

Related Solutions

Sql-server – Is it possible to get SQL Server 2008 to download a file from a URL

Use an external process that does the HTTP work and the inserts into the database. I explicitly advise against using SQLCLR for this. Hijacking precious SQL Server workers for the boring job of waiting for HTTP results will one day impact your server severely.

but the size of the XML can be quite variable (esp given that a binary would pass it to the stored proc using L2S) and Iv'e already overflowed the input to the SP a couple of times because there's been too much data

Use the techniques from DOWNLOAD AND UPLOAD IMAGES FROM SQL SERVER VIA ASP.NET MVC to stream the HTTP response into the database.

SQL Server – Capture PRINT Statements from TSQL Called by CLR Stored Procedure

In order to capture messages (either from PRINT or RAISERROR('', 1, 10) or like it) in .NET (regardless of calling a Stored Procedure or ad hoc SQL), you need to set up a method that will get called by the SqlConnection.InfoMessage event.

The basic implementation is as follows:

[SqlProcedure()]
public static void Print_CLR()
{
    using (SqlConnection conn = new SqlConnection("context connection=true"))   
    {
        conn.InfoMessage += new SqlInfoMessageEventHandler(CaptureMessage);
        conn.Open();

        using (SqlCommand c = new SqlCommand("exec dbo.Print_TSQL", conn))
        {   
            c.ExecuteNonQuery();    
        }
    }
}

private static void CaptureMessage(object sender, SqlInfoMessageEventArgs args)
{
  // do something with args.Message

  return;
}

At this point, you still need to do something with the Message. While there are a few options, some of them have security implications due to the environment. SQL Server's CLR host is highly constrained compared to the main CLR running on the OS that is used for Windows Apps, Console Apps, and ASP.NET apps.

If you use the static method approach as shown above, then what you do with the Message will impact the PERMISSION_SET value for the assembly containing this code:

If you simply want to print the message back to the user, then you can do something like the following which can be done in a SAFE assembly:
```
SqlContext.Pipe.Send(args.Message);
```
If you want to write the messages to a file, then you can do something like the following which can be done in an EXTERNAL_ACCESS assembly:
```
File.AppendAllText(FILE_PATH_CONSTANT, args.Message);
```
This might cause extra latency and not be worth it. But on the other hand, if the process fails in the middle, whatever was written to the file would still be there, so there is definitely some benefit to this approach.
But if you want to store the messages in a variable to do something with later, then you will need a static class variable since the method to capture the message is static. For example, you can declare private static m_Messages StringBuilder = new StringBuiler("");. Then, in your CaptureMessage method you can do the following:
```
m_Messages.AppendLine(args.Message);
```
This seems simple enough. And for most any environment outside of SQLCLR it is. However, using a static variable in SQLCLR requires that the assembly have a PERMISSION_SET of UNSAFE, and it is best to avoid that if at all possible. And the reason that the assembly is required to be marked as UNSAFE is that the AppDomain where this code is running is shared across all Sessions / SPIDs. Hence, a static class variable is also shared across Sessions and that can lead to very odd behaviour.

Thankfully, all hope is not lost if you want to collect messages to an instance variable. To do this, you need to define the handler inline using an anonymous delegate. The anonymous method is within the same scope as the rest of your code so it has access to local variables. This is quite handy as it allows you to easily store the messages in an assembly that has a PERMISSION_SET of SAFE (which is highly preferred).

[SqlProcedure()]
public static void Print_CLR()
{
    StringBuilder _Messages = new StringBuilder("");

    using (SqlConnection conn = new SqlConnection("context connection=true"))   
    {
        conn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs args)
        {
            SqlConnection _Connection = (SqlConnection)sender;
            // _Connection.DataSource is the "Server" of the ConnectionString
            // _Connection.Database is the CURRENT database of the Connection

            _Messages.Append(args.Message);

            return;
        };

        conn.Open();

        using (SqlCommand c = new SqlCommand("exec dbo.Print_TSQL", conn))
        {   
            c.ExecuteNonQuery();    
        }
    }
}

Best Answer

Related Solutions

Sql-server – Is it possible to get SQL Server 2008 to download a file from a URL

SQL Server – Capture PRINT Statements from TSQL Called by CLR Stored Procedure

Related Question