2100 parameter limit

[limskii]

I am getting this error microsoft.sqlserver.jdbc.SQLServerException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.

im new to coding stuffs, i know that i am passing morethan 2100 parameter that is why the error is encountered.

Can someone help me to find better solution or approach so that i can resolve the error?

Thank you very much.

Here is the actual code.

Public Sub New(ByRef messages As List(Of SomeMessage))

            Dim count As Integer = 0
            Dim sb As StringBuilder = New StringBuilder()

            sb.AppendLine(" INSERT INTO Table1 ")
            sb.AppendLine("            (Id ")
            sb.AppendLine("            ,UniqueNbr ")
            sb.AppendLine("            ,ClockId ")
            sb.AppendLine("            ,TypeNmber")
            sb.AppendLine("            ,CurrencyAmt ")
            sb.AppendLine("            ,CurrencyCd ")
            sb.AppendLine("            ,MemberId ")
            sb.AppendLine("            ,CreateDttm ")
            sb.AppendLine("            ,UpdateMemberId ")
            sb.AppendLine("            ,UpdateDttm) ")

            For Each abc As SomeMessage In messages

                count += 1
                sb.AppendLine("      Select     ")

                sb.AppendLine("            @Id" + count.ToString())
                sb.AppendLine("            ,@UniqueNbr " + count.ToString())
                sb.AppendLine("            ,@ClockId " + count.ToString())
                sb.AppendLine("            ,@TypeNmber" + count.ToString())
                sb.AppendLine("            ,@CurrencyAmt" + count.ToString())
                sb.AppendLine("            ,@CurrencyCd" + count.ToString())
                sb.AppendLine("            ,@MemberId " + count.ToString())
                sb.AppendLine("            ,@CreateDttm" + count.ToString())
                sb.AppendLine("            ,@UpdateMemberId " + count.ToString())
                sb.AppendLine("            ,@UpdateDttm" + count.ToString())
                sb.AppendLine("      UNION ALL")

                Me.Parameters.Add(Me.CreateParameter("@Id" + count.ToString(), DbType.Int32, abc.Id, ParameterDirection.Input))
                Me.Parameters.Add(Me.CreateParameter("@UniqueNbr " + count.ToString(), DbType.Int32, abc.UniqueNbr, ParameterDirection.Input))
                Me.Parameters.Add(Me.CreateParameter("@ClockId" + count.ToString(), DbType.Int32, abc.ClockId, ParameterDirection.Input))
                Me.Parameters.Add(Me.CreateParameter("@TypeNmber" + count.ToString(), DbType.Int32, abc.TypeNmber, ParameterDirection.Input))
                Me.Parameters.Add(Me.CreateParameter("@CurrencyAmt" + count.ToString(), DbType.Decimal, abc.CurrencyAmt, ParameterDirection.Input))
                Me.Parameters.Add(Me.CreateParameter("@CurrencyCd" + count.ToString(), DbType.String, abc.CurrencyCd, ParameterDirection.Input))
                Me.Parameters.Add(Me.CreateParameter("@MemberId" + count.ToString(), DbType.String, abc.MemberId, ParameterDirection.Input))
                Me.Parameters.Add(Me.CreateParameter("@CreateDttm" + count.ToString(), DbType.Date, abc.CreateDttm, ParameterDirection.Input))
                Me.Parameters.Add(Me.CreateParameter("@UpdateMemberrId" + count.ToString(), DbType.String, abc.UpdateMemberrId, ParameterDirection.Input))
                Me.Parameters.Add(Me.CreateParameter("@UpdateDttm" + count.ToString(), DbType.Date, abc.UpdateDttm, ParameterDirection.Input))

            Next

            Me.Sql = sb.ToString().Trim().TrimEnd("UNION ALL".ToCharArray())
            Me.KeyParameter = "@TabletCompletionId"

        End Sub

[debasisdas]

2100 parameter !

for what ?

[limskii]

2100 parameter !

for what ?

The object in the for each loop (abc) is 319 and each on is passing to the below code and generate 3190 parameters.

Me.Parameters.Add(Me.CreateParameter("@ForecastVersionId" + count.ToString(), DbType.Int32, eac.ForecastVersionId, ParameterDirection.Input))
                Me.Parameters.Add(Me.CreateParameter("@WBSInternalNbr" + count.ToString(), DbType.Int32, eac.WBSInternalNbr, ParameterDirection.Input))
                Me.Parameters.Add(Me.CreateParameter("@TimeId" + count.ToString(), DbType.Int32, eac.TimeId, ParameterDirection.Input))
                Me.Parameters.Add(Me.CreateParameter("@ElementNbr" + count.ToString(), DbType.Int32, eac.ElementNbr, ParameterDirection.Input))
                Me.Parameters.Add(Me.CreateParameter("@ObjectCurrencyAmt" + count.ToString(), DbType.Decimal, eac.ObjectCurrencyAmt, ParameterDirection.Input))
                Me.Parameters.Add(Me.CreateParameter("@ObjectCurrencyCd" + count.ToString(), DbType.String, eac.ObjectCurrencyCd, ParameterDirection.Input))
                Me.Parameters.Add(Me.CreateParameter("@CreateUserId" + count.ToString(), DbType.String, eac.CreateUserId, ParameterDirection.Input))
                Me.Parameters.Add(Me.CreateParameter("@CreateDttm" + count.ToString(), DbType.Date, eac.CreateDttm, ParameterDirection.Input))
                Me.Parameters.Add(Me.CreateParameter("@UpdateUserId" + count.ToString(), DbType.String, eac.UpdateUserId, ParameterDirection.Input))
                Me.Parameters.Add(Me.CreateParameter("@UpdateDttm" + count.ToString(), DbType.Date, eac.UpdateDttm, ParameterDirection.Input))

[debasisdas]

This approach (with 3190 parameters) is impractical.

You need to try some other method to achieve the same.

[hericles]

You can't break it into manageable chunks instead of dealing with the whole thing at once?

[limskii]

You can't break it into manageable chunks instead of dealing with the whole thing at once?

Is there any one know how can i convert this into an xml? or bulk insert?

[adam_k]

Where are you reading data from ?