How to return nvarchar(max) to SQL server from CLR?
----------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlString Select_Description(int Obj_ID)
    {

        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            try
            {
                string sql = @"Select description from tbl_detail";

                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                return (SqlString)cmd.ExecuteScalar();

            }
            catch (Exception e)
            {
                throw e;
            }

            finally
            {
                conn.Close();
            }
        }
    }

};
----------------------

Code above work fine with any [description]< 4000 characters. Get an error if description field have values> 4000 characters.

Try to solve ==> Looking for the net they said change SqlString ==> SqlChars

Here my modify code
------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlString Select_Description(int Obj_ID)
    {

        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            try
            {
                string sql = @"Select description from tbl_detail";

                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                return (SqlChars)cmd.ExecuteScalar(); //<== is this correct syntax?

            }
            catch (Exception e)
            {
                throw e;
            }

            finally
            {
                conn.Close();
            }
        }
    }

};
------------------------

the error come when compile

Error 1 Cannot implicitly convert type 'System.Data.SqlTypes.SqlChars' to 'System.Data.SqlTypes.SqlString'. An explicit conversion exists (are you missing a cast?)

Please teach me how to solve this problem. Thank you.
I'm newbie on CLR and .NET

-------------------------------
Dear Gonzalo,

I try to make my data simple to explain I will post short data construct and values soon, hope you have time give me your hand to solve my problem about my SQL query.

Hi Gonzalo,


I have solve all my problem now.
---------------------------------
[Microsoft.SqlServer.Server.SqlFunction(FillRowMeth odName="Obj_Row",
                                                IsDeterministic=true,
                                                IsPrecise=true,
                                                TableDefinition = "ObjID int,ObjCategoryID int,ObjValue nvarchar(400)",
                                                DataAccess= DataAccessKind.Read,
SystemDataAccess = SystemDataAccessKind.Read )] <== After add this line CLR funtion work fine.

My function A, call function B, and Store procedure C. I change some of them to CLR may be that need SystemDataAccessKind.Read ------------------------------

Other question about sqlchars, I solve that problem too. Thank you for reading my post.