Heartbeat

SQL server Agent runs jobs, and if they fail to run, it can be configured to send you a message to say they have fallen over.  You can if you like configure it to send a message if it succeeds too.    Beware!  That way lies madness. 

Problem with a Job Succeeded message is that there may be dozens, even hundreds of servers.  Each server might easily have dozens, even hundreds of jobs running on them.  Once a day, once an hour, once a minute even.  So for 50 servers running 25 jobs every hour on the hour, that is a lot of emails.  You will probably delete them automatically without reading them, and so miss the one you really care about - the one job that failed last night.

So set it up to notify you of jobs that fail. 

All well and good.  But how will you know if SQL Server Agent itself has failed?  It does, occasionally. 

I set up a job on each server which runs a Job Succeeded job on each server, once a day.  At midnight, before the more significant jobs run.  It sends me a message.  I never read it. 

But I have set up rules in my email which put the messages into folders, one per server.   The job almost always succeeds, and every day, the number of messages goes up by one. 

But if SQL Server Agent ever fails on any server, the number of messages will be different.  I can see at a glance that something has gone wrong

And I can go and fix it

SSRS access denied

Nobody can run the SSRS reports I laboriously created, which rather makes life pointless.  Yet they have permissions to do so.  At the highest level.  I upgrade a user to Content Manager.  Nope.  As soon as he tries to run it, it fails. 

I'm indebted to Sajid Pandore for the solution

To see where it was denying the access, he ran this QUERY on the RS1 server

Use ReportServer
Go

select *
from ExecutionLog3
order by TimeStart desc;

It seemed that the query was being denied permission to run one specific dataset, dtsAcYear, which contains data for the Academic Year parameter.  Unfortunately, this is the first parameter the system gets to. 

Checked the permission on that Dataset – we found that the BI User group for some strange reason was not listed there.

I am not sure why it did not inherit the permissions from the higher level.  All the other datasets did!

We added this manually and tested the report all seems to be working now. 

Until the next thing goes wrong

SSIS Job Failure @P21 error

My SSIS job fails with an @P21 error message

Huh?  There is no such parameter in my stored procedure.  There are quite a few, but not 21 of the dratted things. 

The job was trying to run a stored procedure to updated changed records.  The procedure ran perfectly well in SQL server.  But not when I built it into an SSIS job.

Here's why.  Muggins here typed:
exec usp_update ?.?.?.?.?

And I should have written:
exec usp_update ?,?,?,?,?

D'oh!