Hi trying to upgrade my current SQL 2000 database to 2008.
All the legacy code uses VARCHAR: tables columns, variables, input/output of SPs and XPs etc...
I'm trying to port an existing extended stored procedure to CLR stored procedure. The output parameter for the old xp is varchar(8000). In CLR it is obviously SQLString which maps to NVARCHAR(4000)
Now the legacy code which is expecting VARCHAR(8000) output is calling the new CLR which returns NVARCHAR(4000). The CLR procedure can indeed return more then 4000 characters. I can use SQL facet size of -1 to force NVARCHAR(MAX). But I hear the performance
is slow. Also what are the consequences of a CLR returning NVARCHAR to an SQL SP that expects VARCHAR?
Is there a way to return larger string from a CLR to SQL code that expects varchar?
This is the error I'm getting
Msg 6522, Level 16, State 1, Procedure xp_XYZ, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "xp_XYZ":
System.Data.SqlServer.TruncationException: Trying to convert return value or output parameter of size 8000 bytes to a T-SQL type with a smaller size limit of 8000 bytes.
at System.Data.SqlServer.Internal.CXVariantBase.StringToWSTR(String pstrValue, Int64 cbMaxLength, Int32 iOffset, EPadding ePad)
View Complete Post