You receive a "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect" exception when using NVarchar parameters with Sqlclient

Source: Microsoft Support

RAPID PUBLISHING

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.

Symptom



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.


 

Cause



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

Resolution



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.

More Information



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:

Modifying Large-Value (max) Data (ADO.NET)

DISCLAIMER

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.
Properties

Article ID: 970519 - Last Review: Apr 23, 2009 - Revision: 1

Feedback