Monthly Archives: June 2009

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.

 

I Love row_number()

I think the row_number() function is one of the best things that’s been added to SQL for some time. If you’re not familiar with it you really should check it out. It’s very useful where you want to join a set of data to another set detailing the latest/biggest/etc something related to the first set. Imagine top (n) and group by combined. The 5 most recent orders from each customer. The person who has used each repoort the most. You get the picture.
It adds a sequential number column to a resultset. The syntax is

row_number() over ([partition by ...] order by ...)

Order by (required) specifies what order you want the rows numbered in. Partition by (optional) controls when the numbering should start back at 1 again.

Suppose you want to list your customers and the value of each one’s latest order. You could do it like this:

select	c.Name
	, (select top 1 o.OrderValue from Orders o where o.CustomerID = c.CustomerID order by o.OrderDate desc) as LatestOrderValue
from	Customers c

Not too bad in a simple example but it can get really convoluted really quickly in the real world and then you pass that magic cut-off point where the query optimiser suddenly gives you a bad plan.

This is the same thing with row_number():

select	*
from	(
	select	c.Name
		, o.OrderValue as LatestOrderValue
		, row_number() over (partition by o.CustomerID order by o.CustomerID asc, o.OrderDate desc) as RowNum
	from	Customers c
		inner join Orders o on o.CustomerID = c.CustomerID
	) x
where	RowNum = 1

It looks a bit heavy but it clearly expresses the intention and stays readable when the query gets more complex.

Quick Info About an Object

If you highlight the name of an object in a query window in SQL Management Studio and press Alt+F1 you instantly get a set of results showing almost everything you could want to know about the object: columns, parameters, datatypes, constraints, indexes, storage details. This works on your own tables etc. not just system objects. Forget scrolling around in the Object Explorer.

What’s actually happening is that the system stored procedure sp_help is being executed (with the highlighted text as the parameter value) because sp_help is bound to the keyboard shortcut Alt+F1. You can see and customise these query shortcuts in the Environment / Keyboard part of the Options dialogue (menu Tools / Options).

Really obvious once you know it’s there, but it hasn’t been very well publicised!

Who’s running your reports?

Reporting Services doesn’t give you any tools to monitor report usage. Who’s running these reports? How often? When did they last use them? Here’s how I find out. (Surprise surprise – it’s another report!)

I’ve developed over 130 Reporting Services reports and I want to know how much they’re being used and by whom. They run off a dedicated reporting database that contains summarised data extracted from the OLTP databases so I’m reasonably well insulated from schema changes. (I highly recommend this approach – it’s saved me so much trouble). But whenever there’s a significant change to our systems I have to assess the impact on the reports. I need to know who to talk to when I need a decision from the business on how to handle a change, and I don’t want to waste time maintaining reports that aren’t being used.

I have two reports, one showing the last use of each report and the other to drill into the usage history of a specific report. Here are the queries I use:
I use this one to provide the values for the next query’s @DataSourceID parameter:

create proc dbo.rpt_ListReportingDataSources
as

/*
----------------------------------------------------------------------
Version:  1.0
Date:     14/05/2008
----------------------------------------------------------------------
*/

set nocount on

select	cast(null as uniqueidentifier) as DataSourceID
	, 'All' as DataSourceName
union all
select	ItemID
	, [Name]
from	ReportServer.dbo.Catalog dscat with (nolock)
where	[Type] = 5

go

This one lists who last ran each report and when:

create proc dbo.rpt_ITReportsSummary
(
    @DataSourceID uniqueidentifier = null
)
as

/*
----------------------------------------------------------------------
Version:  1.2
Date:     14/05/2008
Purpose:  List all reports with who last ran each one and when.
----------------------------------------------------------------------
*/

set nocount on

select	r.Path as FullName
	, r.Name as [Name]
	, dscat.[Name] as DataSource
	, runcount.RunCount
	, runcount.FirstRun
	, lastrun.LastRun
	, lastrun.LastRunBy
	, mostrun.MostRunBy
	, r.ItemID as ReportID
from	ReportServer.dbo.Catalog r with (nolock)
	inner join ReportServer.dbo.DataSource ds with (nolock) on ds.ItemID = r.ItemID
	inner join ReportServer.dbo.Catalog dscat with (nolock) on dscat.ItemID = ds.Link
	left join 
	(
	select	ReportID
		, count(*) as RunCount
		, min(TimeStart) as FirstRun
	from	ReportServer.dbo.ExecutionLog with (nolock)
	where	UserName <> '<domain-name>\davidwimbush'
	group by ReportID
	) runcount on runcount.ReportID = r.ItemID
	left join 
	(
	select	ReportID
		, TimeStart as LastRun
		, UserName as LastRunBy
		, row_number() over (partition by ReportID order by ReportID asc,TimeStart desc) as SeqNo
	from	ReportServer.dbo.ExecutionLog with (nolock)
	where	UserName <> '<domain-name>\davidwimbush'
	) lastrun on lastrun.ReportID = r.ItemID
	left join 
	(
	select	ReportID
		, UserName as MostRunBy
		, count(*) as UserRunCount
		, row_number() over (partition by ReportID order by ReportID asc, count(*) desc) as SeqNo
	from	ReportServer.dbo.ExecutionLog with (nolock)
	where	UserName <> '<domain-name>\davidwimbush'
	group by ReportID
		, UserName
	) mostrun on mostrun.ReportID = r.ItemID
where	r.Type = 2 -- Report
	and (@DataSourceID is null or ds.Link = @DataSourceID)
	and isnull(lastrun.SeqNo,1) = 1
	and isnull(mostrun.SeqNo,1) = 1
order by r.Name

go

This one lists everyone who ran a specific report and when, using the report ID from the previous query:

create proc dbo.rpt_ITReportHistory
(
    @ReportID uniqueidentifier = null
)
as

/*
----------------------------------------------------------------------
Version:  1.2
Date:     14/05/2008
Purpose:  List who ran this report and when.
----------------------------------------------------------------------
*/

set nocount on

select	el.TimeStart as WhenRun
	, el.UserName as RunBy
from	ReportServer.dbo.ExecutionLog el with (nolock)
where	el.ReportID = @ReportID
order by el.TimeStart desc

go