SQL LocalDB - Fails exceeds the maximum supported precision
I get the following error message:
System.InvalidOperationException occurred
HResult=-2146233079
Message=Precision '39' required to send all values in column 'EntityUtilization' exceeds the maximum supported precision '38'. The values must all fit in a single precision.
Source=System.Data
StackTrace:
at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DbCommand command) in e:\Builds\EntLib\Latest\Source\Blocks\Data\Src\Data\Database.cs:line 436
InnerException:
This happens if I try to call a stored procedure that takes a table of user-defined-sql-type as parameter:
CREATE PROCEDURE [dbo].[DoMerge]
@MergeTable [dbo].EntitiesDynType READONLY
AS
BEGIN
SET NOCOUNT ON
END
The user-defined-sql-type has a column of type decimal(18.5):
CREATE TYPE [dbo].[EntitiesDynType] AS TABLE(
[EntityDynKey] [VARCHAR](150) NOT NULL,
[EntityUtilization] [DECIMAL](18, 5) NULL,
PRIMARY KEY CLUSTERED
(
[EntityDynKey] ASC
)WITH (IGNOREDUPKEY = OFF)
)
With 10 (or more) System.Data.DataRow where all contains "evil" decimals:
118.03407680028934172003533616 object {decimal}
238948079330.8403205431864835 object {decimal}
238948079330.8403205431864835 object {decimal}
238944985589.09876203547470873 object {decimal}
571.72751405677565973786807202 object {decimal}
571.72751405677565973786807202 object {decimal}
571.59808657336724380941582624 object {decimal}
3501.7662280391827072812438614 object {decimal}
8433.390354715490292009196923 object {decimal}
61.86492618854062442592262116 object {decimal}
If I round the decimals to 6th decimal, then the call to stored procedure succeeds.
If I take 1 row at a time, then the call to stored procedure succeeds.
AdminMicrosoft SQL Server
(Product Manager, Microsoft Azure)
shared this idea
Upvotes: 1
<=-=Aug 21 2014 1:19AM=-=>My guess is that the decimal values for a single column has to be within the same “range”. It probably tries to find a common decimal representation during query-serialization, and fails because they are uneven (Some very large, some with many decimals).
<=-=Aug 26 2014 2:02PM=-=>Thank you for your feedback for SQL Server. Unfortunately, I have not been able to reproduce the behavior you are reporting. If you could please respond with a small TSQ script or program which demonstrates the behavior it will help us to diagnose the behavior.
<=-=Aug 26 2014 2:02PM=-=>Thank you for your feedback for SQL Server. Unfortunately, I have not been able to reproduce the behavior you are reporting. If you could please respond with a small TSQL script or program which demonstrates the behavior it will help us to diagnose the behavior.
<=-=Sep 3 2014 6:21AM=-=>I have attached a small console application, that reproduces the error.
1 comment
-
Moshe
commented
I am seeing this exact issue, specifically in a table type even though there are no values that have a precision greater than whats sepecified in both the table type and the table.
Is there any clarity or resolution on this?