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: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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 6, 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 1, 2013 12:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 10:02 AM
Points: 1,372, Visits: 1,566
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 1, 2013 1:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:38 PM
Points: 1,029, Visits: 2,334
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
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Post #1448324
Posted Wednesday, May 1, 2013 2:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 6,086, Visits: 8,354
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 1, 2013 2:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:38 PM
Points: 1,029, Visits: 2,334
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
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Post #1448335
Posted Wednesday, May 1, 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 1, 2013 10:00 AM
Points: 808, Visits: 41
Very well put, Hugo!
Post #1448341
Posted Wednesday, May 1, 2013 2:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:00 PM
Points: 7,847, Visits: 9,596
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 1, 2013 3:25 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, September 1, 2014 7:39 AM
Points: 642, Visits: 520
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 1, 2013 5:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 6:04 AM
Points: 1,127, Visits: 1,599
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