SQL CLR Issues VS2010 SQL Server 2012
I am trying to create a simple CLR UDF using Visual Studio 2010 and SQL Server 2012. It builds fine, but when I try to debug I get this error:
SqlClrDeploy:
Starting deployment of CCOMM_CLR.dll assembly on Titan: CBMAPP_REMAS
When deploying a SQL CLR project that was created for a .NET Framework version that is incompatible with the target SQL Server instance, the following error may occur: "SQL01268 deployment failed: CREATE ASSEMBLY for assembly failed because the assembly failed validation. " To resolve this issue, open the properties for the project and change the version of the .NET Framework. C: \ Program Files (x86) \ MSBuild \ Microsoft \ VisualStudio \ v10.0 \ TeamData \ Microsoft.Data.Schema.SqlClr.targets (96.5): SQL01234 deployment error: Database version not supported.Build FAILED
So ... how about building and deploying to a machine? This 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 whole UDF
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString HelloWorld(SqlString theName)
{
// Put your code here
return "Hello " + theName;
}
};
Installing the assembly and UDF works fine, but when I run it ...
Select Master.dbo.HelloXP('Chris')
I am getting this error
Msg 6522, Level 16, State 2, Line 2
A .NET Framework error occurred while executing a custom routine or aggregate "HelloXP": System.Data.SqlServer.TruncationException: Attempting to convert a return value or output parameter of 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? It looks pretty straight forward in the MS examples, so something messes up. Yes CLR is enabled on the server. I am already doing extended stored procedures and they seemed much simpler than the CLR.
Thank you, Chris
source to share
Regarding your first problem:
Deployment error SQL01234: Database version not supported.
I don't know the exact solution, but since SQL Server 2012 was not yet released during Visual Studio 2010, and SQL Server 2012 has a new version of the Tabular Data Flow Protocol (TDS) that Visual Studio 2010 doesn't know yet, it looks like compiling with Visual Studio 2012 or later will resolve this issue.
As for the second problem:
".NET Framework error while executing user routine or aggregate
HelloXP
:System.Data.SqlServer.TruncationException
Attempted to convert a return value or output parameter of 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 forNVARCHAR
, if you omit the capacity specification, then 1 character (i.e. 2 bytes) capacity is assumed. The string created in your C # ( "Hello " + theName
) method contains more than 1 character (at least 6 of "Hello "
) and is therefore truncated; thus a runtime exception.
Try changing the return type to something like NVARCHAR(100)
or NVARCHAR(MAX)
. The same applies to the input parameter @Name
. Make sure the return type has a capacity of at least 6 characters more than the capacity @Name
.
source to share