Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
scale greater than precision - not a valid instance of data type real

Answered scale greater than precision - not a valid instance of data type real

  • Tuesday, February 14, 2006 3:52 PM
     
     
    Sign In to Vote
    0

    Our shop recently upgraded to MS SQL 2005 server from the prior SQL 2000 product.

    I receive and error during processing related to inserting a value into a field of datatype real.  This worked for years under MS SQL 2000 and now this code throws an exception.

    The exception states:

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

    This error is caused by inserting several values that fall outside of a range that MS SQL 2005 documentation specifies.

    The first value that fails is 6.61242e-039.  SQL Server 2005 documentation seems to indicate that values for the datatype real must be - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38.

    Why doesn't 6.61242e-039 just default to 0 like it used to? 

    I saw an article that might apply, even though I just use a C++ float type and use some ATL templates.

    Is my question related to this post?http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=201636&SiteID=1

     

     

All Replies

  • Tuesday, February 14, 2006 11:05 PM
     
     
    Sign In to Vote
    0
    Yes, this is due to the new RPC parameter validations as explained in the other post. You need to ensure that the values fall within the domain of the SQL Server type. The problem with defaulting to 0 or NULL or error out may depend on your business needs. In older versions it was possible to populate the column for example with a value that you could not manipulate in TSQL also. So there were several issues with the absence of such checks in the engine.
     
  • Wednesday, February 15, 2006 2:40 PM
     
     
    Sign In to Vote
    0

    Shouldn't the Numeric Round-Abort property be able to stop this error?  By default the Numeric Round-Abort property is set to false.  Perhaps the Numeric Round-Abort property needs to be set at the connection string level to override a connection string default of true?

    There is inconsistency in accepting a number like 6.61242e-39

    1.  In MSSQL 2000 you can insert this number via code (ATL/OLEDB, rowset.SetValue(ord, fData))), SQL Server UI, or via an INSERT statement.

    2.  In MSSQL 2005 you can insert this number via an INSERT statement.  You cannot insert it via the SQL Server UI or via code (ATL/OLEDB). 

    This seems like a bug...

     
    Numeric Round-Abort

    Specify how the database handles rounding errors. Possible values are True and False. When True, an error is generated when loss of precision occurs in an expression. When False, losses of precision do not generate error messages, and the result is rounded to the precision of the column or variable storing the result.

     
  • Thursday, February 16, 2006 12:17 AM
     
     
    Sign In to Vote
    0
    Could you please post the sample code that demonstrates this problem? And also which SQL Server UI are you talking about? Is this via the open table in SSMS? This will help me narrow down the problem and/or report it internally.
     
  • Thursday, February 16, 2006 2:49 PM
     
     
    Sign In to Vote
    0

    Example 1:

    The UI I refer to in the previous post is the Microsoft SQL Server Management Studio (2005 edition).

    To recreate the problem open or create a table that has a field of datatype real.  Then, using the mouse, right click on the table in the tree control and choose "open table" from the pop-up menu.  You should now be presented with a grid control where you can manually enter in values.  Enter in the value 6.61242e-39 into the field of datatype real.  Assuming you have completed the other fields, move off of that record and SQL will attempt to commit the record.  At this time you will receive a dialog with an error message stating something like the following:

     

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

     

    This is the exact same message I get when I retrieve the error from my C++ code upon record insertion failure.

     

    Example 2:

    Using the SQL Server 2000 product, and the similar manual entry mechanisms from Example 1, enter in the same value (6.61242e-39) .  This product accepts the value.

     

    Example 3:

    The INSERT command works to insert this value in either the 2005 or 2000 product with no error given.  The example SQL insert command assumes a table called “Table_1” with two fields, a field called “col1” of type integer, and a “col2” of type real.

     

    insert  Table_1 (col1, col2) values (1, 6.61242e-39)

     

    Example 4:

    As far as code goes, we use an OLEDB wrapper that we have used for years.

    Any SetValue() type insertion mechanism should trigger the same error.  If it is critical, doctoring up some C# code will almost certainly produce the same problem.  I know I’m avoiding the code, but the simple example will take much time in my case and I would have to provide the OLEDB wrapper we use.  The UI for the SQL 2005 product probably uses the exact same calls underneath and thus has the same problem.  If the error is fixed from the UI I am convinced that my error will be resolved as well.

     
  • Friday, July 07, 2006 4:28 PM
     
     
    Sign In to Vote
    0

    What the hell is wrong with MS people. Someone from MS asked for code sample to address the issue and when the original poster supplied all information, this guy is not replying at all.

    I am also facing the same problem while calling a stored procedure from 'Execute SQL Task' in SSIS package. I am assigning varchar value to the OUTPUT parameter in the SP and using that Output parameter to update a valiable.

    Is there anyone else apart from MS who faced the same issue and resolved it. Please reply.

    Thanks!

     
  • Saturday, July 08, 2006 12:54 AM
     
     
    Sign In to Vote
    0
    Sorry, I seem to have missed this thread before somehow. The change to the RPC parameter values validation in SQL Server 2005 is by design. So there is no fix to change this behavior. The validation helps to ensure that only clean data that can be manipulated on the server or valid is stored in database tables. Before SQL server 2005, it was possible to insert corrupted values or values that are beyond the domain of certain types. These cases will not fail for RPC calls and bulk insert operations for example. The only option is to modify the application to validate the data so it matches the domain of each SQL Server type. In your example, it looks like the data which is being passed by SSIS or fed to SSIS is invalid for SQL Server. So please check the ranges of the data type in Books Online.
     
    In the previous post, the behavior as observed from diffferent mechanisms is as a result of the server change and how the client makes the call to the server (RPC or direct exec). Hope this helps.
     
  • Saturday, July 08, 2006 5:47 AM
     
     
    Sign In to Vote
    0

    Thanks for replying atleast. Now, let me first explain what all I am doing.

    I have an "Execute SQL Script" task which I configured to execute a stored procedure. In the sp, there is one output parameter of type varchar(10). After completing all required processing, I assign ' ' (space) to the output parameter. The SSIS task is configured to assign the output parameter value to a package variable. The package variable is of type String.

    Its pretty simple. Here, I am not inserting any data in any table in SQL Server. So, there is no point of exceeding the range of a datatype.

    Also, in this post, some one from microsoft has already confirmed that this is a known issue and suggested a wiered solution to use Oledb insteed of SQL Client.

    Now I am really confused if this is a feature or bug in SQL Server.

    Please suggest.

     
  • Saturday, July 08, 2006 7:03 PM
     
     Answered
    Sign In to Vote
    0
    Ok. Let me try to explain again. The validations doesn't have to anything to do with what the SP or statement is actually doing. The validations happen against the parameter values being passed - it verifies length of the data vs length of the parameter, base type check in case of sql_variant, range of values and so on.
     
    In case of your issue, it looks like the SSIS SQL Task is doing something wrong when initializing the RPC parameters. The bug is not a server related one. I searched through the bug database and found an issue related to SQL Task where the length of the parameter is set incorrectly by SSIS which results in this error due to new validations on the server. And bug seems to be related to use of ADO.NET connection type in SQL Task only. So if you follow the workaround as suggested before then the task will run fine. As for whether the fix will be included in a service pack or future release, please follow up in the original thread in the SSIS forum. I do not have visibility into the SSIS bug fixes and plans for service packs.
     
  • Saturday, July 08, 2006 7:07 PM
     
     
    Sign In to Vote
    0
    Fine. Thanks for your time!