May 2, 2013 at 12:26 am
Nice question....
May 2, 2013 at 1:14 am
crussell-931424 (5/1/2013)
Hugo Kornelis (5/1/2013)
Raghavendra Mudugal (5/1/2013)
sorry; I have hard time in understanding/realizing the missing piece here.- Replicate is replicating as needed (the expression is 5 chars and and @s-2 is also 5 so no changes in the @s-2 in the SET level0
- as the @s-2 is set V5, so the datalength is 5 of the S as-well in the table
can any one shed some light please?
I think Ron expected the ANSI_WARNINGS setting to cause errors. Either because of the implicit conversion of 12.5 to integer, or because of the string truncation.
However ...
1. Rounding numerical data is not an overflow condition; AFAIK this does not raise an error regardless of ANSI_WARNINGS setting;
2. String truncation does cause an error to occur with ANSI_WARNINGS on - but only if it occurs while inserting or updating, not when assigning to a variable. This is mentioned explicitly in the Books Online article (http://msdn.microsoft.com/en-us/library/ms190368.aspx).
If you increase the length of @s-2 to 11 characters or more, the effect of ANSI_WARNINGS does kick in. An error is returned during the INSERT statement, and the final SELECT returns nothing at all. (Since the INSERT errored, no row was inserted, so there is nothing to return).
Thanks Hugo, that makes sense. I was having a hard time understanding why someone would think there was a problem here.
+1
May 2, 2013 at 11:07 am
emanuel ionescu (5/1/2013)
Very well put, Hugo!
+1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 2, 2013 at 11:50 am
Interesting question and nice insight from Hugo. Thanks to all!
May 3, 2013 at 6:53 am
I think we should give Hugo bonus points for another awesome explanation...
Good question no matter what, and an even more awesome explanation from Hugo
May 6, 2013 at 12:45 am
Thanks for the question, and Hugo, thanks for the explanation 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 6, 2013 at 12:49 am
mtassin (5/3/2013)
I think we should give Hugo bonus points for another awesome explanation...Good question no matter what, and an even more awesome explanation from Hugo
Yes, I agree......
May 8, 2013 at 7:11 am
nice question....
Manik
You cannot get to the top by sitting on your bottom.
May 15, 2013 at 2:02 am
Very good question and excellent explanation. 🙂
September 16, 2013 at 3:30 am
Hugo Kornelis (5/1/2013)
I think Ron expected the ANSI_WARNINGS setting to cause errors. Either because of the implicit conversion of 12.5 to integer, or because of the string truncation.
However ...
1. Rounding numerical data is not an overflow condition; AFAIK this does not raise an error regardless of ANSI_WARNINGS setting;
2. String truncation does cause an error to occur with ANSI_WARNINGS on - but only if it occurs while inserting or updating, not when assigning to a variable. This is mentioned explicitly in the Books Online article (http://msdn.microsoft.com/en-us/library/ms190368.aspx).
If you increase the length of @s-2 to 11 characters or more, the effect of ANSI_WARNINGS does kick in. An error is returned during the INSERT statement, and the final SELECT returns nothing at all. (Since the INSERT errored, no row was inserted, so there is nothing to return).
Took me a while to understand, what you wrote there, Hugo, but:
"[...] string too large for database column [...]" in the article http://msdn.microsoft.com/en-us/library/ms190368.aspx made it clear after all. Thanx for your explanation, Hugo, and also thanx to the author of the QotD!
________________________________________________________
If you set out to do something, something else must be done first.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply