SQL CLR problems VS2010 SQL Server 2012

Question

I'm trying to create a simple CLR User defined function using Visual Studio 2010 and SQL Server 2012. It builds just fine, but when I try to debug I get this error:

SqlClrDeploy:
Beginning deployment of assembly CCOMM_CLR.dll to server Titan : CBMAPP_REMAS
The following error might appear if you deploy a SQL CLR project that was built for a version of the .NET Framework that is incompatible with the target instance of SQL Server: "Deploy error SQL01268: CREATE ASSEMBLY for assembly failed because assembly failed verification". To resolve this issue, open the properties for the project, and change the .NET Framework version. C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): Deploy error SQL01234: The database version is not supported.

Build FAILED

So... How about build and deploy on the machine it self... Here is the code to install the assembly and the UDF

CREATE ASSEMBLY CCOMM_CLR 
  FROM 'E:\SQL\ASSEMBLIES\CCOMM_CLr.dll' WITH PERMISSION_SET = SAFE;

CREATE FUNCTION HelloXP(@Name nvarchar )
Returns nvarchar
As EXTERNAL NAME CCOMM_CLR.UserDefinedFunctions.HelloWorld;

and here is the entire UDF

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString HelloWorld(SqlString theName)
    {
        // Put your code here
        return  "Hello " + theName;
    }
};

Installing of the assembly and the UDF works fine, but when I run it...

Select Master.dbo.HelloXP('Chris')

I get this error

Msg 6522, Level 16, State 2, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "HelloXP": System.Data.SqlServer.TruncationException: Trying to convert return value or output parameter of size 14 bytes to a T-SQL type with a smaller size limit of 2 bytes.
System.Data.SqlServer.TruncationException:
at System.Data.SqlServer.Internal.CXVariantBase.StringToWSTR(String pstrValue, Int64 cbMaxLength, Int32 iOffset, EPadding ePad)

What am I doing wrong? This seems fairly straight forward in the examples by MS so something is messed up. Yes CLR is enabled on the server. I'm already running extended stored procedures and they seemed a lot easier than the CLR.

Thanks, Chris

I recall a similar issue where you had to build your assembly with the same .NET framework version that the sql server uses. I think 2012 was 4.0 and 2008 was 3.5 - but those are guesses. I'll try to find documentation. — snow_FFFFFF
– snow_FFFFFF, Commented Jan 7, 2015 at 21:13
This might help you determine detail about your server: select * from sys.dm_clr_properties — snow_FFFFFF
– snow_FFFFFF, Commented Jan 7, 2015 at 21:16
It is version 4 and the assembly is using version 4. I've also tried 3.5, 3.0 and 2.0. Same results. — MtnManChris
– MtnManChris, Commented Jan 7, 2015 at 21:29

stakx - no longer contributing · Accepted Answer · 2015-01-07 21:42:13Z

2

Concerning your first issue:

Deploy error SQL01234: The database version is not supported.

I don't know the exact solution, but since SQL Server 2012 wasn't released yet at the time of Visual Studio 2010, and SQL Server 2012 introduced a new version of the Tabular Data Stream (TDS) protocol which Visual Studio 2010 tooling wouldn't know yet, it seems likely that compiling with Visual Studio 2012 or later would solve this issue.

Concerning your second issue:

"A .NET Framework error occurred during execution of user-defined routine or aggregate HelloXP: System.Data.SqlServer.TruncationException: Trying to convert return value or output parameter of size 14 bytes to a T-SQL type with a smaller size limit of 2 bytes."

You have declared the return type of your UDF as NVARCHAR:

CREATE FUNCTION HelloXP (@Name NVARCHAR)
RETURNS NVARCHAR
…

According to the MSDN documentation for NVARCHAR, if you omit the capacity specification, then a capacity of 1 character (i.e. 2 bytes) is assumed. The string created in your C# method ("Hello " + theName) contains more than 1 character (at the very least 6 from "Hello ") and is therefore truncated; thus the run-time exception.

Try changing the return type to something like NVARCHAR(100) or NVARCHAR(MAX). Same goes for the input parameter, @Name. Make sure the return type has a capacity at least 6 characters more than the capacity of @Name.

edited Jan 7, 2015 at 21:42

answered Jan 7, 2015 at 21:32

stakx - no longer contributing

85.1k20 gold badges176 silver badges277 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MtnManChris Over a year ago

Yes, I assumed the problem from VS2010 to SQL2012 was an issue. I was hoping to find a template that would work with 2012 but I could not find it looking on line. Therefore I tried to manually put it up on the server. The ANSWER is to change the nvarchar to a specific size. This worked.

Collectives™ on Stack Overflow

SQL CLR problems VS2010 SQL Server 2012

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related