none
Error truncating data after upgrade to SQL Server 2005 RRS feed

  • Question

  •  

    We are testing a DotNet web application against a newly upgraded SQL Server 2005 SP2 database.

     

    Certain code that is working against the SQL Server 2000 database is failing against SQL Server 2005.

     

    The issue is that certain stored procedures are being called from the Web Application and where some parameters are declared as Numeric(18,4) the front-end application is passing more than 4 decimal places. This is causing an error

     

    An example is:

     

    Message: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 6 ("@PalletForecast"): 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.

    Number: 8023

     

    The numeric round-abort option on the database is set to false and also when the procedure is called directly from SSMS the automatic truncation is occuring . Only when the procedure is called via the DotNet layer does this error occur.

     

    I realise that the best option is to pass only the declared precision but this is not  possible at this time.

     

    Does anyone have any suggestions on how to overcome this "upgrade benifit" in order to allow the ral additional benifits of SQL Server 2005 to be used

     

    TIA

     

    Nadreck

     

     

    Tuesday, September 18, 2007 9:03 AM

All replies

  • What data access API are you using? Since you mentioned DotNet, is this SqlClient?

     

    Wednesday, September 19, 2007 10:04 AM
    Answerer
  • Connectivity is via SQLClient.

    The error is a System.Data.SqlClient.SqlException error (No 8023)

     

    After further research I believe that this may relate to RPC parameter validations introduced in SQL Server 2005

     

    The issue has been discussed for overflow/underflow values failing in 2005 but I have not seen discussion of the issue in respect of declared accuracy for float/numeric values

    eg

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=249505&SiteID=1

     

    It does seem that this is a deliberate decision and that there is no work around.

    Thursday, September 20, 2007 1:14 AM
  • We have an SQL Server 2005 and a database running in SQL 2000 mode.

    When we run our applicaton on the server we get the same error, but when we connect to the server from the development machine on which SQL 2000 client is installed, we do not get the specified error.

     

    So it looks like the validation is not on the server side, which makes me think can I install SQL 2000 client on the SQL 2005 server and workaround this problem.

    Friday, October 19, 2007 1:10 PM