No Results Found

Suppose you run a query and it comes up blank?  Now, is that because there are no matching records, or is it because you got something wrong in the code?  Obviously at first the answer is (b) - there is always an error in code.  That’s why you test and test again, and then get someone else to have a look because they see immediately the things that you miss. 

But once you have got the code working properly, and you deploy a report to SSRS, you need a way of telling people that there are genuinely no matching records that satisfy the query’s criteria.  Otherwise they might ring up and tell you that the report isn't working, and disturb your meditation, or at least make your tea grow cold as you deal with their problems.  

Here’s how: add a textbox saying “no matching records” or words to that effect.  This is SSRS 2005, but subsequent versions look much the same:

Now edit the visibility property of that textbox. 

=iif(Len(Sum(Fields!bodid.Value, “MIDAS”))>0,True,False)

In this example, Midas is the name of the dataset, and the Bodid is an integer, a unique reference for each person in the table.  (Note that the field names are case sensitive, at least in 2005.  I haven't checked 2008 - so  bodid <> BodID).  

If records are selected, the length of the sum of the Bodids will be greater than zero, and the textbox will be invisible.  On the other hand, if there are no records, the length is zero and the message in the box appears.

Gone Skiing

It’s a bit chilly in Scheffau; -25C promised tonight, Friday, so the ski shop guys say.  Nice and warm inside though.  My wife dropped my mobile outside the underground car park in the snow, a bit of a shock to the nipple when I found it an hour later and put it back in my pocket.  A good day for wearing a fur hat.  I might even think about wearing some thermal underwear. 

Scheffau is a little village at the edge of Austria’s Skiwelt, claimed to be the largest inter-connected area in the country.  More details in this recent Blog by Brave SkiMom, Kristen Lummis.  Almost all the runs are red or blue - the few blacks are not very hairy, the reds usually have an easier side to them.  

We averaged one run to one hot drink on Saturday. Plus the occasional nip from the hipflask.  I wasn’t too cold considering it was somewhere below -17C - one advantage of wearing a helmet.  But if I see a thin balaclava I might get one to keep my forehead warm. 

The sun has been trying to break through the clouds but I think it might now have given up.  Still there is always Jägertee.  This is literally Hunter’s Tea, principal ingredient hot rum, and very acceptable if you are out hunting on a cold winter day.  The locals have taken to spelling this Jagatee, perhaps as part of the German language’s recent spelling revolution.  Or maybe it’s a brand name?

It’s Sunday and the snow diamonds are sparkling in the sunshine.  -17C at the Osthangbahn lift and very chilly indeed when I tuck down into a schuss.  Maybe I should buy a Hannibal Lecter face mask?  After a day’s skiing my face is tingling... I shall need to slap on loads of after sun tonight. 

Excellent lunch at the Brenneralm above Going.  Tirolergröstl mit Spiegelei - sauté potatoes with diced ham and a fried egg on top.  Schmeckt!  It’s a big, modern, efficient waiter-service place,  but perhaps a little soulless.  Big fire in the middle, panoramic views over the mountains.  I think my favourite is still the Rübezahlalm though.   

Unfortunately I have caught an ear worm today.  It goes Ich kann dich einfach nicht vergessen, Mary Jane, tum ti tum ti tum auf Wiedersehen,  round and round in my head.  They were playing it in the Aualm bar and earworms, being ferociously catchy, get into your head and stay there until displaced by something else. 

Over the mountain to Westendorf Monday; the run down to Brixen is a bit disappointing.  Maybe it seems a bit cramped after the runs higher up - this time last year it was a narrow strip of white  between brown fields, and this year I expected better.  But it was still narrow.  Still the diamonds in the air make everything beautiful.  

Went into the self-service place at the Choralpe but the smell of cigarette smoke was too off-putting; there didn’t seem to be a Nichtraucher area so we walked out and skied down the mountain to the Gassnerwirt.  An old favourite, but they have sadly taken Käsekräuterkräpfel off the menu this year.  Great for alliteration and umlaut fans, these were herby cheesy things, a bit like Greek spinakopitta.  Had Oma’s Suppentopf instead, very nice veg soup.  Oma (granny) gives you a very tasty, generous bowl, and I’m not a big soup fan. 

 

Tuesday, and Holzfällerpfandl in the Stoagrubnhütte. - don’t you just love German spelling?  This is pre-revolutionary stuff, and who can blame the Germans for storming the barricades to overthrow the tyranny of words like that?   The Woodcutter’s Pan was a pork chop with sauté potatoes and fresh veg with garlic sauce, served in an individual frying pan on a wooden platter.

Much warmer today (Wednesday) at -6C.  I have new skis and they are a big improvement - about 10cm longer and when I put them on edge they stay there instead of wobbling about.  I suspect my technique is mostly to blame - if I was a better skier they wouldn’t wobble. 

Lunch at the Gasthof Hochschwendt, which is tucked away at the bottom of the Schmiedlalm; very pleasant and I might even friend it on Facebook as the menu politely requests, although sadly I can't find it on facebook....  Geselchtes mit Sauerkraut und Bratkartoffeln.  They call this Kassler in northern Germany - I think it would be boiled gammon in English.   There is a problem with Sauerkraut unfortunately, delicious as it is, but let’s not go into that. I didn’t know this restaurant or even this run existed - it’s on the way to Going, on the Schmiedlalm, itself off to one side of a run that I’ve always zoomed straight past under the Ellmi’s 6-er chair.  Shame that it is served by one of the few T-bars left in the Skiwelt.  Wide, varied and well-groomed like most of the runs in the Skiwelt, there’s a ski-route from the bottom of the run down to the lifts at Scheffau in the next valley, although I didn’t try going that way. 

Thursday and a mid morning stop at the Bergkaiser motorway services high above Ellmau for a strange and slightly sickly concoction called chiocco intense - made of coffee, chocolate sauce and whipped cream.  Long wait for service as everything is prepared fresh; what can it be like at busy times?  Not bad for such a big place, and very civilised loos with an escalator down.  There’s allegedly a word in German which translates as “the sense of self-loathing and emotional collapse men feel as they walk down the steps into a beer hall toilet”.  That would not apply here.  

We were heading over to Going for another visit to the Brenneralm, but got sidetracked and decided to have lunch at the Hausleiternsstube just above Ellmau.  Another restaurant new to me - it’s a tiny self-service place, obviously family run and very friendly.  I had  Kartoffellauchsuppe mit Würstl.  That’s garlicky potato soup with a couple of sausages floating in it.  Tasty, but I think I prefer my frankfurters in a roll with mustard. 

Over to the Aualm and a quick  Leberkäsesemmel mit Senf on what is nearly the last run of the week.  Liver Cheese is neither liver nor cheese - it’s not unlike Spam (cue chorus of Vikings).  It makes a very tasty snack in a roll with mustard.

And finally down the blue to Scheffau.  I usually take the black (it’s not that steep, used to be marked as a red) and will do that next time.  The blue is a narrow icy road, while the red/black is wide and uncrowded.  At the middle station the red and blue runs come together and cross over.  My advice is stick to the red, although sometimes the poor signposting makes this tricky. 

Bis nächstes Mal!

Using SSRS? Then use Table Variables

I won't go into the difference between temp  tables and table variables - here's a blog which does this for me:

Temporary Tables versus Table Variables

and another one:

What's the difference?

The way I read these descriptions, there is little to choose between the two techniques. They certainly look  very alike - here's the grammar to create a temporary table, pretty much the same as creating a regular, permanent table except for the # character:

--create temporary table
create table #MemscalesA
(
bodid               int,
familyid            int,
strForename     nvarchar(100),
strSurname       nvarchar(100),
coverid             int,
strCover           nvarchar(100),
IsMember         tinyint,
IsSpouse          tinyint,
IsDependant     tinyint
)

So when Microsoft announced table variables,  for SQL Server 2000 I think it was, this revolutionary concept must have stunned and amazed the community:

-- declare table variable
declare @MemscalesA table
(
bodid               int,
familyid            int,
strForename     nvarchar(100),
strSurname       nvarchar(100),
coverid             int,
strCover           nvarchar(100),
IsMember         tinyint,
IsSpouse          tinyint,
IsDependant     tinyint
)


Wow, eh?  Sit back and catch your breath.

I'm being a bit unfair,of course - there are differences, and they depend on what exactly you are doing with your tables. Dimitri Tsuranoff puts it well (in the first link above) when he says "There is no universal rule for when and where to use temporary tables or table variables. Try them both and experiment."

So I was a bit surprised when I found that there was a circumstance when you MUST use table variables if you want your code to work.  It's if you want to use it in an Reporting Services report.

My colleague wrote some code to find people who had purchased unsuitable  insurance cover (single people with family policies, families with policies excluding dependants, that sort of thing.  Chances are that their circumstances changed after the purchase, but obviously it isn't right and the business wanted to put it right).  He did it using temporary tables and it worked like a charm.  Muggins here then tried to set up an SSRS report so that it could be delivered automatically to the people that needed it every month.  It plain refused to work.  

Your report will work fine whichever technique you use, while you are building it.  But SSRS almost certainly uses a different permission to you - and crucially, that permission is something like a db_datareader - it can read data, but isn't allowed to create a table, permanent or temporary.

Fun with data types

Are protected underwriting terms applicable?  (Never mind what it means).  The answer is either Yes or No – they either are or they aren't.  No maybes possible, no special situations - so the perfect data type would be Boolean - a value of 1 means True, and 0 means False.  Named after 19^th century mathematician George Boole, who presumably worked out that the opposite of True was in fact False.  Isn't maths wonderful?

Unfortunately, in SQL Server, there is no Boolean data type.  This might be something of a setback.  Still, the nearest thing that can be used in place of Boolean data is the BIT data type, which is an integer data type that can accept a value of 1, 0 or NULL value only.  And it only uses up one bit so it is very efficient.  

So, problem solved, I hear you say?  Ho, ho, ho; that is hollow laughter echoing round the IT crowd’s squalid basement.   Because 1 is fine for Yes, but 0 won’t do for No.  Oh dear me no - the bulk upload software to which I want to transfer data expects either 1 or blank.  It could be changed of course, but that would mean months of development and testing. 

Hokay, how else can we do this?  Tinyint, Smallint and no doubt Int and Bigint all implicitly convert the desired blank into a 0.  So no joy in that direction. 

A fixed size character string?  So I tried it as char(1), and did a CASE statement to specify ‘1’ for Yes and “ for No.  But that changed ‘’ to ‘ ‘ in other words, a space or ASCII code, er, 32.  Not the same thing at all, as far as a computer is concerned.  I asked for a single character – I get a single character – a space is a character, it just happens to be an invisible character. 

The answer was to use a variable length character string – the varchar datatype is usually used for strings that can vary greatly in length.  Names, for example.  You don’t know who is going to order a widget next – could be Jim Lee, could be Felicity-Jayne Anstruther-Gough-Calthorpe.  Using a varchar allows the length to vary – from 0, which is what we want, to 1, so it’s now varchar(1), which looks bizarre but works!  There’s a length overhead to using a variable character field, but we can live with that. 

I still think it should be Boolean!

How to Identify Twins

I'm trying to find a list of twins in my source database - reason being, twins share a surname, a date of birth, and a postcode.  Unfortunately, those are exactly the criteria that the destination database uses to identify and reject duplicates <sigh>.  So it's handy to have a list of twins beforehand and do some manual jiggerypokery to make things work as desired. 

(In an ideal world, perhaps our skilled team of Oracle experts would modify the destination database so that it considered the forename as well, and said to itself "Hey, twins!  Maybe I won't reject this guy after all."  But that puts us into a world of development changes and testing and pain - it's easier and quicker to leave the damn thing alone and manually wrestle with the input data.)

So the first step is to identify surnames which occur more than once in the data.  Notice that I'm grouping by Surname and Date of Birth:

      Select mb1.surname

      From   tblbods    mb1

      Group BY    mb1.surname, mb1.dob

      having count(mb1.surname) > 1

Which gives us:

Surname

SMITH

JONES

FORTESCUE-SMYTHE

MURPHY

MCDONALD

PATEL

Actually it gives us an awful lot more than this - there are 200,000 records in the table.  But I thought you would get bored if I listed more, plus I have had to change all the names and dates of birth so as to anonymise the data. 

Second step is to do the same for dates of birth which occur more than once.

      Select mb2.dob

      From   tblbods    mb2

      Group BY    mb2.surname,      mb2.dob

      having count(mb2.DOB) > 1

DoB

10/05/1993

11/06/1996

11/06/2007

25/12/2005

11/07/2010

14/02/2001

And then finally I want to select data where the surname is in the list of surnames from Step 1, and the date of birth is in the list of DoBs from Step 2.

Select

      mb.BodID, mb.Title, mb.Forename, mb.Surname,

      mb.relationship, mb.sex as Gender, mb.DoB

from

      -- Table of individual members

      tblbods     mb

where  mb.surname in

(

      -- Step 1 - surnames which occur more than once

      Select mb1.surname

      From   tblbods    mb1

      Group BY    mb1.surname ,     mb1.dob

      having count(mb1.surname) > 1

)

and mb.dob in

(

      -- Step 2 - Dates of Birth which occur more than once

      Select mb2.dob

      From   tblbods    mb2

      Group BY    mb2.surname,      mb2.dob

      having count(mb2.DOB) > 1

)

And the end result is:

BodID	Title	Forename	Surname	relationship	Gender	DoB
523189	MISS	MARIA	SMITH	Child	F	11/07/2010
523190	MISS	ALANA	SMITH	Child	F	11/07/2010
531637	MR	JASPER	FORTESCUE-SMYTHE	Child	M	14/02/2001
531638	MR	SIMON	FORTESCUE-SMYTHE	Child	M	14/02/2001
466662	MASTER	RAHUL	PATEL	Child	M	10/05/1993
466664	MISS	SAMIRA	PATEL	Child	F	10/05/1993
519007	MASTER	JOSEPH	MURPHY	Child	M	11/06/2007
519008	MISS	ANNETTE	MURPHY	Child	F	11/06/2007
339731	MISS	PATRICIA	JONES	Child	F	11/06/1996
339736	MISS	IMOGEN	JONES	Child	F	11/06/1996
419768	MASTER	WINSTON	MCDONALD	Child	M	25/12/2005
419769	MASTER	DOUGLAS	MCDONALD	Child	M	25/12/2005

Simples!  Hope you find this useful.

Job Hunting - Preparing for the Interview

So you've read my blogs on doing up your Linked In profile, and job hunting - and all this good advice has worked!  (Or at least helped a bit).  You have an interview.  Are you ready for it?

Part of it is getting organised.  If you are looking, there's a fair chance that you will get a few interviews.  If you get them mixed up, this will be a bad thing - so you need to get organised, don't play it by ear.  Start by creating a file for the Employer.  I like to use clear plastic folders, but whatever you find in the cupboard will do.

Then a cover sheet.  Put the Company name in big letters, then the job title.  You've probably come through an agency, so put down the agency name and the bloke's name.  He will want you to ring him after the interview to discuss how things went, so you might as well ring the right bloke.  Then the name of the interviewer and their job title - the agency should give you this in advance.  It's handy to have all this stuff on one sheet of paper, rather than have to search for it.  Let me know if you want a copy and I'll send a copy of mine - this site doesn't seem to allow me to add stuff for you to download.

Add your CV to the file - it got you to the interview, they will probably want to ask you questions about it - you might think you know it off by heart, but take it just in case.  Maybe the recruitment agency has sent an old version, maybe the interviewer has left yours on his desk and brought someone else's.  If you have a copy, you look efficient.  If you don't have one, you might find yourself struggling.

Make a list of any holidays you have booked over the next three months - type it up nicely, hand it to the interviewer or the HR bod if there is one - it shows them that you are efficient and thinking about their problems. I once had a holiday booked, but no-one asked me about holidays and I forgot to mention it - it caused a lot of grief when I told them about my long-planned holidays a couple of weeks after I started work.  If you give them a list up front, they can't quibble about your week on the beach when you start the job, or God forbid!  ask you to cancel.

Put together a list of good questions for you to ask when it's your turn.  They ask you questions, and during the course of the interview you'll probably ask them a few back - but it's worth having a few good questions to ask at the end when you want to leave them with a good impression of you.  Maybe something that shows you have checked out their website, or their competitor's website - "I see your competitor has just brought out X - how is this likely to affect this business?"  A good last question might be "If I was successful, when would you need me to start?"  But build a list of good questions (the recruitment agencies have samples on their websites) and keep adding to it.  Google has 14,000,000 pages of "good questions to ask at interview" - some are better than others, and some are very cheesy indeed.  Prepare a half a dozen that you are comfortable with in advance - that way your mind doesn't go blank.  It's probably OK to refer to your list - again, it proves you have prepared for the interview.  But just ask a couple of good ones - reading 20 questions off your list will not be helpful.

All that stuff above is going to be pretty much the same for every interview you go to.  Now for the stuff that applies to the specific job.  Start with the letter or email inviting you to attend - you might need it.  Then the job spec that the agency sent you.  I like to go through it, ticking the things I know on the Essential and Desirable lists.  Don't panic if they specify something as essential but you can't in honesty tick it.  Maybe it isn't that essential, but most likely the other seven things you have ticked outweigh the one you can't.  Be ready to ask them about it - get your attack in first - "I see you are looking for someone with X, whereas my experience is in Y - is this likely to be a problem?"  The answer's probably not - or they wouldn't be wasting their time talking to you.

You know the name of the interviewer - look them up on Linked In and print it out, stick it in the file.  You get a picture, some background history, current and previous jobs, groups they are members of - chances are you will find some common connection or interest you can mention.  Don't be a creepy stalker though!

Check out the company website.  Check them out on Google as well - maybe they have been in the news recently.  Print out anything that you think is interesting and add it to your file.  Maybe cut and paste stuff onto a one page summary - you are probably not going to remember tons of corporate waffle.  I'm not a fan of Twitter but it doesn't hurt to look and see what people are saying.  Good or bad, it might lead to a good question to ask.

The agency will probably give you advance warning if there is going to be a technical test.  You can study for this - Pinal Dave has a good list of SQL Server Questions, and unlike others he gives you the answers too.  But remember that the test isn't trying to catch you out.  And in the great scheme of things, it doesn't matter very much how many Pages there are in an Extent*.

You are going to have to wear a suit and tie, or the female equivalent, especially if you are female.  It's far better to be overdressed - if the interviewer is in jeans and a tee shirt, you can take off the jacket, maybe loosen the tie.  But if you are in jeans and a tee shirt, you can't suddenly get less casual if you need to.  I was nearly caught out once - I knew the firm had a casual dress culture, so nearly went along in my chinos.  Luckily I stuck to my suit and tie, because when I got there it turned out that interviews were very formal indeed.

Check out where the place is - if you can go the day before and scope out the route and the parking, do so.  In any event, get to the place early on the day and spend a few minutes skimming through your file.  Take a few deep breaths.  And go in there and do your best!


* Eight.  Now you know.

More on setting up a DTS package

Remember I was talking about setting up a thing to import stuff from a spreadsheet into SQL Server?


Well, there are a few more wrinkles worth mentioning.


First thing was that when I first tried to run it, there was a problem.  The Data for the Source column is too large for the specified buffer size.  Here's the error message:

Ah?  I don't remember specifying a buffer size.  Well, not surprisingly, because I never did - what's more, as far as I can tell I can't get at the buffer size to change it.  It seems that by default DTS reads the first eight rows of the spreadsheet and works out how big the buffers and field lengths need to be, based on what it finds there.  Unfortunately, the data in this particular field reads something like "Yes" or "No" until we get down to row number 172 where someone has written an essay detailing the sad and complex story of one particular Hospital. (Don't get me started on database design and normalisation - let me just say that not everyone in the past adopted best practice - grrrr!)

The quickest way round this is to take row 172 and move it up to the first eight rows.  This works fine, and the data gets loaded.  But I'm not free to interfere with the master spreadsheet.  (Perhaps I could copy it and sort the copy?  I may come back to that)  That also means I don't want to put a dummy row up at the top with a 2048 character field in it.  (http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/5e4f90d7-b84c-422b-bbd1-35481ae3d1ba/)

Apparently I can edit the registry so that it calculates buffer sizes based on more than the default eight rows.  But I'm leery of mucking about with the registry, and anyway it may be that someone else needs to run the package, not me. 

It might be possible to read in the field in chunks of 255 characters until the source field is empty But that is getting into something that's too heavy duty for me.  Maybe someone cleverer than me can figure it out and post the solution.  

The obvious answer is to find out where the buffer size is specified as 255, and change it to something larger.  But as far as I can see, it isn't possible to change this in DTS.  

One entry in Google suggested that I might need a new service pack?  http://support.microsoft.com/kb/247527 - nope, already got it.  


Had you guessed already that I've been researching this on Google?  I also tried asking the question on the Linkedin SQLDBA group.  I got some good answers but not THE answer, unfortunately.  So I suppose this blog post is really about problem solving.  You know, that favourite interview question - "Right, Jack, tell me about how you would go about solving a problem" and you think, Er...

First thing is research - Google finds lots of posts where it has been done, or something like it has been done, but usually not quite the same thing.  LinkedIn groups helped, but no magic bullet.  One of my colleagues vaguely remembered coming across something similar a couple of years ago, but couldn't really remember what he did to solve it <sigh>.  But it's always worth asking around, because there's a fairly good chance that someone will know the answer.  

Then, evaluate the options.  I did try moving row 172 up to the top, and it worked like a charm.  But it isn't my spreadsheet to modify, and there is no guarantee that the author isn't going to write a long and complicated explanation in row 207 next week.  So that was a no go.  

Then - decision.  I went for editing the registry in the end.  There's an article here on how to do it.  You change the permitted value for the TypeGuessRows variable in the registry so that DTS reads all the rows in the spreadsheet. Unfortunately, this means that the DTS package will only run on a machine which has been so modified. On the other hand, maybe this means that I have a job for life?  Yeah, right.

And Test.  Yep - it works.