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.

 

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.

 

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]

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

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.