InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 12:38:34 GMT20http://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspxI didn't get any error. i got 0.00 i wonder whySun, 07 Nov 2010 16:59:13 GMTDhruvesh Shahhttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]Paul White NZ (5/7/2010)[/b][hr][quote][b]Michael Poppers (5/7/2010)[/b][hr]Ditto.Paul, isn't it worth noting the different "ThePrecision" results from the sql_variant_property() output noted earlier in this discussion? Would you like me to add the following code snippet as a comment to your Connect submission?[/quote]Thank you for your vote Michael - and yes, please feel free to add the comment :-)[/quote]Done. Thanks.Mon, 10 May 2010 11:09:32 GMTMichael Poppershttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]Michael Poppers (5/7/2010)[/b][hr]Ditto.Paul, isn't it worth noting the different "ThePrecision" results from the sql_variant_property() output noted earlier in this discussion? Would you like me to add the following code snippet as a comment to your Connect submission?[/quote]Thank you for your vote Michael - and yes, please feel free to add the comment :-)Fri, 07 May 2010 22:43:04 GMTSQL Kiwihttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]Christian Buettner-167247 (5/7/2010)[/b][hr][quote][b]Paul White NZ (5/6/2010)[/b][hr]Please vote and mark as reproducible if you can.[/quote]Done :)[/quote]Ditto.Paul, isn't it worth noting the different "ThePrecision" results from the sql_variant_property() output noted earlier in this discussion? Would you like me to add the following code snippet as a comment to your Connect submission? Thanks.[code="sql"]select sql_variant_property(cast(1.0 as sql_variant), 'BaseType') BaseType, sql_variant_property(cast(1.0 as sql_variant), 'Precision') ThePrecision, sql_variant_property(cast(1.0 as sql_variant), 'Scale') Scale;select sql_variant_property(cast(ROUND(0.5,0) as sql_variant), 'BaseType') BaseType, sql_variant_property(cast(ROUND(0.5,0) as sql_variant), 'Precision') ThePrecision, sql_variant_property(cast(ROUND(0.5,0) as sql_variant), 'Scale') Scale;[/code]Fri, 07 May 2010 10:15:34 GMTMichael Poppershttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]Paul White NZ (5/6/2010)[/b][hr]Please vote and mark as reproducible if you can.[/quote]Done :)Fri, 07 May 2010 00:54:18 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspxConnect Item added:[url]https://connect.microsoft.com/SQLServer/feedback/details/557523/automatic-type-assignment-causes-data-corruption[/url]Please vote and mark as reproducible if you can.Thanks to everyone that contributed to the discussion - especially Christian, Wayne, and Oleg.Thu, 06 May 2010 19:27:12 GMTSQL Kiwihttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspxWow. I didn't believe that the error message was coming from the client - I still figured it was one of the settings being missed somewhere, but I ran a profiler trace watching user errors and sure enough, the server didn't send an error back to the client. I never would have guessed that the client would provide an error when the server didn't. My apologizes for ever doubting!Thanks,ChadWed, 05 May 2010 11:21:48 GMT Chad Crawfordhttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]Paul White NZ (5/4/2010)[/b][hr]Thanks Wayne, that's very useful! My only remaining question is what versions of SQL Server (engine and SSMS) you are running. I guess they are similar to mine - and you have patched your SSMS as well as the server - since our results agree so well.isql eh? :blink:[/quote]Servers:SQL 2008: 10.0.1600.22SQL 2005: 9.00.4053.00SQL 2000: 8.00.2055SSMS 2008: 10.0.1600.22SSMS 2005: 9.00.4035.00 SQL Query Analyzer 8.00.2039I'll edit this to include SSMS 2005 later... have to get that from a co-worker.Wed, 05 May 2010 08:00:09 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]Christian Buettner-167247 (5/5/2010)[/b][hr]I have no idea what the following 4 digits ('0001') mean.[/quote]The second byte '01' is the sign. Can't think what the first byte is for :([quote]Paul, did you already (re-)open a connect item, or should I do that?[/quote]I think the issue is different enough to warrant a new item - I haven't started on it yet, but I intend to.I'm not expecting too much from Microsoft though: 2005 is close to the end of its life, and the problem is already fixed in 2008.Maybe they'll surprise me - they do seem to have been somewhat charitable recently.Wed, 05 May 2010 04:20:03 GMTSQL Kiwihttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspxSome more tests showing the bug "in action":[code="sql"]SELECT '0.5' [Expression], CAST(0.5 as varbinary) AS [Binary Representation]UNION ALL SELECT 'ROUND(0.5,0)', CAST(ROUND(0.5,0) as varbinary)UNION ALL SELECT '1.0', CAST(1.0 as varbinary)UNION ALL SELECT '0.0', CAST(0.0 as varbinary)[/code][code="plain"]Expression Binary Representation0.5 0x0101000105000000ROUND(0.5,0) 0x010100010A0000001.0 0x020100010A0000000.0 0x0101000100000000[/code]The binary representation of the rounded value shows the problem: A (10) does not fit into decimal(1,1). This is definitively a bug (as already shown by Paul with DBCC CHECKDB)Just some further explanation on the binary representation:The first 2 digits seem to be the precision, the second 2 digits the scale. I have no idea what the following 4 digits ('0001') meanThe rest seems to be the number without decimal point (in reverse byte order)Example:[code="plain"]1.0 0x020100010A000000[/code]Precision = 2, Scale = 1, Value = A (10)Since we have a scale of 1, the decimal point is inserted between the 1 and the 0:1.0So obviously the following is not a valid binary representation of the decimal datatype:[code="plain"]0x010100010A000000[/code]You cannot have a precision of 1 for the value A (10)You either need a precision of 2 for that, or you need to have the value 1 instead.Paul, did you already (re-)open a connect item, or should I do that?Wed, 05 May 2010 02:22:11 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspxThanks Wayne, that's very useful! My only remaining question is what versions of SQL Server (engine and SSMS) you are running. I guess they are similar to mine - and you have patched your SSMS as well as the server - since our results agree so well.isql eh? :blink:Tue, 04 May 2010 21:40:28 GMTSQL Kiwihttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]Paul White NZ (5/4/2010)[/b][hr]Results so far for [b]SELECT ROUND(0.5, 0)[/b] for me:[/quote]Paul, this question has turned into a very interesting topic.I also have isqlw / isql for SQL 2000 available, and a few SQL 2000 servers; here are my tests for running:SET ARITHABORT OFF;SET NUMERIC_ROUNDABORT OFF;PRINT ROUND(0.5,0);SELECT ROUND(0.5,0)for all clients on 2000/2005/2008 servers:isql:--> SQL 2008: PRINT: Arithmetic overflow occurred msg (no error #); SELECT: [b]NULL, + [/b]Arithmetic overflow occurred msg (no error #)--> SQL 2005: PRINT: 1.0; SELECT: 1.0--> SQL 2000: PRINT: 1.0; SELECT: 1.0osql:--> SQL 2008: PRINT: Error 8115; SELECT: Error 8115--> SQL 2005: PRINT: 1.0; SELECT 1.0--> SQL 2000: PRINT: 1.0; SELECT 1.0sqlcmd:--> SQL 2008: PRINT: Error 8115; SELECT: Error 8115--> SQL 2005: PRINT: 1.0; SELECT .0--> SQL 2000: PRINT: 1.0; SELECT .0isqlw:--> SQL 2008: PRINT: Error 8115; SELECT: Error 8115--> SQL 2005: PRINT: 1.0; SELECT 1.0--> SQL 2000: PRINT: 1.0; SELECT 1.0SSMS (2005 & 2008):--> SQL 2008: PRINT: Error 8115; SELECT: Error 8115 [b]& returns an empty result set[/b]--> SQL 2005: PRINT: 1.0; SELECT Arithmetic overflow error msg (no error #)--> SQL 2000: PRINT: 1.0; SELECT Arithmetic overflow error msg (no error #)Edit: removed excess quotes, bolded text.Tue, 04 May 2010 13:28:14 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]Oleg Netchaev (5/4/2010)[/b][hr]So, sql_variant_property correctly opts for decimal base type, 10 precision and 0 scale rather than bigint base type which would yield 19 precision and 0 scale:[/quote]Hey Oleg, and thanks - it's been a fun night here :-DAbout the data type - I don't think you can credit sql_variant_property - it's those mysterious rules SQL Server uses for assigning a type to a literal. The literal gets a type before being cast to sql_variant. The sql_variant just contains the assigned type. That's my take on it anyway!Tue, 04 May 2010 12:05:43 GMTSQL Kiwihttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspxVery good question, thank you Paul.It is interesting to see how perfectly sql_variant_property handles the literals. For example, 3 billion can be represented as bigint or as decimal(10, 0), and the latter is definitely cheaper:[code="sql"]declare @bi bigint;declare @dc decimal(10, 0);select @bi = 3000000000, @dc = @bi;select datalength(@bi) bigint_length, datalength(@dc) decimal_length;[/code]The above returns[code="sql"]bigint_length decimal_length------------- --------------8 5[/code]So, sql_variant_property correctly opts for decimal base type, 10 precision and 0 scale rather than bigint base type which would yield 19 precision and 0 scale:[code="sql"]select sql_variant_property(cast(3000000000 as sql_variant), 'BaseType') BaseType, sql_variant_property(cast(3000000000 as sql_variant), 'Precision') ThePrecision, sql_variant_property(cast(3000000000 as sql_variant), 'Scale') Scale;[/code]This returns [code="sql"]BaseType ThePrecision Scale---------- ---------- ----------numeric 10 0[/code]OlegTue, 04 May 2010 10:21:12 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspxfrom within the Query Analyzer (SQL 2000 SP4) I got the resultant 1.0 as well.just my $0.02.MTue, 04 May 2010 09:58:27 GMTmark.pleasancehttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspxWow...Who would have thought the QOD would have unveiled a bug and produced great discussion. Congrats to all who have contributed to this discussion!!Tue, 04 May 2010 08:49:42 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]Steve Jones - Editor (5/4/2010)[/b][hr]It's an interesting question. Good to see there's a Connect item on it, and people should have learned things.[/quote]I have..for one :-)[quote]Not sure how I feel about the question. Definitely if the server handles it, the client should.[/quote]I think there's another lesson here - patch your client tools as well as the server.I wonder how many people will be surprised by the client tools version returned by Help...About in SSMS.The situation with SQL Server 2005 is very odd.The server seems happy to process SELECT ROUND(0.5, 0) but assigns invalid metadata:[code="sql"]SELECT a = ROUND(0.5,0) INTO #a;SELECT a FROM #a;[/code]The first statement succeeds, but the data type of column a in #a is numeric(1,1)! :w00t:DBCC CHECKDB on tempdb returns:[code="plain"][color="#FF0000"]Msg 2570, Level 16, State 3, Line 1Page (1:2373), slot 0 in object ID 258099960, index ID 0, partition ID 72057594068271104, alloc unit ID 72057594073382912(type "In-row data"). Column "a" value is out of range for data type "numeric".Update column to a legal value.[/color][/code]Tue, 04 May 2010 08:44:11 GMTSQL Kiwihttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspxIt's an interesting question. Good to see there's a Connect item on it, and people should have learned things.Not sure how I feel about the question. Definitely if the server handles it, the client should.Tue, 04 May 2010 08:24:14 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]webrunner (5/4/2010)[/b][hr]If it turns out that the error is only in some clients, does that mean that those of us who got the question wrong by picking 1 can get ROUND(0.5,0) points for it?[/quote]Superb. Well done, sir! :laugh:It definitely is client-specific, but also server-specific :w00t:Results so far for [b]SELECT ROUND(0.5, 0)[/b] for me:osql client to 2005 server = 1.0 (correct!)SQLCMD client to 2005 server = .0 (wrong!)SSMS 2005 to 2005 server = An error occurred while executing batch. Error message is: Arithmetic Overflow.SSMS 2008 to 2005 server = An error occurred while executing batch. Error message is: Arithmetic Overflow.osql client to 2008 server = Error 8115: Arithmetic overflow error converting expression to data type numeric.SQLCMD client to 2008 server = Error 8115: Arithmetic overflow error converting expression to data type numeric.SSMS 2008 to 2008 server = Error 8115: Arithmetic overflow error converting expression to data type numeric.Results so far for [b]PRINT ROUND(0.5, 0)[/b] for me:osql to 2005: 1.0 (correct!)SQLCMD to 2005: 1.0 (correct!)SSMS 2005 to 2005 server: 1.0 (correct!)SSMS 2008 to 2005 server: 1.0 (correct!)osql client to 2008 server = Error 8115: Arithmetic overflow error converting expression to data type numeric.SQLCMD client to 2008 server = Error 8115: Arithmetic overflow error converting expression to data type numeric.SSMS 2008 to 2008 server = Error 8115: Arithmetic overflow error converting expression to data type numeric.Tue, 04 May 2010 08:15:08 GMTSQL Kiwihttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspxGood question, great discussion. Very interesting problem. I definitely learned a lot today, including to watch how I set up ad hoc queries.Thanks!Tue, 04 May 2010 08:14:21 GMTCarla Wilson-484785http://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]webrunner (5/4/2010)[/b][hr]Interesting question, and fascinating discussion. At this point, I'm not sure what the final correct explanation will be.If it turns out that the error is only in some clients, does that mean that those of us who got the question wrong by picking 1 can get ROUND(0.5,0) points for it? :-P - just kidding!- webrunner[/quote]I appreciate the discussion, but the question was ambiguous in the extreme, and I join those who didn't consider it a good QotD per se. Where I come from (the UpOver land of SimpleDom :-)), SELECT ROUND(0.5, 0) returns the sum of (a) the rounded-up value of the first operand and (b) a zapped fractional component in a value formatted the same as the first operand (in this case, NUMERIC(2,1)) -- again, I appreciate knowing that different query engines return different results, but that fact means that the QotD was not well-phrased! Just my tuppence.Tue, 04 May 2010 08:12:02 GMTMichael Poppershttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspxInteresting question, and fascinating discussion. At this point, I'm not sure what the final correct explanation will be.If it turns out that the error is only in some clients, does that mean that those of us who got the question wrong by picking 1 can get ROUND(0.5,0) points for it? :-P - just kidding!- webrunnerTue, 04 May 2010 07:46:21 GMTwebrunnerhttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]Paul White NZ (5/4/2010)[/b][hr][quote][b]Hugo Kornelis (5/4/2010)[/b][hr]Here is another repro that runs fine on all clients, to demonstrate that SQL Server itself actually does not have any issues with this code:[code]-- Or, the more direct approachSELECT CAST(ROUND(0.5,0) AS decimal(12,2));[/code][/quote]According to Jim Hogg (MSFT) on Connect, the error is raised by SQL Server, and is By Design:[url]https://connect.microsoft.com/SQLServer/feedback/details/364387/using-round-function-with-passing-numeric-expression-to-9-5-and-length-0[/url][/quote]Good find, Paul.But Jim is wrong. If his explanation was correct, than[code="sql"]DECLARE @x numeric(2,1); SET @x = ROUND(9.5,0);[/code]should return an error, on [i]all[/i] clients. It does not. But[code="sql"]DECLARE @x numeric(2,1); SET @x = ROUND(9.5,0); SELECT @x;[/code]does return an error, on [i]some[/i] clients.Tue, 04 May 2010 07:05:22 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]Paul White NZ (5/4/2010)[/b][hr][quote][b]Hugo Kornelis (5/4/2010)[/b][hr]Here is another repro that runs fine on all clients, to demonstrate that SQL Server itself actually does not have any issues with this code:[code]-- Or, the more direct approachSELECT CAST(ROUND(0.5,0) AS decimal(12,2));[/code][/quote]According to Jim Hogg (MSFT) on Connect, the error is raised by SQL Server, and is By Design:[url]https://connect.microsoft.com/SQLServer/feedback/details/364387/using-round-function-with-passing-numeric-expression-to-9-5-and-length-0[/url][/quote]I don't think that this explanation is 100% correct, as we had already found out that the error is raised within the Client, not SQL Server (at least thats what I think)This all still does not make too much sense to me, especially after you gave the hint regarding SQLCMD returning 0. Did not see that in the first place.And thanks for your nice comment as well. Feels really good to get such positive feedback from real experts:-)Tue, 04 May 2010 07:01:27 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]Hugo Kornelis (5/4/2010)[/b][hr]Here is another repro that runs fine on all clients, to demonstrate that SQL Server itself actually does not have any issues with this code:[code]-- Or, the more direct approachSELECT CAST(ROUND(0.5,0) AS decimal(12,2));[/code][/quote]According to Jim Hogg (MSFT) on Connect, the error is raised by SQL Server, and is By Design:[url]https://connect.microsoft.com/SQLServer/feedback/details/364387/using-round-function-with-passing-numeric-expression-to-9-5-and-length-0[/url]Tue, 04 May 2010 06:50:07 GMTSQL Kiwihttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]r.hensbergen (5/4/2010)[/b][hr]I got the answer correct for the wrong reason... This really surprises me and definitely learned a lot from this! Shows how important it is to always convert your data before doing calculations. To me, this is a great question. If you want questions without any discussion about the results, things will get real boring around here.[/quote]Fantastic, thank you Ronald. That's exactly how I feel about it.Tue, 04 May 2010 06:37:17 GMTSQL Kiwihttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]Christian Buettner-167247 (5/4/2010)[/b][hr]Does not look like it is settings related.Here is the code executed via each client tool:[/quote]Hey Christian,Thanks for the awesome feedback there - but have you noticed that SQLCMD gets the answer wrong?ROUND(0.5, 0) is "1.0" not ".0"! I got the same results in my tests (see the rather pretty screenshot).This has to be a bug. If no-one comes up with a comprehensive answer, this is going on Connect.PaulTue, 04 May 2010 06:36:32 GMTSQL Kiwihttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspxI got the answer correct for the wrong reason... This really surprises me and definitely learned a lot from this! Shows how important it is to always convert your data before doing calculations. To me, this is a great question. If you want questions without any discussion about the results, things will get real boring around here.Tue, 04 May 2010 05:04:40 GMTRonald Hhttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]Hugo Kornelis (5/4/2010)[/b][hr]Good observation, Christian. The format of the error message, which looks quite different from errors that are thrown by SQL Server, is also an indication that there is a different source of the error.[/quote]This just gets more and more interesting. I have been able to reproduce the SSMS-only behaviour when connecting to a default instance of SQL Server 2005 from SSMS and SQLCMD. I found this in Books Online:[size="-1"][hr][b]Important:[/b][i]SQL Server Management Studio uses the Microsoft .NET Framework SqlClient for execution in regular and SQLCMD mode in Query Editor. When sqlcmd is run from the command line, sqlcmd uses the OLE DB provider. Because different default options may apply, you might see different behavior when you execute the same query in SQL Server Management Studio in SQLCMD Mode and in the sqlcmd utility.[/i][/size][hr]So, I thought...that explains that. Except that it doesn't. I get a proper SQL-Server-generated error for all the tests presented so far when connected to a named instance of SQL Server 2008 - connecting from SSMS or SQLCMD! The error is:[code="plain"][color="#FF0000"].Net SqlClient Data Provider: Msg 8115, Level 16, State 2, Line 1Arithmetic overflow error converting expression to data type numeric.[/color][/code]The error is captured in a Profiler trace under the Errors and Warnings: User Error Message category, so this is definitely a SQL Server error. This does not happen when connected to SQL Server 2005 - the error definitely comes from the provider not SQL Server.Here's a repro to try (notice no value is returned to the client - it is written to a temporary table):[code="sql"]BEGIN TRY SELECT ROUND(0.5, 0) AS a INTO #a;END TRYBEGIN CATCH SELECT message = ERROR_MESSAGE(), number = ERROR_NUMBER(), line = ERROR_LINE(), severity = ERROR_SEVERITY(), state = ERROR_STATE();END CATCH;DROP TABLE #a;[/code]That produces full error details when connected to 2008, but runs without error on 2005 - even from SSMS. Finally, to add to the evidence, this is a screenshot of the results of SELECT ROUND(0.5, 0) when run from SQLCMD against 2005 and 2008:[img]http://www.sqlservercentral.com/Forums/Attachment5870.aspx[/img]Answers on a postcard, please!Tue, 04 May 2010 03:43:43 GMTSQL Kiwihttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspxThank you Hugo - feels good to get positive feedback from an expert :-)Tue, 04 May 2010 03:08:22 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]cengland0 (5/4/2010)[/b][hr][quote][b]ra.shinde (5/3/2010)[/b][hr]Looks like another bad question.[/quote]I would not consider this a bad question - I assume that almost noone actually knew the behaviour differences and actual cause of the error in SSMS before reading the comments.I definitively learned something again today, and therefore I consider this a good question.In the end, this is to some extent unpredictable behaviour. Paul definitively did enough research & due diligence before posting this question - sometimes items only get caught in the field test when tested with a wide variety of tools & settings etc. This is the real life.So definitively thumbs up from me for this QODT!Tue, 04 May 2010 03:05:40 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]ra.shinde (5/3/2010)[/b][hr]I executed all three statement together. Still no error. Getting 1.0[/quote]Same here. Looks like another bad question. The explanation was good but the answer is still wrong.I executed all three commands at one time and am using SQL 2005 with SQLDbx as the client. It produces Numeric(1,1) with an answer of 1.I selected 1 as the correct answer and was told it was wrong.Tue, 04 May 2010 02:48:47 GMTcengland0http://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]Christian Buettner-167247 (5/4/2010)[/b][hr]One important observation: This is an error caused within the client tool, not within SQL Server.[/quote]Good observation, Christian. The format of the error message, which looks quite different from errors that are thrown by SQL Server, is also an indication that there is a different source of the error.Here is another repro that runs fine on all clients, to demonstrate that SQL Server itself actually does not have any issues with this code:[code]DECLARE @a sql_variant;SET @a = ROUND(0.5,0);-- no errorSELECT CAST(@a AS decimal(12,2));-- error when running on SSMSSELECT @a;go-- Or, the more direct approachSELECT CAST(ROUND(0.5,0) AS decimal(12,2));go[/code]Tue, 04 May 2010 02:34:22 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspxNice intuitive question and an equally impressive explanation, Mr.Black Cap. Learnt quite a few behind-the-scenes of datatype conversions and roundings! Thanks!Tue, 04 May 2010 02:00:05 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspxDoes not look like it is settings related.Here is the code executed via each client tool:[code]SET QUOTED_IDENTIFIER OFFSET ARITHABORT OFFSET ANSI_NULL_DFLT_ON OFFSET ANSI_DEFAULTS OFFSET ANSI_WARNINGS OFFSET ANSI_PADDING OFFSET ANSI_NULLS OFFSET CONCAT_NULL_YIELDS_NULL OFFSELECT S.quoted_identifier, S.arithabort, S.ansi_null_dflt_on, S.ansi_defaults, S.ansi_warnings, S.ansi_padding, S.ansi_nulls, S.concat_null_yields_nullFROM sys.dm_exec_sessions SWHERE session_id = @@SPID;DECLARE @a sql_variantSET @a = ROUND(0.5,0);SELECT CAST(SQL_VARIANT_PROPERTY ( @a , 'BaseType') AS varchar(10)) [BaseType],CAST(SQL_VARIANT_PROPERTY ( @a , 'Precision') AS varchar(10)) AS [Precision],CAST(SQL_VARIANT_PROPERTY ( @a , 'Scale') AS varchar(10)) AS [Scale],CAST(@a as varbinary) BinarySELECT @a [/code]SSMS:[quote]quoted_identifier arithabort ansi_null_dflt_on ansi_defaults ansi_warnings ansi_padding ansi_nulls concat_null_yields_null----------------- ---------- ----------------- ------------- ------------- ------------ ---------- -----------------------0 0 0 0 0 0 0 0BaseType Precision Scale Binary---------- ---------- ---------- --------------------------------------------------------------numeric 1 1 0x010100010A000000-------------------------------------------------------------------------------------An error occurred while executing batch. Error message is: Arithmetic Overflow.[/quote]SQLCMD:[quote]quoted_identifier arithabort ansi_null_dflt_on ansi_defaults ansi_warnings ansi_padding ansi_nulls concat_null_yields_null----------------- ---------- ----------------- ------------- ------------- ------------ ---------- ----------------------- 0 0 0 0 0 0 0 0BaseType Precision Scale Binary---------- ---------- ---------- --------------------------------numeric 1 1 0x010100010A000000------------------------------------------------------------------------------------------.0[/quote]The error message as such also indicates a client error as SQL Server errors usually look a little different, for example like this:[quote]Msg 8115, Level 16, State 8, Line 2Arithmetic overflow error converting numeric to data type numeric.[/quote]Tue, 04 May 2010 01:51:50 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]Christian Buettner-167247 (5/4/2010)[/b][hr]One important observation: This is an error caused within the client tool, not within SQL Server. Query Analyzer has no issues; SQL Server management Studio does have the issue....SQL Server Query Analyzer will execute the code without error, and SQL Server Management Studio will error only for "SELECT @a".To me it looks like this is an issue with incorrect metadata being returned. The data itself obviously seems to be correct.Edit: sqlcmd does not throw an error either - so this seems to be an issue with SSMS only.[/quote]Results may depend on session SETtings which may be different for those tools.What does the following return for you in each client tool?[code="sql"]SELECT S.quoted_identifier, S.arithabort, S.ansi_null_dflt_on, S.ansi_defaults, S.ansi_warnings, S.ansi_padding, S.ansi_nulls, S.concat_null_yields_nullFROM sys.dm_exec_sessions SWHERE session_id = @@SPID;[/code]Tue, 04 May 2010 01:36:24 GMTSQL Kiwihttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspxOne important observation: This is an error caused within the client tool, not within SQL Server.Query Analyzer has no issues; SQL Server management Studio does have the issue.It does not matter against which database version you are executing this query.Try this as well to see the error happen only when you actually return results to the client:[code]DECLARE @a sql_variantSET @a = ROUND(0.5,0);SELECT CAST(SQL_VARIANT_PROPERTY ( @a , 'BaseType') AS varchar(10)) [BaseType],CAST(SQL_VARIANT_PROPERTY ( @a , 'Precision') AS varchar(10)) AS [Precision],CAST(SQL_VARIANT_PROPERTY ( @a , 'Scale') AS varchar(10)) AS [Scale]SELECT @a[/code]SQL Server Query Analyzer will execute the code without error, and SQL Server Management Studio will error only for "SELECT @a".To me it looks like this is an issue with incorrect metadata being returned. The data itself obviously seems to be correct.Edit: sqlcmd does not throw an error either - so this seems to be an issue with SSMS only.Tue, 04 May 2010 01:18:18 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]Paul White NZ (5/3/2010)[/b][hr]Comments posted to this topic are about the item [B]<A HREF="/questions/T-SQL/69825/">A round number</A>[/B][/quote]Me too. I executed the code and there will be no error.Tue, 04 May 2010 00:42:09 GMTjava56phttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspxI executed all three statement together. Still no error. Getting 1.0Mon, 03 May 2010 23:55:02 GMTra.shindehttp://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx[quote][b]ra.shinde (5/3/2010)[/b][hr]I am not getting the error. It returns result as 1.0. I am using SQL Server 2005. Is there any setting, due to which I am not getting any error?[/quote]But even if u execute one by one, the same error is thrown. I'm also using SQL server 2005 only. I did not change any setting before executing this query set.Mon, 03 May 2010 23:53:40 GMTNiths