TSQL throwing arithmetic overflow when using parameters, but not without parameters?

Question

I am trying to save certain records that have an undetermined amount of decimals but never bigger than 4 or 10. I recently moved from MySQL to SQL Azure and I am running in a problem with my code; it is throwing a arithmetic overflow but I cannot figure out where. (console application, .NET 4.6)

cmd.CommandText = "INSERT INTO [crypto].[ticker" + Toggles.tablePostFix + "] ([pair], [ask_price], [ask_volume], [aks_wholelotvolume], [bid_price], [bid_volume], " +
                  "[bid_wholelotvolume], [lasttrade_price], [lasttrade_volume], [volume_today], [volume_24h], [trades_today], [trades_24h], " +
                  "[VWAP_today], [VWAP_24h], [open], [low_today], [low_24h], [high_today], [high_24h], [serverTime], [base_currency], [quote_currency]) " +
                  "VALUES (N'" +
                        ticks.Key + "', " +
                        "CAST(@ask_price AS Decimal(15, 10)), " +
                        "CAST(@ask_volume AS Decimal(10, 4)), " +
                        "CAST(@aks_wholelotvolume AS Decimal(10, 4)), " +

                        "CAST(@bid_price AS Decimal(15, 10)), " +
                        "CAST(@bid_volume AS Decimal(10, 4)), " +
                        "CAST(@bid_wholelotvolume AS Decimal(10, 4)), " +

                        "CAST(@lasttrade_price AS Decimal(15, 10)), " +
                        "CAST(@lasttrade_volume AS Decimal(10, 4)), " +
                        "CAST(@volume_today AS Decimal(10, 4)), " +

                        "CAST(@volume_24h AS Decimal(10, 4)), " +
                        "@trades_today, " +
                        "@trades_24h, " +
                        "CAST(@VWAP_today AS Decimal(15, 10)), " +
                        "CAST(@VWAP_24h AS Decimal(15, 10)), " +
                        "CAST(@open AS Decimal(15, 10)), " +
                        "CAST(@low_today AS Decimal(15, 10)), " +
                        "CAST(@low_24h AS Decimal(15, 10)), " +
                        "CAST(@high_today AS Decimal(15, 10)), " +
                        "CAST(@high_24h AS Decimal(15, 10)), " +
                        //"@serverTime, " +
                        //"@base_currency, " +
                        //"@quote_currency);";
                        "N'" + servertijd.ToString() + "', N'" + basecur + "', N'" + quotecur + "');";

cmd.Prepare();

//cmd.Parameters.AddWithValue("@pair", ticks.Key);
cmd.Parameters.AddWithValue("@ask_price", values.Ask[0]);
cmd.Parameters.AddWithValue("@ask_volume", values.Ask[1]);
cmd.Parameters.AddWithValue("@aks_wholelotvolume", values.Ask[2]);
cmd.Parameters.AddWithValue("@bid_price", values.Bid[0]);
cmd.Parameters.AddWithValue("@bid_volume", values.Bid[1]);
cmd.Parameters.AddWithValue("@bid_wholelotvolume", values.Bid[2]);
cmd.Parameters.AddWithValue("@lasttrade_price", values.Closed[0]);
cmd.Parameters.AddWithValue("@lasttrade_volume", values.Closed[1]);
cmd.Parameters.AddWithValue("@volume_today", values.Volume[0]);
cmd.Parameters.AddWithValue("@volume_24h", values.Volume[1]);
cmd.Parameters.AddWithValue("@trades_today", values.Trades[0]);
cmd.Parameters.AddWithValue("@trades_24h", values.Trades[1]);
cmd.Parameters.AddWithValue("@VWAP_today", values.VWAP[0]);
cmd.Parameters.AddWithValue("@VWAP_24h", values.VWAP[1]);
cmd.Parameters.AddWithValue("@open", values.Open);
cmd.Parameters.AddWithValue("@low_today", values.Low[0]);
cmd.Parameters.AddWithValue("@low_24h", values.Low[1]);
cmd.Parameters.AddWithValue("@high_today", values.High[0]);
cmd.Parameters.AddWithValue("@high_24h", values.High[1]);
//cmd.Parameters.AddWithValue("@serverTime", servertijd.ToString());
//cmd.Parameters.AddWithValue("@base_currency", basecur);
//cmd.Parameters.AddWithValue("@quote_currency", quotecur);

string tekst = cmd.CommandText;

for(int i = 0; i < cmd.Parameters.Count; i++)
{
    tekst = tekst.Replace(cmd.Parameters[i].ParameterName, cmd.Parameters[i].Value.ToString());
}

Debug.WriteLine(tekst);
cmd.ExecuteNonQuery();

I used to insert everything with parameters but I have changed that for the non-numeric values ([pair], [base_currency], [serverTime] and [quote_currency]). Reason for that was the lower bit of the code, where I use a for loop to create a SQL statement that I can test myself.

Here comes the kicker; when I debug this and I copy over string 'tekst', I get this;

INSERT INTO [crypto].[tickertest] ([pair], [ask_price], [ask_volume], [aks_wholelotvolume], [bid_price], [bid_volume], [bid_wholelotvolume], [lasttrade_price], [lasttrade_volume], [volume_today], [volume_24h], [trades_today], [trades_24h], [VWAP_today], [VWAP_24h], [open], [low_today], [low_24h], [high_today], [high_24h], [serverTime], [base_currency], [quote_currency]) 
VALUES (N'BCHEUR', CAST(359.600000 AS Decimal(15, 10)), CAST(114 AS Decimal(10, 4)), CAST(114.000 AS Decimal(10, 4)), CAST(359.300000 AS Decimal(15, 10)), CAST(1 AS Decimal(10, 4)), CAST(1.000 AS Decimal(10, 4)), CAST(359.600000 AS Decimal(15, 10)), CAST(4.11400000 AS Decimal(10, 4)), CAST(1474.09361766 AS Decimal(10, 4)), CAST(4159.61657024 AS Decimal(10, 4)), 913, 3206, CAST(356.680012 AS Decimal(15, 10)), CAST(362.558505 AS Decimal(15, 10)), CAST(376.000000 AS Decimal(15, 10)), CAST(344.500000 AS Decimal(15, 10)), CAST(344.500000 AS Decimal(15, 10)), CAST(377.200000 AS Decimal(15, 10)), CAST(382.400000 AS Decimal(15, 10)), N'9/17/2017 10:36:51 AM', N'BCH', N'EUR');

And that just works? SO, I am at a lose here, why is it working when not using parameters but is it throwing this arithmetic overflow when I use the parameters?

PS. all decimal columns are of the type DECIMAL(x, y) as per the cast statements (so either decimal(10, 4) or decimal (15, 10).

Full error:

Arithmetic overflow error converting numeric to data type numeric. The statement has been terminated.at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at COnsolePrutsApp.Program.SaveTicker(Dictionary2 ticker, Int32 serverTime, Dictionary2 assetpairs, Dictionary`2 assetinfo) in C:\Users\jarro\OneDrive\Documents\Visual Studio 2017\Projects\COnsolePrutsApp\COnsolePrutsApp\Program.cs:line 163

Line 163 is

cmd.ExecuteNonQuery()

EDIT

I also tried changing the AddWithValue to this:

cmd.Parameters.Add("@ask_price", SqlDbType.Decimal).Value = values.Ask[0];

I have double checked that I am putting in decimals and int's (the latter only for @trades_today and @trades_24h). Still same error though. (came onto the idea from here: SO question on the matter

EDIT 2 I found it; but it seems strange this didn't happen in MySQL; there was indeed a value that was not adhering to decimal(10,4) (the actual value was something like 3425343.0045) because it had one to many numbers on the left hand of the decimal (7 where the max was 6). I have upgraded all my columns to Decimal(20,10) just to get rid of this annoyance, and now it works :)

Use sql server profiler and capture what exact query is being executed on server. That would help you to figure out what's wrong with the query or parameters. — Chetan
Do you know if it's a single parameter that's causing the problem? — Zohar Peled
I am using Azure SQL and when I try to add a new trace in SQL Server Profiler it tells me I am not a member of the SYSADMIN group... Is there another way I can check what is going wrong? Can't find anything in Azure Portal either. — JarroVGIT
@ZoharPeled: no, is there anyway I can find that out? I've looked in the SQLException object but nowhere is any mention of the column in question :( — JarroVGIT

Stefanos Zilellis Stefanos Zilellis · Accepted Answer · 2017-09-17T12:37:50

Give a try with:

SELECT CONVERT(DECIMAL(38,20),34.654) a, CONVERT(DECIMAL(38,20),34.654) b

Then give a try with:

SELECT CONVERT(DECIMAL(15,10),CONVERT(DECIMAL(15,10),34.654)) a, CONVERT(DECIMAL(15,4),CONVERT(DECIMAL(15,4),34.654)) b

The first should work (unless there is no real number data). If the second does not work (and the first does) then you need should have numbers with higher precision. Also: if your source data is not numeric or similar data type but e.g. varchar, make sure you have the proper character as decimal separator.

TSQL throwing arithmetic overflow when using parameters, but not without parameters?

1 Answers