none
The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect

    Question

  • I've read the other posts related to this issue, but I'm just REALLY confused as to whats happening in my case.  Like everyone else it was working fine in SQL 2000 but now in SQL 2005 there is an issue. I'm calling a stored procedure with parameters defined like this:

    @action varchar(10),
    @GLTransactionID int = NULL OUTPUT ,
    @GLBatchID int = NULL ,
    @GLAccountID int = NULL ,
    @CurrencyID int = NULL ,
    @LocalDebit decimal(28, 13) = NULL ,
    @LocalCredit decimal(28, 13) = NULL ,
    @BaseDebit decimal(28, 13) = NULL ,
    @BaseCredit decimal(28, 13) = NULL ,
    @TransID int =NULL,
    @Description varchar(255) = NULL 

    I am calling this proc from VS.NET 2003 using the .Net SqlClient Data Povider (C#).  I'm setting the values of the parameters like this:

    cm.Parameters.Add("@action", "insert");
    cm.Parameters.Add("@GLBatchID", _gLBatchID.DBValue);
    cm.Parameters.Add("@GLAccountID", _gLAccountID.DBValue);
    cm.Parameters.Add("@CurrencyID", _currencyID.DBValue);
    cm.Parameters.Add("@LocalDebit", _localDebit.DBValue);
    cm.Parameters.Add("@LocalCredit", _localCredit.DBValue);
    cm.Parameters.Add("@BaseDebit", _baseDebit.DBValue);
    cm.Parameters.Add("@BaseCredit", _baseCredit.DBValue);
    cm.Parameters.Add("@TransID", _transID.DBValue);
    cm.Parameters.Add("@Description", _description.DBValue);

    When I execute the call to the stored proc I get this:

    "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 8 (\"@BaseDebit\"): The supplied value is not a valid instance of data type numeric. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision."

    Using the VS.NET command window I then inspect that parameter to see what the heck is going on and get this:

    ?cm.Parameters["@BaseDebit"].SqlDbType
    Decimal
    ?cm.Parameters["@BaseDebit"].Precision
    0
    ?cm.Parameters["@BaseDebit"].Scale
    22
    ?cm.Parameters["@BaseDebit"].DbType
    Decimal
    ?cm.Parameters["@BaseDebit"].Value
    1000000
        [System.Decimal]: 1000000

    So I set a decmial parameter to 1,000,000, that parameter in the DB is defined as decimal(28,13) so should fit no problem, but it seems the Sql data provider is confused and thinks 1,000,000 is decimal (0,22)???

    Monday, May 22, 2006 6:20 PM

Answers

  • OK, I guess I found the issue and its strange but not a huge deal I guess.  Using the VS.NET command window it showed to value being passed as 1000000 with no decimal points ect.  So I'd assumed all was good.  After being completely confused I tossed some code in there to route all information to the console and got this.

    Param: 0 Key: @action Type: NVarChar Precision: 0 Scale: 0 Value: insert
    Param: 1 Key: @GLTransactionID Type: Int Precision: 0 Scale: 0 Value: -2147483648
    Param: 2 Key: @GLBatchID Type: Int Precision: 0 Scale: 0 Value: 48260
    Param: 3 Key: @GLAccountID Type: Int Precision: 0 Scale: 0 Value: 373
    Param: 4 Key: @CurrencyID Type: Int Precision: 0 Scale: 0 Value: 4
    Param: 5 Key: @LocalDebit Type: Decimal Precision: 0 Scale: 13 Value: 2000000.0000000000000
    Param: 6 Key: @LocalCredit Type: Decimal Precision: 0 Scale: 0 Value: 0
    Param: 7 Key: @BaseDebit Type: Decimal Precision: 0 Scale: 22 Value: 2000000.0000000000000000000000
    Param: 8 Key: @BaseCredit Type: Decimal Precision: 0 Scale: 0 Value: 0
    Param: 9 Key: @TransID Type: Int Precision: 0 Scale: 0 Value: 45396
    Param: 10 Key: @Description Type: NVarChar Precision: 0 Scale: 0 Value: Contribution Accrual

    Well, in that view it was obvious there were too many trailing zeros for the @BaseDebit parameter.  Not sure why in some cases it was having these extra zeros and others it wasn't, but luckily our app uses our own datatypes (which are wrappers around the based datatypes with a little extra functionality) so it just took a couple lines of code in that class to fix.  Still find it strange, but its working.

    Monday, May 22, 2006 9:01 PM

All replies

  • Reading my first post I realized I'd cut out the setting of the output parameter in my copy/paste.  If it makes a difference, here is the full code for settings all parameters.

    cm.Parameters.Add("@action", "insert");
    cm.Parameters.Add("@GLTransactionID", _gLTransactionID.Value);
    cm.Parameters["@GLTransactionID"].Direction = ParameterDirection.Output;
    cm.Parameters.Add("@GLBatchID", _gLBatchID.DBValue);
    cm.Parameters.Add("@GLAccountID", _gLAccountID.DBValue);
    cm.Parameters.Add("@CurrencyID", _currencyID.DBValue);
    cm.Parameters.Add("@LocalDebit", _localDebit.DBValue);
    cm.Parameters.Add("@LocalCredit", _localCredit.DBValue);
    cm.Parameters.Add("@BaseDebit", _baseDebit.DBValue);
    cm.Parameters.Add("@BaseCredit", _baseCredit.DBValue);
    cm.Parameters.Add("@TransID", _transID.DBValue);
    cm.Parameters.Add("@Description", _description.DBValue);
    cm.ExecuteNonQuery();

    Monday, May 22, 2006 6:23 PM
  • The error is due to new validations that we added for RPC parameters to make sure the data being passed is within the bounds of what the SQL engine supports. So you will have to correct the decimal value that you are sending frm the client such that it is within the supported range in TSQL. Please see the thread below for a related problem:
     
     
    Monday, May 22, 2006 8:13 PM
  • Thanks for the reply!

    I guess I might have gotten into too much detail so that it over-shadowed core question.  Why is a System.Decimal datatype with the value of 1,000,000 not valid to be passed to a parameter defined as decimal(28,13)?  I just cannot understand how that would be outside the bounds of the SQL's supported range.

    Am I just missing something?

    Monday, May 22, 2006 8:20 PM
  • OK, I guess I found the issue and its strange but not a huge deal I guess.  Using the VS.NET command window it showed to value being passed as 1000000 with no decimal points ect.  So I'd assumed all was good.  After being completely confused I tossed some code in there to route all information to the console and got this.

    Param: 0 Key: @action Type: NVarChar Precision: 0 Scale: 0 Value: insert
    Param: 1 Key: @GLTransactionID Type: Int Precision: 0 Scale: 0 Value: -2147483648
    Param: 2 Key: @GLBatchID Type: Int Precision: 0 Scale: 0 Value: 48260
    Param: 3 Key: @GLAccountID Type: Int Precision: 0 Scale: 0 Value: 373
    Param: 4 Key: @CurrencyID Type: Int Precision: 0 Scale: 0 Value: 4
    Param: 5 Key: @LocalDebit Type: Decimal Precision: 0 Scale: 13 Value: 2000000.0000000000000
    Param: 6 Key: @LocalCredit Type: Decimal Precision: 0 Scale: 0 Value: 0
    Param: 7 Key: @BaseDebit Type: Decimal Precision: 0 Scale: 22 Value: 2000000.0000000000000000000000
    Param: 8 Key: @BaseCredit Type: Decimal Precision: 0 Scale: 0 Value: 0
    Param: 9 Key: @TransID Type: Int Precision: 0 Scale: 0 Value: 45396
    Param: 10 Key: @Description Type: NVarChar Precision: 0 Scale: 0 Value: Contribution Accrual

    Well, in that view it was obvious there were too many trailing zeros for the @BaseDebit parameter.  Not sure why in some cases it was having these extra zeros and others it wasn't, but luckily our app uses our own datatypes (which are wrappers around the based datatypes with a little extra functionality) so it just took a couple lines of code in that class to fix.  Still find it strange, but its working.

    Monday, May 22, 2006 9:01 PM