Category Archives: TSQL

SQLCMD and SET QUOTED_IDENTIFIER

Posted on by

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.

Cannot assign a default value to a local variable !

Posted on by

You will hit this error Cannot assign a default value to a local variable in case you are running SQL 2005/below,and you try to declare a variable and assign it a value in one statement.

Something like  –

DECLARE @Var Varchar(15) = 'Test'

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable

Variable declaration has been enhanced in SQL2008/above and I realized this today when I was deploying a code for multiple systems.

The same declaration mentioned above will work great for 2008/above

Apparently we also have the flexibility to just use one Declare statement to declare multiple variables  –

DECLARE 
@Var Varchar(15) = 'Test',
@Char Varchar(10) = 'Test2',
@Char2 Varchar(10) = 'Test3'

These are indeed “feel good to have” enhancements !

[Update Added on 10/24/202]

In SQL2005 or below you will need to declare the variable and then assign it a value using Set statement.

Example –

DECLARE @Var Varchar(15) 
SET @Var = 'Test'

[Update]

Thanks for reading.