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 ««1234»»»

SET Options - 2 Expand / Collapse
Author
Message
Posted Tuesday, December 4, 2012 2: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: Yesterday @ 1:00 AM
Points: 3,060, Visits: 3,246
Good question and Hugo raises some good points too.

My mistake in not getting a point today for being too literal with my interpretation of the question


-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Post #1392310
Posted Tuesday, December 4, 2012 3:20 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:20 AM
Points: 1,945, Visits: 3,204
Nice question on what quoted identifiers can let you get away with, shame about the extra ';' which means the table isn't created.
Post #1392323
Posted Tuesday, December 4, 2012 3:43 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: Monday, August 4, 2014 5:38 AM
Points: 945, Visits: 558
Thanks man. Reall Great


--
Dineshbabu
Desire to learn new things..
Post #1392334
Posted Tuesday, December 4, 2012 4: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: Thursday, June 12, 2014 4:19 AM
Points: 701, Visits: 1,145
Dont' know what everyone else got but on SS 2008 I got:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ';'.

Looks like another fine QotD!
Post #1392359
Posted Tuesday, December 4, 2012 4:39 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:08 AM
Points: 8,727, Visits: 9,278
Nice question apart from teh semi-colon.

Some interesting discussion about the default. BoL contradicts itself firmly here - the SET QUOTED_IDENTIFIER page has said the default is ON at least since SQL 2005, while the SET OPTIONS page says it is OFF. I believe that the fact is that it's one of those options like ANSI_WARNINGS where the default in the engine is OFF but the default applied by most connection methods is ON, but unlike ANSI_WARNINGS MS hasn't documented this in BoL (you will only finnd ANSI_WARNINGS off if you connect using DBLIBRARY, it's on by default for the native ODBC and OLE-DB drivers.
It's rather surprising this error has managed to remain on the SET QUOTED_IDENTIFIER page in BoL for so many years.

It's quite surprising too that more of these SET options which can be set off to get non-standard behavious have not been deprecated. There are features which just don't work when they are OFF - for example if QUOTED_IDENTIFIER is OFF tables with indexes on computed columns behave badly.


Tom
Post #1392369
Posted Tuesday, December 4, 2012 6:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:41 AM
Points: 2,046, Visits: 2,045
Hugo Kornelis (12/4/2012)
I had to make a guess as to the author's intention and preparation.

Did he actually try the code himself? In that case, it is a nasty trick question, because everything in the question distracts from the syntax error (semicolon after GO) that causes the batch to fail and the table to not be created.

Of did he forget to execute the code? In that case, it is an unintended trick question, because I can assume that the semicolon after the GO was an oversight and the author wanted us to focus on the ability to create a table name using reserved words (not a recommended good practice, by the way).
...


Another possibility is that Ron did in fact try the code but then added the semicolon when submitting the question to SSC without re-testing. Possibly a good lesson here about minor touch up editing without testing.
Post #1392444
Posted Tuesday, December 4, 2012 7:04 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: Yesterday @ 6:19 AM
Points: 3,917, Visits: 5,109
Saw the semicolon after the GO and thought to myself - this will not execute, therefore table will not be created.
Over and above that, this is a good question about behaviour when QUOTED IDENTIFIERS is ON vs. OFF


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1392464
Posted Tuesday, December 4, 2012 7:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 5,594, Visits: 24,979
My apologies to all .....
1. I did test the code multiple times ...
2. When testing the ; was NOT present.
3. I can only guess, but was using a brand new lap top when submitting the question and typing the QOD rather than a cut and paste from SSMS, and somehow or other I pressed the semi-colon key.
4. Poor excuse on my part for the error so let me say again:
My apologies to all


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 #1392469
Posted Tuesday, December 4, 2012 7:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:41 AM
Points: 2,046, Visits: 2,045
bitbucket-25253 (12/4/2012)
My apologies to all .....
1. I did test the code multiple times ...
2. When testing the ; was NOT present.
3. I can only guess, but was using a brand new lap top when submitting the question and typing the QOD rather than a cut and paste from SSMS, and somehow or other I pressed the semi-colon key.
4. Poor excuse on my part for the error so let me say again:
My apologies to all


We're all human Ron. Apology not needed but certainly accepted.
Post #1392476
Posted Tuesday, December 4, 2012 7:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
okbangas (12/3/2012)
Wrong answer marked as correct. SQL Server will return the following error message:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ';'.

Semicolon is not allowed after go.

So hard to tell why anyone would post a QOTD without actualy running the SQL....

Of course like Hugo said...
had I looked at who posted the question I would have known....
Post #1392483
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse