Category Archives: SQL Agent

Filtered index gotcha

We added a filtered index to a table today and a SQL Agent job that updates that table started to fail with this error message:

Msg 1934, Sev 16, State 1, Line 290 : UPDATE 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]

It turns out that you must have certain options enabled in your session in order to use or update a filtered index.

All these must be ON:


And this one must be OFF:


See Books Online for details: (it’s about half way down the page in the Remarks section).

So what happened here? Well it looks like SQL Agent does not, by default, set QUOTED_IDENTIFIER on. I can’t find any clear official information on this but that’s what I’m seeing in SQL Profiler. I also couldn’t find any way to override this in SQL Agent’s configuration settings.

Anyway, I added SET QUOTED_IDENTIFIER ON at the start of the job step and it worked fine.

SQL Agent CmdExec gotcha

I was writing a new SQL Agent job for somebody else so I thought I should comment it a bit more thoroughly than I usually do. There’s an Operating System (CmdExec) step that calls sqlcmd to run a script file:

sqlcmd -S localhost -E -d master -i "\\\joblogs\somescript.sql" -b

This pattern works fine elsewhere. Here though, I added a comment just to point out something:

rem A comment
sqlcmd -S localhost -E -d master -i "\\\joblogs\somescript.sql" -b

The job ran successfully but it was suspiciously fast. It turns out that’s because it didn’t do anything. Apparently SQL Agent interprets this differently. I know the DOS shell language is a bit arcane sometimes but rem means ‘ignore everything until the next command’. This works in a .bat file. I just tested to make sure I wasn’t going mad.

Time to dig deeper. Books Online ( says:

This topic describes how to create and define a Microsoft SQL Server Agent job step in SQL Server 2016 that uses an executable program or operating system command

That ‘an‘ is a subtle clue. I had always assumed this worked like a batch file but it seems only the first command gets executed. In my case the first line is commented out, which effectively means ‘do nothing’. I tested with multiple statements and again only the first one executed.

So don’t confuse it with batch files. Put one command per step and – a bit of lateral thinking here – put your comments in the line(s) underneath:

sqlcmd -S localhost -E -d master -i "\\\joblogs\somescript.sql" -b
rem A comment

PowerShell in SQL Agent: not what I’d hoped for

Like many DBAs, I suspect, I just haven’t found much need for PowerShell so far. There were lots of articles about how you could backup a database, run a query, get a list of databases etc. I just wasn’t seeing anything I couldn’t already do just fine in T-SQL. But with many Microsoft server products, PowerShell is taking over as the main management tool with the GUI serving as just a thin skin over the PowerShell API. Could this be coming our way?

So I’ve been meaning to give it a try and I just had a request to set up a daily FTP upload of some data to a partner organisation. I don’t know the command line stuff for FTP so this looked like a good excuse to learn a bit of PowerShell. After a while I had a script that worked nicely until I put it into a PowerShell step in a SQL Agent job. Then it failed complaining about how I was specifying non-existent arguments for a cmdlet. WTF?

After a lot of digging, I discovered that SQL Agent shells your script out to PowerShell 2.0. This is SQL Server 2014 and PowerShell 2.0 was RTMd in 2009. We’re up to PowerShell 5.0 now.

I imagine there are sound technical reasons for this but it’s a bit disappointing after all the hype.

Does this folder exist?

Today I tripped over a problem that was new to me. I wanted to find out, in SQL, whether a folder existed in the file system. I knew about using xp_fileexist to check for existence of a file but what about a folder? Well, it doesn’t work on a folder name and there’s no sign of xp_folderexist.

I finally found in a forum that you use xp_fileexist and pass in the imaginary file nul in that folder (eg. to check if the folder ‘C:\test’ exists, check if file ‘C:\test\nul’ exists). There was no explanation but, according to Raymond Chen who knows a few things, NUL is a special, ‘magic’ file name that goes back to DOS 1.0. So some days you learn an old new thing.

How to process a cube in a SQL Agent job

It can be done but it’s not well documented and it’s complicated by the fact that you can’t easily get the result of the process. Unless you actively check and manually raise an error if there’s been a problem, the job will report sucess no matter how screwed up the cube processing is. That part is all about the XML results that are output by the process.

Here’s how I do it (and I’d be very interested if you have any suggestions to improve it):

Connect to the Analysis Services server in SQL Server Management Studio.

Right click on the database and select Process.

Configure all the options and then use the Script button to grab the XML that defines the processing job. It will look something like this:

<Batch xmlns="">
    <ErrorConfiguration xmlns:xsd="" xmlns:xsi="" xmlns:ddl2="" xmlns:ddl2_2="">
    <Process xmlns:xsd="" xmlns:xsi="" xmlns:ddl2="" xmlns:ddl2_2="">

Create a new SQL Agent job.

Add a step to get rid of any old output file(s). Change the step type to CmdExec and paste in this code, modifying the file name:

if exist "\\rep01\joblogs\ProcessASOnBI1Log.xml" (del "\\rep01\joblogs\ProcessASOnBI1Log.xml")

Add a step for the processing. Change the step type to SQL Server Analysis Services Command and paste in the XML. Go to the Advanced page and give it an XML output file – with the same name you used in the previous step – to write its results to.

Add a step to check the output XML file to see whether the process was successful. Change the step type to T-SQL and paste in this code:

-- Check the result files from the cubes
set nocount on;
create table #File
        ResultFileName        varchar(255)

insert #File (ResultFileName) values ('\\rep01\joblogs\ProcessASOnBI1Log.xml');
insert #File (ResultFileName) values ('\\rep01\joblogs\ProcessASOnBI2Log.xml');

create table #FileContents 
        LineNumber        int identity
        , LineContents        nvarchar(4000)
declare @FileContents        nvarchar(4000)
        , @NewLine        char(2)
        , @FileName        varchar(255)
        , @CubesFailed        bit
        , @CmdLine        varchar(300)
set @NewLine = char(13) + char(10);
set @CubesFailed = 0;
-- Loop through result files
declare file_cursor cursor
local forward_only 
select        ResultFileName
from        #File;
open file_cursor
        fetch next from file_cursor 
        into         @FileName
        while @@fetch_status = 0
                set @CmdLine = 'type ' + @FileName;
                insert #FileContents
                exec master.dbo.xp_cmdshell @CmdLine;
                select        @FileContents = isnull(@FileContents, '') + @NewLine + isnull(LineContents, '')
                from        #FileContents;
                select @FileName;
                select @FileContents;
                set nocount off;
                if @FileContents like '%error%'
                        set @CubesFailed = 1
                delete #FileContents;
                -- Get the next cursor row
                fetch next from file_cursor 
                into         @FileName
close file_cursor
deallocate file_cursor
drop table #FileContents;
drop table #File;
if @CubesFailed = 1
        raiserror('Cubes failed to build.', 16, 1)

Modify it to add your own file name(s) into #File at the top. This step will raise an error if the processing failed. If you don’t do this, you won’t know whether the job has worked.

Start a SQL Agent Job in SQL

How do you start a SQL Agent job in SQL? I recently found myself wanting to be able to do this and it turns out it’s really easy:

exec msdb.dbo.sp_start_job 'Check Integrity'

It returns as soon as the job has started, without waiting until the job has finished. You could use it to run a job from a script or a stored procedure. But the way I use it is in the last step of a job in order to run jobs in sequence without having to guess in the schedules how to time them. No wasted time and no danger of the second job running before the first one has finished.


Monitoring SQL Agent Jobs

In my last post I showed how to reliably get your SQL Agent jobs to only bother you if they fail. But how can you be confident that they are in fact running? Well, what I did is I set up a report in Reporting Services that reads the job history tables on a list of servers and shows me what jobs have run lately and whether they succeeded. I have the report sent to me every morning. Successful jobs are green and failed jobs are red. I get a clear consolidated picture of what’s been going on.

Here’s the query the report uses:

if exists (	select	1 
		from	dbo.sysobjects 
		where	id = object_id(N'dbo.rpt_ITSQLAgentJobs') 
			and objectproperty(id, N'IsProcedure') = 1  )
	drop procedure dbo.rpt_ITSQLAgentJobs
set quoted_identifier on
set ansi_nulls on
create proc dbo.rpt_ITSQLAgentJobs
	@From   datetime
	, @To   datetime
Version:    1.0
Date:       13/05/2009
set nocount on
declare @FromInt int
	, @ToInt int
-- In sysjobhistory Run Date is an integer in the form YYYYMMDD.
set @FromInt = cast((convert(varchar(8),@From,112)) as int)
set @ToInt = cast((convert(varchar(8),@To,112)) as int)
-- I tried a union but the servers aren't all on the same collation so that doesn't work.
-- The temp table approach gets round that.
-- server1
-- Create the temp table from the first server's results.
select	sjh.run_date
	, sjh.run_time
	, sjh.server
	, as job_name
	, sjh.step_name
	, sjh.message
	, sjh.run_status
into	#t
from	server1.msdb.dbo.sysjobhistory sjh with (nolock)
	inner join server1.msdb.dbo.sysjobs_view sj with (nolock) on sj.job_id = sjh.job_id
where	sjh.step_id = 0 --(Job outcome)
	and sjh.run_date >= @FromInt
	and sjh.run_date <= @ToInt

-- server2 

begin try 

	insert	#t 
	select	sjh.run_date 
		, sjh.run_time 
		, sjh.server collate Latin1_General_CI_AS 
		, as job_name 
		, sjh.step_name 
		, sjh.message 
		, sjh.run_status 
	from	server2.dbo.sysjobhistory sjh with (nolock) 
		inner join server2.msdb.dbo.sysjobs_view sj with (nolock) on sj.job_id = sjh.job_id 
	where	sjh.step_id = 0 --(Job outcome) 
		and sjh.run_date >= @FromInt
		and sjh.run_date <= @ToInt
end try
begin catch
	insert	#t
		, run_time
		, [server]
		, job_name
		, step_name
		, [message]
		, run_status)
	select	99999999
		, 0
		, 'server2'
		, ''
		, ''
		, error_message()
		, 0
end catch
-- Repeat the server2 bit for every other server you want to monitor
--Return results
select	*
	, case run_status
		when 0 then 'Failed'
		when 1 then 'Succeeded'
		when 2 then 'Retry'
		when 3 then 'Canceled'
		when 4 then 'In progress'
		else 'Unknown (' + cast(run_status as varchar(3)) + ')'
	  end as status_desc
from	#t
order by run_date desc
	, run_time desc
	, [server]