SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


REPLICATE - 1


REPLICATE - 1

Author
Message
mtassin
mtassin
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7008 Visits: 72521
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



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58795 Visits: 13297
Thanks for the question, and Hugo, thanks for the explanation :-)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9553 Visits: 1407
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......



manik_anu
manik_anu
SSChasing Mays
SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)

Group: General Forum Members
Points: 625 Visits: 313
nice question....

Manik
You cannot get to the top by sitting on your bottom.
sqlnaive
sqlnaive
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6209 Visits: 2774
Very good question and excellent explanation. :-)
Dscheypie
Dscheypie
SSC-Addicted
SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)

Group: General Forum Members
Points: 476 Visits: 271
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 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search