SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by bitbucket / SET Options - 2 / Latest Posts

RE: SET Options - 2

WWDMark — Fri, 05 Apr 2013 08:59:02 GMT

Oops; those damned semi-colons get in everywhere;!!Luckily I chose to ignore it after thinking "they wouldn't do that, would they??" :-D

RE: SET Options - 2

Andre Ranieri — Wed, 13 Feb 2013 16:35:04 GMT

+1 on the semi-colon being illegal after GO

RE: SET Options - 2

manik123 — Thu, 24 Jan 2013 00:43:57 GMT

nice one.... thanks for question.... we can create table name with double quotes.... and coulumn name also....:-)manikandan.psGo as fast as possible.

RE: SET Options - 2

asifkareem — Tue, 15 Jan 2013 13:53:13 GMT

RE: SET Options - 2

(Bob Brown) — Wed, 05 Dec 2012 04:09:39 GMT

[quote][b]Toreador (12/5/2012)[/b][hr]Shame about the endless complaints about the semi-colon (does nobody ever read the thread before adding to it?)The easy way to have avoided confusion as to whether the syntax error was intended is to do what I did, and fail to notice it was there :-)[/quote]:doze:

RE: SET Options - 2

Toreador — Wed, 05 Dec 2012 01:57:41 GMT

Shame about the endless complaints about the semi-colon (does nobody ever read the thread before adding to it?)The easy way to have avoided confusion as to whether the syntax error was intended is to do what I did, and fail to notice it was there :-)

RE: SET Options - 2

kapil_kk — Tue, 04 Dec 2012 23:17:02 GMT

nice basic question neglecting ':' part

RE: SET Options - 2

Lokesh Vij — Tue, 04 Dec 2012 21:28:59 GMT

Good Question Ron. Thanks for posting. Its always good to revise our basics :-)

RE: SET Options - 2

Samith C — Tue, 04 Dec 2012 21:27:59 GMT

Nice Question That ";" made a small black mark on the question :)

RE: SET Options - 2

prvmine — Tue, 04 Dec 2012 20:19:10 GMT

RE: SET Options - 2

SQLRNNR — Tue, 04 Dec 2012 19:24:59 GMT

Thanks Ron.

RE: SET Options - 2

Koen Verbeeck — Tue, 04 Dec 2012 13:51:09 GMT

Great question Ron, thanks!

RE: SET Options - 2

seankyleprice — Tue, 04 Dec 2012 12:39:37 GMT

[quote][b]ma-516002 (12/4/2012)[/b][hr]Got it wrong, because I thought it is a trick question since "GO;" will throw an error and the table will not be created.[/quote]Agreed. If the SQL is executed with the semi-colon an error will be thrown and the table will not be created.

RE: SET Options - 2

(Bob Brown) — Tue, 04 Dec 2012 10:36:35 GMT

[quote]BLAH! Why can't I ever read question writers minds?Too often the surprise questions leave me sitting there going... did he mean for the ; to throw the error or not.Obviously now it's that he didn't mean for that. Of course I had to guess which way his devious mind was going.Steve should at least fix the question for the later comers.[/quote]Sad to have to ferret out the syntax typos and get to question the author meant. Sometimes the syntax errros are the question and sometimes not?? And I'm a bad guesser.

RE: SET Options - 2

mtassin — Tue, 04 Dec 2012 10:31:15 GMT

[quote][b]mtassin (12/4/2012)[/b][hr]I get this if I run the script.Msg 102, Level 15, State 1, Line 8Incorrect syntax near 'GO'.If I remove the semi-colon from the GO, it works.[/quote]BLAH! Why can't I ever read question writers minds?Too often the surprise questions leave me sitting there going... did he mean for the ; to throw the error or not.Obviously now it's that he didn't mean for that. Of course I had to guess which way his devious mind was going.Steve should at least fix the question for the later comers.

RE: SET Options - 2

mtassin — Tue, 04 Dec 2012 10:27:48 GMT

I get this if I run the script.Msg 102, Level 15, State 1, Line 8Incorrect syntax near 'GO'.If I remove the semi-colon from the GO, it works.

RE: SET Options - 2

EL Jerry — Tue, 04 Dec 2012 10:13:47 GMT

I picked the right answer because I did think the semicolon was a typo from Ron.Great question about options, but keep in mind that naming DB objects with reserved words is not a good practice.Saludos,

RE: SET Options - 2

Revenant — Tue, 04 Dec 2012 09:03:13 GMT

RE: SET Options - 2

Rose Bud — Tue, 04 Dec 2012 08:27:12 GMT

[quote][b]Hugo Kornelis (12/4/2012)[/b][hr]I picked the wrong option. If I had checked the submitter of the question, I probably would have picked the right option - I don't think I ever saw an intentional trick question from Ron.[/quote]I did see Ron was the submitter and I still picked the wrong option. I thought to myself, "It's not like him to write an intentional trick question but it's not like him to create a blatent typo either." Other than this snag, I liked the question.

RE: SET Options - 2

paul.knibbs — Tue, 04 Dec 2012 08:22:32 GMT

[quote][b]bitbucket-25253 (12/4/2012)[/b]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.[/quote]Are programmers (including the ones who write SQL code) the only people who think retyping something is less hassle than cut and paste, I wonder? :-)

RE: SET Options - 2

SanDroid — Tue, 04 Dec 2012 07:34:10 GMT

[quote][b]okbangas (12/3/2012)[/b][hr]Wrong answer marked as correct. SQL Server will return the following error message:[code]Msg 102, Level 15, State 1, Line 5Incorrect syntax near ';'.[/code]Semicolon is not allowed after go.[/quote]So hard to tell why anyone would post a QOTD without actualy running the SQL.... :crazy:Of course like Hugo said... had I looked at who posted the question I would have known....:-P

RE: SET Options - 2

Dave62 — Tue, 04 Dec 2012 07:22:20 GMT

[quote][b]bitbucket-25253 (12/4/2012)[/b][hr]My apologies to all ..... 1. I did test the code multiple times ... 2. When testing the [b];[/b] 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: [b]My apologies to all[/b][/quote]We're all human Ron. Apology not needed but certainly accepted. ;-)

RE: SET Options - 2

bitbucket-25253 — Tue, 04 Dec 2012 07:11:26 GMT

My apologies to all ..... 1. I did test the code multiple times ... 2. When testing the [b];[/b] 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: [b]My apologies to all[/b]

RE: SET Options - 2

Stewart "Arturius" Campbell — Tue, 04 Dec 2012 07:04:05 GMT

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

RE: SET Options - 2

Dave62 — Tue, 04 Dec 2012 06:45:28 GMT

[quote][b]Hugo Kornelis (12/4/2012)[/b][hr]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).... [/quote]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.

RE: SET Options - 2

L' Eomot Inversé — Tue, 04 Dec 2012 04:39:21 GMT

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.

RE: SET Options - 2

(Bob Brown) — Tue, 04 Dec 2012 04:22:09 GMT

Dont' know what everyone else got but on SS 2008 I got:Msg 102, Level 15, State 1, Line 5Incorrect syntax near ';'.Looks like another fine QotD!

RE: SET Options - 2

Dineshbabu — Tue, 04 Dec 2012 03:43:30 GMT

Thanks man. Reall Great

RE: SET Options - 2

Gazareth — Tue, 04 Dec 2012 03:20:19 GMT

Nice question on what quoted identifiers can let you get away with, shame about the extra ';' which means the table isn't created. :-)

RE: SET Options - 2

Stuart Davies — Tue, 04 Dec 2012 02:50:45 GMT

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

RE: SET Options - 2

Paul White — Tue, 04 Dec 2012 02:24:09 GMT

[quote][b]dineshbabus (12/4/2012)[/b][hr]Please refer this URL. It says by default IT will be ON.http://msdn.microsoft.com/en-us/library/ms174393(SQL.105).aspx[/quote][code="sql"]-- Default for user options is 0-- See http://msdn.microsoft.com/en-us/library/ms189631.aspx-- Flag values are at http://msdn.microsoft.com/en-us/library/ms176031.aspx-- e.g. QUOTED_IDENTIFER is 256SELECT c.value_in_use FROM sys.configurations AS cWHERE c.name = N'user options';-- Show database-level defaultsSELECT name, is_quoted_identifier_onFROM sys.databases AS d;-- Is QUOTED_IDENTIFIER on for the current connection?-- A value of 256 means yes-- See http://msdn.microsoft.com/en-us/library/ms177525.aspxSELECT @@OPTIONS & 256;[/code]Try connecting via sqlcmd instead of using SSMS. It does not SET QUOTED_IDENTIFIER ON unless you specify the -I switch, see [url]http://msdn.microsoft.com/en-us/library/ms162773.aspx[/url][img]http://www.sqlservercentral.com/Forums/Attachment12797.aspx[/img]

RE: SET Options - 2

Paul White — Tue, 04 Dec 2012 02:07:42 GMT

[quote][b]dineshbabus (12/4/2012)[/b][hr]I Opened a new SQL Server Management Studio and tried the create statement without any set options. Query works fine. Can you explain in detail?[/quote]SQL Server Management Studio is a client application which (by default) issues SET QUOTED_IDENTIFIER ON when connecting. It depends on how you have your SSMS configured, of course:[img]http://www.sqlservercentral.com/Forums/Attachment12796.aspx[/img]

RE: SET Options - 2

Hugo Kornelis — Tue, 04 Dec 2012 02:02:48 GMT

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).I picked the wrong option. If I had checked the submitter of the question, I probably would have picked the right option - I don't think I ever saw an intentional trick question from Ron.

RE: SET Options - 2

ma-516002 — Tue, 04 Dec 2012 01:57:28 GMT

Got it wrong, because I thought it is a trick question since "GO;" will throw an error and the table will not be created.

RE: SET Options - 2

Dineshbabu — Tue, 04 Dec 2012 01:00:19 GMT

Please refer this URL. It says by default IT will be ON.http://msdn.microsoft.com/en-us/library/ms174393(SQL.105).aspx

RE: SET Options - 2

Dineshbabu — Tue, 04 Dec 2012 00:55:47 GMT

I Opened a new Sql server management studio and tried the create statement without any set options. Query works fine.can you xplain in detail?

RE: SET Options - 2

Paul White — Tue, 04 Dec 2012 00:06:37 GMT

[quote][b]dineshbabus (12/3/2012)[/b][hr]By Default "Quoted Identifier" Will be ON. Eventhoug if we didn't set any options. the syntax will work fine. Please correct me if i'm wrong.[/quote]The default is OFF (see [url]http://msdn.microsoft.com/en-us/library/ms190707(v=sql.105).aspx[/url] and [url]http://msdn.microsoft.com/en-us/library/ms191203(v=sql.105).aspx[/url]).However, most client applications SET QUOTED_IDENTIIFIER ON when they connect.

RE: SET Options - 2

Dineshbabu — Mon, 03 Dec 2012 23:25:32 GMT

By Default "Quoted Identifier" Will be ON. Eventhoug if we didn't set any options. the syntax will work fine.Please correct me if i'm wrong.

RE: SET Options - 2

okbangas — Mon, 03 Dec 2012 22:15:17 GMT

Wrong answer marked as correct. SQL Server will return the following error message:[code]Msg 102, Level 15, State 1, Line 5Incorrect syntax near ';'.[/code]Semicolon is not allowed after go.

SET Options - 2

bitbucket-25253 — Mon, 03 Dec 2012 20:11:23 GMT

Comments posted to this topic are about the item [B]SET Options - 2[/B]