I have a small script that I use to rebuild indexes and update statistics on a SQL Server 2014 database.
It runs fine when I execute it in MSSM so I wanted to put it into a job to run every night.
I used this link to create the job :
how to schedule a job for sql query to run daily?
and this is my script in step 1:
declare @command nvarchar(1000)
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
set @command = 'alter index all on ' + @TableName + ' rebuild'
exec sp_executesql @command
set @command = 'update statistics ' + @TableName
exec sp_executesql @command
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
and this is my script in step 2:
insert into tblLog values (getdate(), 0, 'sql agent job rebuild index', 'indexes rebuild')
When I try Start Job at Step it failes however with this message in the history for step 1:
Executed as user: NT SERVICE\SQLAgent$GTT_192. 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 have no clue where this setting QUOTED_IDENTIFIER comes from.
So why does it works when I run it in MSSM and not in a job ?
And more important, what can I do to get it working ?
