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.

Is the problem the type choice, or is the problem that you haven't specified a length? — Aaron Bertrand, Jun 18 '12 at 22:08

score 44 · Accepted Answer · answered Jun 18 '12 at 23:45

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

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. — BornToCode, Sep 01 '15 at 16:51
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? — JamesFaix, Jul 16 '18 at 18:49
@JamesFaix use -1 for max. Even using Int32.MaxLength/2 leads to overflows. — Dan, Nov 07 '18 at 18:25

score 3 · Answer 2 · answered Jun 18 '12 at 21:35

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);
        }
    }

The only difference I see is NVarchar to varchar. That's what I originally started with and the result is similar. — P.Brian.Mackey, Jun 18 '12 at 21:57
also changed the column name as I believe that would error, do you have more code for me to run unit tests with? — Neo, Jun 19 '12 at 06:11

The dbType NVarChar is invalid for this constructor

2 Answers2