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 Wednesday, May 1, 2013 7:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:23 PM
Points: 1,951, Visits: 1,473
vinu512 (5/1/2013)
Good Question Ron....and thanks for the explanation Hugo.


+1




Everything is awesome!
Post #1448405
Posted Wednesday, May 1, 2013 7:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 10:36 AM
Points: 320, Visits: 701
vinu512 (5/1/2013)
Good Question Ron....and thanks for the explanation Hugo.


+1
Post #1448426
Posted Wednesday, May 1, 2013 9:06 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, October 24, 2014 12:43 PM
Points: 4,126, Visits: 3,428
Interesting, very interesting... Thanks, Ron!
Post #1448460
Posted Wednesday, May 1, 2013 9:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:15 AM
Points: 2,464, Visits: 1,551
+1 Thanks for your contribution to man of us learning something in this question.

Not all gray hairs are Dinosaurs!
Post #1448486
Posted Wednesday, May 1, 2013 11:09 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:51 AM
Points: 1,938, Visits: 2,372
vinu512 (5/1/2013)
Good Question Ron....and thanks for the explanation Hugo.

+1



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1448689
Posted Thursday, May 2, 2013 12:26 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, November 12, 2014 3:04 AM
Points: 886, Visits: 167
Nice question....
Post #1448704
Posted Thursday, May 2, 2013 1:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:57 AM
Points: 2,587, Visits: 2,443
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 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).


Thanks Hugo, that makes sense. I was having a hard time understanding why someone would think there was a problem here.

+1
Post #1448707
Posted Thursday, May 2, 2013 11:07 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:45 PM
Points: 17,947, Visits: 15,941
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
Post #1448925
Posted Thursday, May 2, 2013 11:50 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 12:24 PM
Points: 3,384, Visits: 2,017
Interesting question and nice insight from Hugo. Thanks to all!
Post #1448932
Posted Friday, May 3, 2013 6:53 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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
Post #1449175
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse