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
scale greater than precision - not a valid instance of data type real
vpsdhillon
TheBouff
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.
Amorn Vikrompanitkul
Randaldtb
Mike Tupker
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...
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.
Fabrizio 868
Norsek
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!
weisun
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.
braverwon