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 Monday, December 3, 2012 8:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:17 AM
Points: 5,589, Visits: 24,967
Comments posted to this topic are about the item SET Options - 2

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 #1392225
Posted Monday, December 3, 2012 10:15 PM


SSC Eights!

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

Group: General Forum Members
Last Login: 2 days ago @ 11:49 PM
Points: 833, Visits: 1,365
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.




Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL
Post #1392248
Posted Monday, December 3, 2012 11:25 PM


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
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.


--
Dineshbabu
Desire to learn new things..
Post #1392255
Posted Tuesday, December 4, 2012 12:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:32 PM
Points: 11,194, Visits: 11,140
dineshbabus (12/3/2012)
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.

The default is OFF (see http://msdn.microsoft.com/en-us/library/ms190707(v=sql.105).aspx and http://msdn.microsoft.com/en-us/library/ms191203(v=sql.105).aspx).
However, most client applications SET QUOTED_IDENTIIFIER ON when they connect.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1392263
Posted Tuesday, December 4, 2012 12:55 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
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?


--
Dineshbabu
Desire to learn new things..
Post #1392271
Posted Tuesday, December 4, 2012 1:00 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
Please refer this URL. It says by default IT will be ON.

http://msdn.microsoft.com/en-us/library/ms174393(SQL.105).aspx


--
Dineshbabu
Desire to learn new things..
Post #1392273
Posted Tuesday, December 4, 2012 1:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, March 3, 2014 12:21 AM
Points: 2,122, Visits: 306
Got it wrong, because I thought it is a trick question since "GO;" will throw an error and the table will not be created.
Post #1392287
Posted Tuesday, December 4, 2012 2:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:22 PM
Points: 5,975, Visits: 8,233
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1392289
Posted Tuesday, December 4, 2012 2:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:32 PM
Points: 11,194, Visits: 11,140
dineshbabus (12/4/2012)
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?

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:





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi


  Post Attachments 
Forum.png (287 views, 51.89 KB)
Post #1392290
Posted Tuesday, December 4, 2012 2:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:32 PM
Points: 11,194, Visits: 11,140
dineshbabus (12/4/2012)
Please refer this URL. It says by default IT will be ON.
http://msdn.microsoft.com/en-us/library/ms174393(SQL.105).aspx

-- 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 256
SELECT
c.value_in_use
FROM sys.configurations AS c
WHERE
c.name = N'user options';

-- Show database-level defaults
SELECT
name, is_quoted_identifier_on
FROM 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.aspx
SELECT @@OPTIONS & 256;

Try connecting via sqlcmd instead of using SSMS. It does not SET QUOTED_IDENTIFIER ON unless you specify the -I switch, see http://msdn.microsoft.com/en-us/library/ms162773.aspx





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi


  Post Attachments 
Forum.png (276 views, 19.63 KB)
Post #1392295
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse