Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

REPLICATE - 1 Expand / Collapse
Author
Message
Posted Tuesday, April 30, 2013 8:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:50 AM
Points: 5,471, Visits: 23,514
Comments posted to this topic are about the item REPLICATE - 1

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1448290
Posted Tuesday, April 30, 2013 11:22 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 06, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Thanks for question.



Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1448309
Posted Wednesday, May 01, 2013 12:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:46 AM
Points: 1,371, Visits: 1,547
Thanks Ron an interesting question. I never thought of trying and testing that option

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1448319
Posted Wednesday, May 01, 2013 1:03 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 10:45 AM
Points: 673, Visits: 1,551
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 is also 5 so no changes in the @S in the SET level0
- as the @S is set V5, so the datalength is 5 of the S as-well in the table

can any one shed some light please?


ww; Raghu
--
There are only 10 types of people in the world, those who understand binary, and those who don't.

Note: (as of now) only.. 1 and 4 applies (i am on my way...)
Post #1448324
Posted Wednesday, May 01, 2013 2:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 5,794, Visits: 8,008
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 is also 5 so no changes in the @S in the SET level0
- as the @S 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 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).



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1448333
Posted Wednesday, May 01, 2013 2:22 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 10:45 AM
Points: 673, Visits: 1,551
Thank you, Hugo. Glad I am on the same page. (but I still don't get the catch; possibly it may be what you have mentioned in the first line.)

ww; Raghu
--
There are only 10 types of people in the world, those who understand binary, and those who don't.

Note: (as of now) only.. 1 and 4 applies (i am on my way...)
Post #1448335
Posted Wednesday, May 01, 2013 2:37 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 10:00 AM
Points: 807, Visits: 41
Very well put, Hugo!
Post #1448341
Posted Wednesday, May 01, 2013 2:50 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 4:44 PM
Points: 8,286, Visits: 8,736
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.

As the explanation refers to the BOL page on REPLICATE, and not to the page on SET ANSI_WARNINGS, I suspect it was the former.

However ...

1. Rounding numerical data is not an overflow condition; AFAIK this does not raise an error regardless of ANSI_WARNINGS setting;

I imagine that was it.

Despite all that, it is an interesting question. Actually, until I saw the explanation I thought it was a very good question, but unfortunately that explanation will probably confuse quite a few people.


Tom
Post #1448348
Posted Wednesday, May 01, 2013 3:25 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:17 AM
Points: 636, Visits: 508
Interesting QoTD, got me thinking about the conversion of 12.5 and why. Thanks.


MCITP
MCTS - E-Business Card
Twitter: WWDMark

Try not! Do or do not, there is no try

email: info@weekendwebdesign.co.uk
Personal Website: www.markallen-online.co.uk
Business Website: www.weekendwebdesign.co.uk
Post #1448358
Posted Wednesday, May 01, 2013 5:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
Good Question Ron....and thanks for the explanation Hugo.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1448378
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse