The dbType NVarChar is invalid for this constructor

Question

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void MyMethod()
    {
        string connectionString = "context connection=true";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            SqlMetaData[] metaData = {
                                         new SqlMetaData("Column1", System.Data.SqlDbType.NVarChar)
                                         ,new SqlMetaData("Column1", System.Data.SqlDbType.NVarChar)
                                     };
            SqlDataRecord record = new SqlDataRecord(metaData);
            record.SetString(0,"hello world");
            SqlContext.Pipe.SendResultsRow(record);
        }
    }

When I run the method in SQL

EXEC MyMethod

Error

Msg 6522, Level 16, State 1, Procedure MyMethod, Line 0 A .NET Framework error occurred during execution of user-defined routine or aggregate "MyMethod": System.ArgumentException: The dbType NVarChar is invalid for this constructor. System.ArgumentException: at Microsoft.SqlServer.Server.SqlMetaData.Construct(String name, SqlDbType dbType, Boolean useServerDefault, Boolean isUniqueKey, SortOrder columnSortOrder, Int32 sortOrdinal) at Microsoft.SqlServer.Server.SqlMetaData..ctor(String name, SqlDbType dbType) at WcfClrApps.MyNamespace.MyMethod()

How can a return a record that I create myself? I do not want to run any SQL. Project build is set for .NET 3.5. MSDN indicates 4.0 is not supported in SQL 2008 R2.

Solution 1

The problem is two fold. 1. Max length is required. 2. SendResultsStart()/SendResultsEnd() are required.

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void MyMethod()
    {
        string connectionString = "context connection=true";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            SqlMetaData[] metaData = {
                                         new SqlMetaData("Column1", System.Data.SqlDbType.NVarChar, 100)//Max length has to be specified
                                         ,new SqlMetaData("Column1", System.Data.SqlDbType.NVarChar, 100)//same story
                                     };
            SqlDataRecord record = new SqlDataRecord(metaData);
            SqlContext.Pipe.SendResultsStart(record);//SendResultsStart must be called

            //create a row and send it down the pipe
            record.SetString(0,"hello world");
            SqlContext.Pipe.SendResultsRow(record);

            SqlContext.Pipe.SendResultsEnd();//End it out

        }
    }

Iterative example

Solution 2

Never done anything this way, but wouldn't this work?

[Microsoft.SqlServer.Server.SqlProcedure]
    public static void MyMethod()
    {
        string connectionString = "context connection=true";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            SqlMetaData[] metaData = {
                                         new SqlMetaData("Column1", System.Data.SqlDbType.VarChar)
                                         ,new SqlMetaData("Column2", System.Data.SqlDbType.VarChar)
                                     };
            SqlDataRecord record = new SqlDataRecord(metaData);
            record.SetString(0,"hello world");
            SqlContext.Pipe.SendResultsRow(record);
        }
    }

Comments

[Adir D]

Is the problem the type choice, or is the problem that you haven't specified a length?

[yairr]

The only difference I see is NVarchar to varchar. That's what I originally started with and the result is similar.

[Neo]

also changed the column name as I believe that would error, do you have more code for me to run unit tests with?

[BornToCode]

For the specific error The dbType NVarChar is invalid for this constructor only adding the MaxLength parameter solves the problem. Turns out that when using SqlDbType.NVarChar, you must use the constructor which accepts the MaxLength parameter.

[JamesFaix]

I am having this problem too. The type I want to use is NVARCHAR(MAX). When I set the max length in the SqlMetaData constructor to Int32.MaxLength I get the error Specified length '2147483647' is out of range. What is the allowed max length?

[Dan]

@JamesFaix use -1 for max. Even using Int32.MaxLength/2 leads to overflows.