This is a weird one - try this query
CREATE INDEX idx_test ON table_test(col_one,col_two);
WIth plain SQLcmd.exe
e.g. SQLCMD.exe -S server\instance -q "USE mydb; CREATE INDEX idx_test ON table_test(col_one,col_two);"
It fails with this error
Msg 1934, Level 16, State 1, Server THOMAS-TESTSQL\DEV1, Line 1 CREATE INDEX failed because the following SET options have incorrect settings: ' QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed vie ws and/or indexes on computed columns and/or filtered indexes and/or query notif ications and/or XML data type methods and/or spatial index operations.
but if I run the same with powershell Invoke-Sqlcmd it works just fine.. as well as in sql-server studio
Quoted identifier setting is off (turning it on doesnt make any difference).
i tried with file-input as well, tried changing encoding etc - but pretty much seems anything with multiple columns in a create index just simply fails with plain sqlcmd.exe - single columns are fine.
user has db_owner role - but tried with a sysadmin user as well (windows auth only box).
tried from multiple hosts as well as on the db server itself - the target is sql-server 2014 std, not sure what more info I can provide that helps..
I guess my first question out there is if it is just me or is it a real bug?
I have been unable to find anything specifically related to this out there
Each of the environments is going to have their own set of default values for the SET options that need to be configured. Prepend this to your CREATE INDEX script and things should be fine:set Quoted_Identifier ON; -- Indexed Views and Replication Settings
set ANSI_Nulls ON;
set ANSI_Padding ON;
set ANSI_Warnings ON;
set ArithAbort ON;
set Concat_Null_Yields_Null ON;
set Numeric_RoundAbort OFF;
1 Like
All our scripts include a call to a Logging SProc - it logs that the script was run. It takes parameters for the Script Name and Version Number, and also logs the current server and database names (in case the DB is restored somewhere else in the future and it is helpful to know the "came from") and the date/time.
That logging SProc checks all the currently SET options and complains if any of them are set wrongly, giving us early warning that something is wrong.
We've had instances where in the middle of a script of various Sprocs we had a piece of code that was generated by SSMS and it started with a SET QUOTED_IDENTIFIER ON, which was fair enough, but then finished by turning that OFF - which then left it off for the duration of the remainder of the script. All the SProcs ran just fine ... until we subsequently added an INDEX to a VIEW and then all the SProcs created with the wrong SET options starting falling over 
So ever since they we've relied on our Logging Sproc to alert us to wrong SET options.
Here's the thread in which all that "stuff" was discussed, some years ago:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=167282
1 Like
Thank you guys - that worked beautifully - i never even thought of that I could get anything but by default for my local session when I didnt do anything to change it that i know of.. quite annoying but now I know to be more explicit and check-ful on my session settings..
issue solved
We have something similar to this but it is implemented as a DDL trigger. I'm sure that you have found how useful it is to be able to find what changed, when it changed and, here's the big one, WHO changed it. I worked at one place where they also preserved the original code when performing an ALTER [PROC, FUNCTION, TRIGGER, etc.] statement. This allows the ability to rollback a change that wasn't working out. The rollback could be done on an object by object basis or to a point in time. I've been thinking of implementing this at my current site but time is in short supply.
I hadn't thought of having my Logging SProc take a copy of the existing/"previous version" code, but that would be handy. We use Revision Control for our code, so the original would be in there, but I probably couldn't be 100% sure that I could get the exact original version out of Revision Control (maybe a small change was made when the Object was deployed ...)
We use ALTER (rather than DROP and CREATE) for such changes, so that will only make a change when there are no syntax errors in the new code (which is not the same as no run-time-syntax-errors of course ), which helps with avoiding deploying a change that doesn't even compiled - i.e. the original is not "lost" until the replacement will at least compile.
Final thought: apart from changes in DEV, and occasional small / emergency patches, all our Objects use WITH ENCRYPTION so keeping the original-content may not be possible / any use (unless I can copy an encrypted object, and have the ability to replace it in the same, binary, form perhaps?). We use WITH ENCRYPTION only to prevent casual alterations, rather than relying on it to prevent industrial espionage (e.g. some Well Meaning Person 
just doing DESIGN or PROPERTIES in SSMS)