none
[Info Sharing]: When specifying an NVarChar parameter with SqlParameter.Size between 4001 and 8000, SqlClient may throw the exception.

    General discussion

  • By Kayoko Nakano-Gray

    SYMPTOMS
    =======
    When you specify an NVarChar parameter with SqlParameter.Size between 4001 and 8000, SqlClient may throw the following exception.

    SqlException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@ParameterName"): Data type 0xE7 has an invalid data length or metadata length.
    Source: .Net SqlClient Data Provider

    Same error may not be encountered if you specify SqlParamter.Size excluding the following values, between 4001 and 8000.


    Resolution
    =============================
    WORKAROUND
    ==========
    - Use an output size of -1 to make sure you are getting the entire data without truncation, or specify excluding values: 4001 to 8000.
    - For the parameter conversion, added a condition where the type is NVarChar and the size is larger than 4000.

     

    Repro Steps
    =============================
    1. Create following table to the target SQL Server.

    use AdventureWorks
    go
    create table test (c1 nvarchar(max))
    go

    2. In Microsoft Visual Studio, create a new Console Application by using Visual Basic .NET
    3. Replace the existing code with the following code:

    Visual Basic .NET code
    Imports System.Data.SqlClient
    Imports System.Text
    Module Module1
        Dim instance As SqlException
        Sub Main()
            Dim cnStr As String
            Dim cmdStr As String
            Dim retVal As String
            Dim errMsg As New StringBuilder()

            'get parameter.size from the console input.
            Console.WriteLine("Specify the size for SqlParameter.Size. Specifying between 4001 and 8000, SqlClient may throw the exception.")
            Console.WriteLine()
            Console.WriteLine("After input, press Enter")
            Console.WriteLine()
            retVal = Console.ReadLine()
            Console.WriteLine("SqlParameter.Size is specified:      " + retVal)

            'connect to SQL Server
            cnStr = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True"
            cmdStr = "insert into test values (@ParameterName)"

            Using connection As New SqlConnection(cnStr)
                Dim command As New SqlCommand(cmdStr, connection)
                Dim param As SqlParameter = New SqlParameter("@ParameterName", SqlDbType.NVarChar, retVal)

                param.Value = "TestValue"
                command.Parameters.Add(param)

                Try
                    command.Connection.Open()
                    Dim iRowsAffected As Integer = command.ExecuteNonQuery()

                    If (iRowsAffected > 0) Then
                        Console.WriteLine("{0} rows affected.", iRowsAffected)
                        connection.Close()
                    End If
                    Console.Write("done")

                Catch ex As SqlException
                    Dim i As Integer
                    For i = 0 To ex.Errors.Count - 1
                        errMsg.Append("Index #" & i.ToString() & ControlChars.NewLine _
                            & "Message: " & ex.Errors(i).Message & ControlChars.NewLine _
                            & "LineNumber: " & ex.Errors(i).LineNumber & ControlChars.NewLine _
                            & "Source: " & ex.Errors(i).Source & ControlChars.NewLine _
                            & "Procedure: " & ex.Errors(i).Procedure & ControlChars.NewLine)
                    Next i
                    Console.WriteLine(errMsg.ToString())
                    connection.Close()
                End Try

            End Using
        End Sub
    End Module

    4. Replace (local) with the name of your SQL Server.
    5. On the Debug menu, click Start to run the application.


    More Information
    =============================
    Status
    ======
    This problem is planned to be corrected in the next major release of .NET Framework.

    References
    =============================
    The SqlDbType and DbType are linked. Therefore, setting the DbType changes the SqlDbType to a supporting SqlDbType. If SqlDbType is not specified explicitly then DbType.String will set SqlDbType to NVarChar as default.

    SqlDbType Enumeration SqlDbType NVarChar
    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.dbtype.aspx


     

    ------------------------------------------------------------

    Microsoft's Role: Microsoft does not offer formal support for the communities you'll find here. Instead, our role is to provide a platform for people who want to take advantage of the global community of Microsoft customers and product experts. Microsoft may monitor content to ensure the accuracy of the information you'll find, but any information provided by Microsoft staff is offered "AS IS" with no warranties, and no rights are conferred. You assume all risk for your use.



    Microsoft Online Community Support
    Friday, April 24, 2009 12:45 PM