When passing a binary value in an sql_variant to/from the CLR, NULL is passed - by Erland Sommarskog

Status : 

  External<br /><br />
		This item may be valid but belongs to an external system out of the direct control of this product team.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
0
Sign in
to vote
ID 738092 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 4/21/2012 12:47:00 PM
Access Restriction Public

Description

When passing a binary value inside sql_variant to a CLR procedure which in its turn calls a T-SQL procedure, the T-SQL procedure sees NULL and not the binary value.

I have not researched where things go wrong, when passing the value to the CLR procedure or when passing from the CLR to T-SQL.

All other data types works with SQL 2012 and .Net 4 as I can tell.  (On SQL 2008, money and smallmoney causes a TDS error, but that appears to have been fixed.)
Sign in to post a comment.
Posted by Erland Sommarskog on 2/9/2013 at 4:30 AM
Hm, if you confirm that it works with all data types, maybe you could get away with documenting this as a best practice? :-)
Posted by Microsoft on 2/8/2013 at 4:02 PM
Hi Erland,

I agree that this is a bug, and we should do something about it. I also agree with you that the behavior should be consistent -- documenting an anomaly like this does not inspire confidence.
However, there is a workaround that should make the behavior consistent (although I have not personally tried every possible sql_variant type).
Use the following pattern when assigning the SQLCLR SP's input parameters to the SqlCommand parameters for the logging SP:

cmd.Parameters["@p1"].Value = p1.GetType().GetProperty("Value").GetValue(p1, null);

This uses reflection to reference the Value property of the input SQL type. You should, perhaps, protect from an exception here. But, as long as the assembly is used only as a stored procedure, you will always see a SQL type object on input.

Have a good weekend!

Ward Beattie, MSFT
Posted by Erland Sommarskog on 2/5/2013 at 11:50 AM
A corner case is for me when something goes wrong when you press two buttons at the same time, and it is also full moon. If we are supposed to be able pass sql_variant data to CLR procedures "this should work". At the same, I agree that this is not a very important use case. I was able to get my stuff to work by using nvarchar(4000) instead.

No, this is not a regression. When I tested my CLR procedure, I found that there were two data types that did not work, binary and one more. This was on SQL 2008. Before I reported the issues, I retested on SQL 2012 and found that binary was still not working, while the other data type was, and thus I only reported binary.

I think that if you decide not to fix it, that you should document it.
Posted by Microsoft on 2/4/2013 at 7:45 PM
Erland, I apologize for the long delay in getting back on this. Our initial take was that this was a very corner scenario. I am now looking at it more closely. Before I explore the repro, I wonder if you know whether this is a regression from SQL Server 2008 or SQL Server 2008 R2? Thanks for your help.
Posted by Umachandar [MSFT] on 6/7/2012 at 11:51 AM
Hello Erland,
Thanks for reporting this issue. We will take a look at the repro and let you know what we find.

--
Umachandar