SQLCMD and SET QUOTED_IDENTIFIER


Let’s talk about something simple today. You might already be knowing this, but if you haven’t seen/noted this before then this post will definitely help you.

I was working on requirement wherein I had to simulate a workload which will do massive inserts to a table which had computed column. I tested my T-SQL script to create the workload via SSMS and it worked like a gem.

Yay, all good and that’s the end of this post!  No, not really.

Things started little interesting when I used SQLCMD as a test case to call the same T-SQL. I got an error which said:

Msg 1934, Level 16, State 1, Server <Name>, Line 4
INSERT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

sqlcmd

 

That’s interesting because my commands worked just great when I used SSMS earlier! What’s wrong with SQLCMD now?

I quickly realized that this has to something to do with query execution options, and cross checked SSMS options.

sqlcmd2

 

So, SET QUOTED_IDENTIFIER is ON for SSMS connections, cool and that’s the reason why my commands where running cool earlier.

I quickly checked books online article for sqlcmd and found this. [Well, this is a very good article which is written for sqlcmd by those technical writers at Microsoft]

sqlcmd3

So, adding -I to sqlcmd as a query execution option fixed my problem.

sqlcmd4

Conclusion: 

Little tips and tricks like these always surprises me when I’m dealing with SQLServer, and it helps me learn something or the other daily.

Thanks for reading and keep watching this space for more. Yep, lot of SQL2014 goodness coming in next few days.

Advertisements

6 thoughts on “SQLCMD and SET QUOTED_IDENTIFIER

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s