Monday, 19 March 2018


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 send 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

Friday, 9 March 2018

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

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

SSRS 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 ?,?,?,?,?


Tuesday, 4 July 2017

Sometimes Database Administration Isn't Challenging Enough

First, we wrap the "hole guy's" arm in a skin for protection

Then we find a big hole and the "hole guy" crawls in.

We use modern lighting...

There it is.

Those must be eggs.

I let it take my protected arm, sort of like noodling for fish.

Then my buddy pulls me out with the snake attached.

Ain't it a beauty?

It will feed the whole village for a while.

Snake Noodling - - - - - What real men do!
Maybe standing in line at the grocery store isn't as bad as it seems!!!

Tuesday, 6 October 2015

The Pointless Job

The job failed.  It had been working forever and suddenly it wasn't any more.  There was of course no documentation - well you knew that.  There was no description in the job description.   It ran an obscurely named stored procedure.  

Okay, the SP connected to an entirely different server, extracted some data from a database that didn't exist, and sent it to an unknown FTP server.   Well, extracting something from a database that doesn't exist (having been dropped yesterday) probably explains why the job was failing today. 

The database in question was for a client we stopped working with about five years ago.  And looking at the history it seemed likely that we had been extracting nothing for those five years, encrypting the nothing, and sending it to the client that we no longer worked with.   Somewhere in the former client's IT department there must be a bod wondering about his error message...

Asking around, one of the developers remembers the customer wanting us to encrypt an empty file and ftp it to them, even though the customer had no business for us.  Something about keeping a gateway open just in case...

Saturday, 26 September 2015

Those Pesky Recruitment Consultants

Let me tell you about Vic* - he is a recruitment consultant, of course, in other words a salesman.  I used to be a salesman, so I know a bit about what goes on.  This all happened a couple of years ago, but I doubt if Vic has changed much.

Vic had a chat with me, got my CV, and put me forward for a job.  But he didn't actually get round to telling me that he was putting me forward, nor did he send me a job spec, nor did he tell me who the client was, which is a bit naughty.  But, you know - sometimes you have to be a bit proactive in this life, and enthusiasm counts for a lot.

Which was fine until Bob* called.  Bob had a chat, got my CV, told me about his client, sent me the job spec, gave me the client's name, and I agreed that Bob should put me forward. 

You see where I'm going, don't you?  The client wisely didn't want to get involved with a nest of vipers. 

Then Vic had another go, on a new job a few months later.  He told me about the client - Joseph Bloggs International*, got my CV, told me he was putting me forward.  Lessons learnt - well done Vic!

Two or three other agencies contacted me about Bloggs -I told them I had already been put forward, and they shrugged their shoulders and moved on.

Then - nothing.  No interview, no feedback.  Nothing.  I shrugged my shoulders and moved on. 

Until I met Susan* - she was interviewing me for another job.  Susan was the outgoing DBA, and she was leaving to go to a firm called Joseph Bloggs International.  Nice step up for her, really interesting job.  Yes indeed.  Except looking at her profile she actually wasn't so well qualified as me.  And although I'm sure she will be great at Bloggs, they really should have interviewed both of us before choosing her. 

Vic had done it again.  He got his hooks into me, but didn't land the contract with the client, so didn't actually put me forward.  Hence no feedback. 

So advice please - as a mere candidate, what am I supposed to do when an agency rings me?  If you work in recruitment please tell me the secret formula that renders consultants powerless to tell me big fibs.

*Names changed to protect the guilty


Sunday, 26 July 2015

So farewell then Hartkaiserbahn.

The Hartkaiserbahn was the funicular railway that took skiers up from Ellmau to the Panoramic restaurant.  And on into the Skiwelt.  Here's a photo which I scrounged from John Barr, the Austria Ski Guide, of the funicular in the 1970s, when his mum and dad went there on their summer holiday. 

And here's one I took in March 2015.  It's just coming down into the bottom station to collect me.  And a few others, of course.

And they have spent the summer of 2015 removing it.  (The ski lift people I mean, not John's mum and dad.)

It's progress, of course.  They are replacing the old funicular, dating from 1970, with a state of the art gondola

Here's a photo from the Skiwelt site of the crane removing the carriage.  

It was a bit of a dinosaur.  Pretty much every time I used it, it was horribly cramped, standing room only, rather less than user friendly.   You had to climb up a set of steps to get to the top of the carriage, clumpitty clump, then fight your way in.  If you were anything less than 1.6m tall you would struggle to stay upright as you probably wouldn't be able to reach the hanging straps.  

A nightmare for the maintenance engineers, parts would have to be made specially to order if anything ever went wrong. 

The track went up the mountain.   The track came down the mountain.  And halfway up it split, the two carriages thundered past each other, and then back they went onto the single track again.  Scary stuff. 

The new one promises to be far more comfortable, quicker, double the capacity, heated seats, hot and cold running wifi, the best thing since sliced bread. 

And yet...

And yet, it's a sad day for the history of skiing. Excuse me while I wipe a little tear away.