- Remove From My Forums
-
Question
-
Hello,
I am using SQL 2012 SE.I was trying to rebuild indexes from a sql agent job and I got an error shown below:
(Message 0) ALTER_INDEX — dbo. [SQLSTATE 01000] (Message 0) ALTER_INDEX — dbo. [SQLSTATE 01000] (Message 0) ALTER_INDEX — dbo. [SQLSTATE 01000] (Message 0) ALTER_INDEX — dbo. [SQLSTATE 01000] (Message 0) ALTER_INDEX
— dbo. [SQLSTATE 01000] (Message 0) ALTER_INDEX — dbo. [SQLSTATE 01000] (Message 0) ALTER_INDEX — dbo. [SQLSTATE 01000] (Message 0) ALTER_INDEX — dbo. [SQLSTATE 01000] (Message 0) ALTER_INDEX — dbo. [SQLSTATE 01000]
(Message 0) ALTER_INDEX — dbo. [SQLSTATE 01000] (Message 0) ALTER_INDEX — dbo. [SQLSTATE 01000] (Message 0) ALTER_INDEX — dbo. [SQLSTATE 01000] (Message 0) ALTER_INDEX — dbo. [SQLSTATE 01000] (Message 0) ALTER_INDEX
— dbo. [SQLSTATE 01000] (Message 0) ALTER_INDEX — dbo. [SQLSTATE 01000] (Message 0) ALTER INDEX 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. [SQLSTATE 42000] (Error 1934). The step failed.I ran the below script:
select object_name(object_id), * from sys.sql_modules where uses_ansi_nulls=0 or uses_quoted_identifier=0 and found few stored proc's.
Does adding
SET QUOTED_IDENTIFIER ON before the
alter index rebuild statements fix the issue?-
Edited by
Tuesday, April 7, 2015 8:02 PM
-
Edited by
Answers
-
For legacy reasons (and Microsoft should be ashamed over this), SQL Server Agent runs jobs with SET QUOTED_IDENTIFIER OFF. This is a legacy setting. The setting QUOTED_IDENTIFIER must be ON when you use certain functionality in SQL Server, for instances
indexed views and indexes on computed columns.I am not sure whether it helps to put SET QUOTED_IDENTIFIER ON in the top of the script, because this setting may be deduced at compile time. You may have to wrap your batch in dynamic SQL. (If you are already using dynamic SQL in the job, that should be
fine.)You can also put all work in a stored procedure. In this case, Agent’s silly legacy setting will not matter, because the setting of QUOTED_IDENTIFIER is saved with the procedure.
It is when you fiddle with stuff like this, you can’t escape the feeling that SQL Server is designed for maximum confusion.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
-
Proposed as answer by
Eric__Zhang
Monday, April 20, 2015 2:09 AM -
Marked as answer by
Eric__Zhang
Tuesday, April 21, 2015 1:18 AM
-
Proposed as answer by
A mystery staring arithabort.
I was coding along one day, working on rolling out some monitoring for a client—monitoring that I had used for previous clients. The code was pretty straightforward and addressed a concern for the client. Having used it for several previous clients, I felt pretty confident in the code. The main purpose was simply to audit index definition changes. It was rolled into a stored procedure and designed to be deployed to a “DBA” database.
With confidence pretty steady on this code, I deployed the stored procedure along with all of the supporting tables and structures. Then I proceeded to deploy the SQL Agent job that controls the execution of the stored procedure. When I deploy a job I like to test the job to ensure it will work. As luck would have it, the job failed on the first test run. I instantly became baffled. Here is the error message.
Job Name DBA – Track Index Changes
Step Name exec proc
Duration 00:00:08 Sql Severity 16
Sql Message ID 1934
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: sa.
INSERT failed because the following SET options have incorrect settings:
‘ARITHABORT’.
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.
[SQLSTATE 42000] (Error 1934). The step failed.
Immediately I started looking at my creation scripts for the tables. Nope, no XML indexes, no spatial indexes, no filtered indexes, no indexes on computed columns (not even any computed columns), and no query notifications.
Next I started checking the database settings. Maybe the vendor for the application this client bought had set something for all of the databases regarding ARITHABORT.
Querying sys.databases, we could easily see that ARITHABORT is not enabled at the database level (just like previous implementations). Well, this is a minor head scratcher at this point. The code works in other environments, the database setting is the same as other environments. I guess I could try setting ARITHABORT within the stored procedure and then re-test.
When I add the last line, “SET ARITHABORT ON;” to this stored procedure and then rerun the job it runs without any error. It is a simple fix but the story doesn’t end there.
After making that change, I decided to go another round with the stored procedure and the ARITHABORT setting. I removed it in the next round and decided to test the stored procedure directly. Running the stored procedure in Management Studio with or without the ARITHABORT setting produces the same result. The result is that both work as desired without any error. For giggles, I ran the job again and discovered that the job still fails. In the end, it appears to be something that the SQL Agent is setting as a part of its connection back to the database in this case.
In addition to this minor nuisance, you saw that the error outlines several possible causes for failure with regards to ARITHABORT. One that I found that can be of big concern is with filtered indexes. Check your connection settings from your application when dealing with any filtered indexes. Filtered Indexes have produced this error in quite a few cases I have been asked to help fix. Just a thought for something you should monitor and check should you run into this error or if you are considering the use of filtered indexes.
Putting a Bow on it…
In conclusion, this can be a short-term head scratcher. Pay close attention to what has changed in the environment. Test alternatives. And check those connection strings.
Due to the high activity on out servers, we have a number of jobs which perform routine maintenance tasks. This past Sunday, July 30, two of these jobs aborted with simialar errors on two different servers.
The first job, which runs at noon againsts all databases on all production servers, executes the following command:
SQLMAINT.EXE -D xxxx -CkDB -CkAl -CkTxtAl -CkCat -UpdOptiStats -10
On two of our servers, when the above job ran against the msdb database, it errored out on the UpdOptiStats command with the following error:
[5] Database msdb: Updating Query Processor Statistics (sampling -10 percent of the data)…
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER, ARITHABORT’.
The second job runs at 4 PM on only one of the two servers and performs a defragmentation of all clustered indexes on all databases as well as an UPDATE STATISTICS command for each table.
This job failed on the UPDATE STATISTICS command with the following error:
UPDATE STATISTICS msdb..sysdbmaintplan_history
(1 rows(s) affected)
Msg 1934, Sev 16: UPDATE STATISTICS failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER, ARITHABORT’. [SQLSTATE 42000]
I used the ‘databasepropertyex’ function to check the settings for these two options on both of the servers with errors and on serveral other production servers. In all cases, the function returned a value of 0(false), indicating that the option was turned off. Since we do not change these settings after installation, I am assuming that this is the default for these options.
I then ran an UPDATE STATISTICS command against the tables in the msdb database on both of the servers thru Query Analyzer. This worked with no errors.
I then used the xp_cmdshell on both servers to run an ‘sqlmaint’ with the UpdOptiStats command. This failed with the same error above.
It seems that when I try to run any kind of update statistics thru job, that it fails with the errors listed above. i do not know what else to try. Any suggestions would be appreciated.