Source: Microsoft Support

RAPID PUBLISHING ARTICLES PROVIDE INFORMATION DIRECTLY FROM WITHIN THE MICROSOFT SUPPORT ORGANIZATION. THE INFORMATION CONTAINED HEREIN IS CREATED IN RESPONSE TO EMERGING OR UNIQUE TOPICS, OR IS INTENDED SUPPLEMENT OTHER KNOWLEDGE BASE INFORMATION.

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



    A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

    Source: .Net SqlClient Data Provider



The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter <n> ("@<ParameterName>"): Data type 0xE7 has an invalid data length or metadata length.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)



   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)



   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)



   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)



   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)



   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)



   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)



   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

  

This error will not happen if you specify any value that is outside the range of 4001 to 8000 for Sqlparameter.size.

 

Status: Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section. This is scheduled to be addressed in the next major release of .NET Framework

To work around this issue, use one of the following options:



·          Set Sqlparamter.size property to -1 to ensure that you are getting the entire data from the backend without truncation.

·          When working with String DbTypes whose sizes are greater than 4000, explicitly map them to another SqlDBType like NText instead of using NVarchar(which also is the default SqlDBType for strings).

·          Use a value that is not between 4001 and 8000 for Sqlparameter.size.

Steps to reproduce this problem:

1. Create a table on the target SQL Server using the following statement:

 

create table test (c1 nvarchar(max))

 

2. Create a Microsoft Visual Basic.Net Console Application that contains the following code:

Note: Before you run the code, replace the DBServer and DBName variables with the correct names of your computer that is running SQL Server and your database.

 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()

        Dim DBServer As String

        Dim DBName As String

 

        DBServer = " SQLServerName"

        DBName = " DatabaseName "

 

 

        '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 =" + DBServer + ";Integrated Security=True;Initial Catalog=" + DBName

 

        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

param = command.CreateParameter

            With param

                .ParameterName = "ParameterName"

                .DbType = DbType.String

'Uncomment the next line for implemnting Workaround #2 that is discussed above.

                '.SqlDbType = SqlDbType.NText

              

                .Size = retVal

            End With

            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

 

 

 

3. Build and then run the Visual Basic.Net Console Application. You receive the error messages that are mentioned in the "Symptoms" section when you specify a value that is between 4001 and 8000 for SqlParameter.Size. If you specify a value that is outside of that range or -1, the application does not report an error message.

 

References:

(http://msdn.microsoft.com/en-us/library/bb399384.aspx) Modifying Large-Value (max) Data (ADO.NET) (http://msdn.microsoft.com/en-us/library/bb399384.aspx)

MICROSOFT AND/OR ITS SUPPLIERS MAKE NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY, RELIABILITY OR ACCURACY OF THE INFORMATION CONTAINED IN THE DOCUMENTS AND RELATED GRAPHICS PUBLISHED ON THIS WEBSITE (THE “MATERIALS”) FOR ANY PURPOSE. THE MATERIALS MAY INCLUDE TECHNICAL INACCURACIES OR TYPOGRAPHICAL ERRORS AND MAY BE REVISED AT ANY TIME WITHOUT NOTICE.

TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, MICROSOFT AND/OR ITS SUPPLIERS DISCLAIM AND EXCLUDE ALL REPRESENTATIONS, WARRANTIES, AND CONDITIONS WHETHER EXPRESS, IMPLIED OR STATUTORY, INCLUDING BUT NOT LIMITED TO REPRESENTATIONS, WARRANTIES, OR CONDITIONS OF TITLE, NON INFRINGEMENT, SATISFACTORY CONDITION OR QUALITY, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, WITH RESPECT TO THE MATERIALS.