CLR Integration function that will take an Image/Varbinary value as input

Question

I am trying to create a CLR integration function in SQL Server 2008 R2 which will read a Image/Varbinary value and return a NVARCHAR(MAX).

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString ConvertByteArrToString(SqlBinary arrByte)

And in SQL Server -

create function ReadAsString(@varData varbinary(max))
returns nvarchar(max)
as
    external name CLRFunctions.[CLRFunctions.FormatUtilities].ConvertByteArrToString

But, when I try to create above function, I get this error:

Msg 6552, Level 16, State 3, Procedure ReadAsString, Line 1
ALTER FUNCTION for "ReadAsString" failed because T-SQL and CLR types for parameter "@varData" do not match.

Can someone suggest what should be the correct CLR match for SQL Server Image/Varbinary(MAX)?

http://stackoverflow.com/a/4764583/73226 – Martin Smith Apr 12 '15 at 19:34 — Martin Smith, Apr 12 '15 at 19:34

score 0 · Answer 1 · edited May 23 '17 at 12:15

The technical answer to the Question as asked is: SqlBytes maps to VARBINARY(MAX) when using the Visual Studio deployment option (and there is no mapping to IMAGE which is deprecated and shouldn't be used). But that is for older versions of Visual Studio and SSDT (SQL Server Data Tools). The more recent versions now map SqlString and SqlChars to NVARCHAR(MAX), and SqlBinary and SqlBytes to VARBINARY(MAX). Some additional details are provided in this answer: CLR UDF returning Varbinary(MAX) .

HOWEVER, given that you are using SQL Server 2008 R2, this SQLCLR function doesn't even need to be created in the first place since it is functionality built into the CONVERT built-in function:

SELECT CONVERT(NVARCHAR(MAX), 0x1234ABCD);    -- 㐒춫
SELECT CONVERT(NVARCHAR(MAX), 0x1234ABCD, 0); -- 㐒춫
SELECT CONVERT(NVARCHAR(MAX), 0x1234ABCD, 1); -- 0x1234ABCD
SELECT CONVERT(NVARCHAR(MAX), 0x1234ABCD, 2); -- 1234ABCD

CLR Integration function that will take an Image/Varbinary value as input

1 Answers1