Wrestling with Email

I get lots of them, so do you.  I get far more at home than I do at work, because I own or moderate about 20 Yahoogroups, and am a member of 20 more.  Plus all my LinkedIn stuff goes to my home email.  So how do I deal with them?

To start off, I use a thing called Tonsho to virtually eliminate spam. New senders are challenged which means that if there isn't a human sending it, it doesn't come near me. And the other good thing is that it's free (they make their money out of charging to send ginormous emails). 

Then I have rules set up to sort email into appropriate folders. If the subject is Project X, it automatically goes into the Project X folder - where I can get to it right after I finish Projects A to W <g>. Outlook automatically shows me how many unread messages I have in the Project X folder. At home my [Live-In-France] folder contains all the emails for that particular Yahoogroup - this lets me prioritise.  

Read and decide what to do with the Inbox - skim through it with your finger on the delete button.  If it needs a quick reply, then reply.  If it needs further thought, tag it and add a date for further action.  If it's something I am likely to want to keep, I move it to an appropriate folder (Useful Technical Stuff, maybe) 

I found this post Email Etiquette for the Super Busy by Jocelyn K Glei with some good advice:

Never send an angry or contentious email.  Put a five minute delay on your outgoing mail by setting up a rule in Outlook.  That gives you a chance for second thoughts - five minutes delay won't hurt - although it can seem like a long time when you are waiting for the messages to be sent!  

Be concise.  I don't use my Blackberry for email (I have work email at my office and home email at home, I don't need it in between as well).  But if I did I would be a bit peeved to get three page messages to read on a 5cm screen.  

Include deadlines - if there's a deadline, it's good to know that.  Burying the deadline at the bottom of the message is not as good as putting it at the top.  

Don’t send “Thanks!” emails.  Interesting advice. I usually try to say thank you, for example to people who accept my invites to link in. But Jocelyn argues cogently that I should NOT. That may be a step too far away from the human-human interface for me, but you can always say Thanks in Advance.  

Back Up Over Network

Microsoft in its wisdom assumes that backups will be held on the local server e.g.

• Data on Drive D:
• Logs on Drive E:
• Backups on Drive F:

What happens if the local server fails?  Ooops. 

We back up to disk and then to tape (belt and braces) - we've had a few less drastic problems than server failure, mainly because we have run out of space on the local machine because some databases have grown a bit bigger than was initially intended.  It's a bit fiddly to back up to tape from 30 different servers too.  So to resolve these problems we decided to back up over the network (BUON) to a central media server.    This makes the process of backing up to tape faster and more reliable.  Our server bods reckon they saved about 900Gb of space on local servers.  And just in case there is a problem with the central media server, we can fall back to an alternative.


This blog is about SQL Server 2005 and 2008.  The technique is a bit different for SQL Server 2000 - let me know if you want this and I'll send you my notes.  But you're moving off 2000 now, aren't you?

Here's how I did it:

Set up a maintenance plan called BUON ServerName System Maintenance Plan (or whatever you like, it's just a name).  I like to set up one plan for System databases and another for User databases. 

a)      Schedule plan for 19:00 daily (for us, most people are finished by 18:00 and the tape backup occurs at 02:00 so this should give it plenty of time, even if the first attempt fails and it has to take another backup).  Don't forget to set up a schedule, otherwise your colleagues will mock you. 

b)      Check Integrity.  There's a nice new thing in SQL Server 2008 that lets the maintenance plan ignore a database if you have taken it offline - tick it.  If you take a database offline, then the maintenance plan carries on happily without whingeing at you that the database is offline.  More for user databases than system, admittedly. 



c)      Reorganise Index; ignore if offline

d)   Backup with destination \\acbriap024\SQL_Backups\<servername>

Here's the meat in the sandwich.  ACBRIAP024 is the primary media server, and SQL_Backups is a shared drive on that machine.  I set up a directory on that share named after the SQL Server instance I want to back up. 

System databases get a .sys.bak extension, User databases get usr.bak. 

And I always tick the Verify Backup Integrity box - it takes a bit longer because it runs a "Restore Database with Verify Only" command after taking the backup, but it's a useful precaution. 

e)      Maintenance Cleanup - clear out backups older than 2-3 days

f)      If step (d) fails, here's the fall back position - backup with destination
\\acbriap023\SQL_Backups\<servername>
This is the secondary media server - the belt and braces machine. 

g)      Maintenance Cleanup

Here's what it looks like:

Notice the red line? Red for failure - if you right click on the line, you can specify whether you want something to happen if the previous step succeeds (green) or fails (red) or just completes regardless (blue). In this case, if the backup to the primary server works, it just tidies up the primary server by deleting old backups and all is complete. If the primary backup fails, then it goes to the secondary backup.

If you want more detail on the individual tasks in a maintenance plan, check out this estimable free download from Brad McGehee.  Brad lives the dream and administers databases in Hawaii, so he must be right!

Telesales Ju-Jitsu

Telemarketers - don’t you just hate them?

Well, up to a point.  When I’m at work, I’m fairly cool about someone who rings up and tries to sell me something.  This may be because it doesn’t happen all that much in my present job and it provides a welcome relief from the slog of administering databases, and maybe because business to business sales are better targeted and have at least some chance of interesting me.

At home though, I’m registered with the Telephone Preference Service which pretty much eliminates calls from reputable companies.  That means that anyone who still tries is not a reputable company.  If they ring up, they want to sell you something dodgy.  So they are fair game for a bout of telesales ju-jitsu.  String them along, and revel in the pleasure of stringing them along.  See how outlandish you can be.

Gavin rang me up all the way from India to tell me about the terrible virus problems I was having on my computer.  Obviously hoping that I didn’t know a computer from a pile of rocks.  I think he wanted me to type in a website URL which of course would pretend to scan my system and Shock Horror!  find a virus.  Not sure if he wanted to sell me an anti virus system, or if he would just run the "scanner" again and miraculously have it clear the problem. Or it may be worse than that and they empty your bank account - never got that far - scam update here.  

Anyway, I was all ears, asked Gavin to help me.  He asked me to type in the URL and I asked him what that was.  So we discussed the Start button and of course I don’t have one on the screen.  After about ten minutes of him getting increasingly frantic as none of the things he tried to get me to do worked, he asked me how I logged on to the internet.  I told him I didn’t have internet access on my PC.  Ha ha!

I told an energy salesman who wanted me to switch to his supplier that I had an experimental cold fusion reactor in my basement which generated all the power I needed, and asked him if his company would be interested in buying my surplus energy.  Still waiting to hear back.

And I told a shares scammer that I was part of an anarcho-syndicalist commune that believed in holding all property in common, and that capitalism was a jackboot on the neck of the people, and share ownership is a weapon used against the working class, etc, etc, until he gave up.

I look forward to telesales calls now...

Time Management for the DBA

I find Outlook is very good for time management.  I dare say there are other programs out there which do much the same, and maybe even do it better, but the chances are that Outlook is already on your desktop for emails, so you might as well use it for Tasks as well. 

Create a task for everything you have to do – transfer all the post-it notes, scribbles in the corner of a meeting agenda, stuff like that, to Outlook tasks.  Usually one line is all you need, maybe even just a couple of words.  Sometimes you want more than that – Outlook has a details box that is pretty much unlimited in size (as far as I can tell).   I think it might fall over if I tried to put more than one chapter of my novel in there...

Suppose someone sends you an email asking you to do something a couple of weeks from now.  Rather than transcribing it to your Tasks, you can right click on it, select Follow Up, then hit tomorrow, next week or custom (to put your own date in).  Then on the day you select, it pops up with all your other tasks for that day.  In the meantime, you can forget about it.   

The same with tasks - I have a database that the HR department haven't quite finished with.  (They buy a million dollar PeopleSoft HR system, all shiny bells and whistles, but they still want to keep the old clunky one just in case people ask them for the data it contains.  And they don't want to transfer it to the new system because that would cost money, time and effort.)   Anyway, in three months time I have it scheduled for review.  It will pop up on my list of things to do, I'll stroll upstairs and ask the HR bod if he still wants it, he will hum and haw and say yes, better keep it for a while longer, and I will change the date to remind me in another three months time.   No worries.

Some things recur – weekly, daily, monthly, annually, whatever – Outlook lets you set up a recurrence and they will automatically come round again.  Even things you do routinely every day – put them on your list because then you can tick them off and you have evidence that they have been done.  Dead easy. 

Then you can specify a high, regular or low priority – I usually leave them all at regular unless something really is high.  If everything is High priority, then that’s regular. 

If you want to prioritise in a more granular way, put a number at the beginning of the subject  - for example: #1 Check backups.  Outlook will sort by date, by priority, and by subject.  Remember the days when computer programs had lines in them?  You could say 10, 20, 30 instead of 1,2,3, which makes it easy to insert a #15 task if you want to.  I don’t bother, I’m not quite that anally retentive. 

It’s now under control and you can choose what you want to do next.  Usually you do the high priority things (if any) and #1 things first, then #2, then #3, but that’s up to you.   I have some #9 things on my list which I sometimes do because I’m in the mood.  #9 Check out ski webcams is a good one to do at lunchtime

If you decide something can wait a week, change the date – Outlook will remind you about it next week.

When you finish a task (because you are just organising yourself here, you still have to actually do stuff) you can use the details box to record what you did.  Tick the box that says Completed.  Why?  Because you now have a record of what you were meant to do, what you did and when you did it.  If the boss asks you for a list of achievements, just give him your completed tasks list. 

You can assign a task to someone else – you’ve done your bit, now you can pass it over.  Or maybe you can delegate something you do regularly to the new Junior DBA – that would be nice.  Outlook will tell you when the new guy has completed the task. 

And another thing – at the end of the day, you can look at the task list and have the satisfaction of seeing that you have done wodges of stuff.  You’ve done the most important things, or at least made a start on them, and moved what's left to tomorrow.  You’ve done the daily recurrences which are probably fairly important or you wouldn’t be doing them every day, would you?   And your list is now clear.  Go home with an easy conscience.  

Social Networks for the DBA



Multimillionaire



There are a lot of social networks about.  I gave up Facebook because I got fed up of people throwing sheep at me.  I managed to solve one Facebook mystery – the reason that all these gorgeous Russian babes wanted to be my friend.  If you look up Jack Whittaker on Google you will find that Jack Whittaker of Culloden, West Virginia won $315 million on the state lottery – he even looks a bit like me! 



Babe Magnet

I can’t get on with Twitter – I don’t have anything to say to the world in less than 140 characters.  Neither do most Twitter users, but they just say it anyway. 

So – LinkedIn, then.  It’s rather more adult than most social networks.  The idea is that you can connect to other professional people, learn what they do, give and get advice on serious and not-so-serious subjects, look for jobs, maybe find potential new members of staff. 

Start with your profile.  Click here to see mine:  http://uk.linkedin.com/in/jackwhittaker

Essentially it’s your CV online – what you do, where you work, how long you have been doing it.  You can say as much or as little as you want, you can put all your past jobs in there in or just your current one.  Make sure you say the key words that matter, as this is what people look for.  For example, if you want people to know that you are familiar with SQL Server 2008, make sure it’s in your profile, or your Specialties (sic - sorry about the American spelling). 

Then you want to make connections.  Start with the people you work with – if they are already on LinkedIn, you can send them a request.  Most people are happy to connect to you.  I have 20 or so pending requests - possibly people who wish to deny knowing me, but I suspect really they don't look at Linkedin very often.

Here's some advice that I found useful - it's ten mistakes, so just do the opposite!

Here’s why – suppose I was an employment agency, looking for a French speaking DBA in Bristol.  Select Advanced Search, put in the keywords DBA and French, put in the location, and it finds 30 DBAs for me with at least some knowledge of the language.  I can look at those people’s CVs, connect to them, send them a message if I want.  And it’s free.  Now that’s much more attractive to an agency than paying 20 grand a year to access the job sites. 

From my point of view, the more connections I have, the easier it is for them to find me, and the closer I am to the top of the list.  I have 200 or so connections; those people in turn know 40,000 other people; and the 40,000 know 3,000,000 or more.  So I am three degrees of separation away from all those people.  I may not know the man in Mauritius at the other end of the chain, but I know someone who knows someone who knows him personally – and that means I can get in touch with him directly if I want to.  And if he happens to be looking for a French-speaking DBA to work on a tropical island paradise, he can get in touch with me. 

Helpful hint - if you ever have anything to do with an employment agency, always send them a request to link in - they usually have several hundred contacts.

The other thing that is worth doing is to join groups which are related to your professional interests - so for example, go to groups, type in Qualitative Data Analysis, and it finds three groups.  Join up, read the discussions, contribute.  If someone sounds sensible, tell them so and ask to link to them. 

We were arguing in the office about whether it's worth rebooting a Server on a regular planned preventative maintenance - some said yes, others took the view that if it ain't broke, don't mess with it.  I started a discussion on one of the many DBA groups and pretty soon we had a consensus - leave well alone. 

Remember that you are marketing here, rather than selling - you want agencies to find you and it is easier for them to do so if you know lots of people in the field.  Try to avoid the direct sell - there's a guy who posts on one of my groups to say that he is looking for a job but never posts otherwise - I get the impression that he is posting everywhere, essentially spamming us. 

As well as that, agencies (and even some companies) post jobs on these groups - if a relevant one comes up, great, apply.  But if you see something that isn't relevant, it might interest one of your contacts.  Pass it on - say "It's not really suitable for me, I'm looking for X rather than Y, but I thought you might be interested" 

Think about where that gets you.  Someone in your field now knows what you are looking for.  They feel well-disposed towards you, even if they are not actually job hunting.  If they see X, there's a pretty good chance that they will tell you about it.  If an agency approaches them, there's a good chance that they will refer you. 

So you can market yourself online, you can learn stuff and get technical questions answered, you can have fun.  What’s not to like?

Happy New Year!

Job Hunting

OK, so it's a bit off to one side of the other things I have posted on this blog so far.  But I am job-hunting, and no doubt others are too, so someone out there might find it useful.  There seem to be more websites advertising jobs than there are jobs.  Here's my list. 

If you get your CV uploaded, then employment agencies really do trawl through these sites and they really do ring or email you.  But although it's free to you, it isn't free to them - it costs an agency £10,000 a year or more for just one of these sites - so agencies only sign up for one or two.  Hence you need to load your CV on at least a few of these. 

Indeed

http://www.indeed.co.uk/jobs

Set up alerts.  This seems to be a scraper site - it picks up the jobs from lots of other sites and delivers them to you in one long email.  I set up an alert for DBA which delivers a manageable list every day.  But add the magic word SQL, and it delivers far too many - so I have to put up with the Oracle jobs too. 

Jobsite

https://www.jobsite.co.uk/cgi-bin/login_applicant.cgi

Set up alerts; Upload CV - an old reliable favourite

Jobserve

http://www.jobserve.com/it/jobs.htm

Set up alerts; Upload CV - another old reliable one

Computer Weekly

www.computerweekly.com

Set up alerts; Upload CV

Career Builder

http://www.careerbuilder.co.uk/

set up alerts; upload CV

CV Library

http://www.cv-library.co.uk/

Set up alerts; Upload CV
Bizarrely, they sent me a list of jobs I might like today, including a Drama Teacher in Mansfield, which is a bit odd since I told them I was looking for a SQL DBA post in Bristol <sigh>

Monster

http://jobsearch.monster.co.uk/

Set up alerts; Upload CV

Thanks to Richard for spotting that I was linking to the US site

CW Jobs

www.cwjobs.co.uk

Set Up Alerts; Upload CV

IT Job Board

http://www.theitjobboard.com/

Set Up Alerts; Upload CV

Planet Recruit

www.planetrecruit.com

Set Up Alerts; Upload CV

Silicon

www.silicon.com

Set Up Alerts

Slightly cockeyed – if you edit your alert, and click on the Save Changes button, it unsubscribes your alert.  There is a Save Changes button and an Unsubscribe button, and someone has screwed up.  It’s easy to work around – just subscribe again – but you need to be aware of it.  I’ve emailed them to report the bug – let’s see if they fix it before you read this!

Total Jobs

www.totaljobs.com

Set Up Alerts; Upload CV

I think this is linked to one of the other job sites here.  I went to load my up to date CV and it was already there, but the rest of the entries related to things I was applying for five years ago.  A little bit spooky...

No Good for Me

Jobs in the Public Sector and Charities http://jobs.thirdsector.co.uk/ Nothing for the DBA here – only one job in the whole country so much as mentions SQL.  Clearly IT employers in this sector shop elsewhere Job Centre Plus http://jobseekers.direct.gov.uk Have you ever heard of anyone going to the Job centre and coming away with a job?  I did hear of it happening once, but that was in France with their ANPE organisation.  You are welcome to try – but I put in SQL Server DBA and it came back with a couple of Data Input Clerk vacancies.  Empty Lemon www.emptylemon.co.uk Great name!  It's an anagram of Employment, geddit?   Great logo - it's an empty lemon!  Ha ha! Shame it never has any DBA jobs...

Still to sign up for:

Workthing

www.workthing.com

Fish4Jobs

http://www.fish4.co.uk/

Gumtree

http://www.gumtree.com/jobs

Reed

www.reed.co.uk/job

Newspapers

I haven't bothered with these as I don't think you will find many DBA jobs in The Guardian and the other nationals.  But it doesn't hurt to list them, as what is true for me may not be true for you.  

MyJobGroup Network - e.g. www.mymanchesterjobs.co.uk

Guardian Jobs

Independent (londoncareers.net)

Telegraph Jobs

Times Online

London Jobs

thejobsmine.co.uk

thisisbristol

Just London Jobs

Good Luck!

Extracting Different Bits Of A Single Field

So what do you do when some crazy person designs a database with a single field to hold three separate bits of data?  Of course it probably wasn’t designed as such – probably somebody using the database decided that it would be neat if they could record three bits of data about the incoming call.  Rather than asking the developers to add a couple of extra fields, some bright spark decided to use the Category field to contain two or three things. 

So – let me show you what I mean. 

Audit Approach - Audit Approach Manual - Other

NHS - Other NHS

NHS - Audit Responsibilities

NHS - Audit Responsibilities

NHS - Accounting - Financial instruments

Local Government - Housing Benefits

Local Government - Housing Benefits

Local Government - Housing Benefits

NHS - Audit Responsibilities

NHS - Audit Responsibilities

Local Government - IFRS Conversion - PPE and other non-current assets

So sometimes there are two categories, and sometimes three.  Fortunately they always use a hyphen between them. 

But now I have to create a report which reads in this single field and splits it into three – before the first hyphen, between the hyphens, and after the second hyphen (if there is one). 

Category 1

So, the first bit is easy:

select

       --the original single field

       ca_full_name,

      

      

       -- cat 1 - before first hyphen

       substring(ca_full_name, 1, charindex(' - ', ca_full_name) )  

as Category_1,

SubString() lets you read in a text field, select a start point, then select a finish point.  So if your text says “Antidisestablishmentarianism” you can say

select substring ('Antidisestablishmentarianism', 1,4)

and get the result “Anti”. 

But in this case we don’t know where to stop, so instead of specifying the end point as a number, we have to use the Charindex() function. 

select charindex ('i', 'Antidisestablishmentarianism')

This means look through the text until you find the first letter ‘i’. 

The answer is 4

So the following code says look through the name until you find space hyphen space

charindex(' - ', ca_full_name)

Okay so far?

Category 2

Here’s how to get the middle bit.  I’m indebted to Stephen Lasham, who lives in New Zealand and wrote this article back in 2004 (isn’t the Internet wonderful?).  The trouble with Charindex() is that it only finds the first occurrence of the thing you are looking for. 

Extracting a string from between two delimiting characters

http://www.sqlservercentral.com/articles/Advanced+Querying/extractingastringfrombetweentwodelimitingcharacter/1397/

       -- cat 2 - from first hyphen to second hyphen or end

       CASE

       -- when no second hyphen,

       WHEN CHARINDEX(' - ',(SUBSTRING(ca_full_name,

CHARINDEX(' - ',ca_full_name,1)+1,99))) = 0

       -- then extract from first hyphen to the max length of 99

       THEN LTRIM(RTRIM(SUBSTRING(ca_full_name,

CHARINDEX(' - ',ca_full_name,1)+3,99)))

      

       -- else extract from the first hyphen up to second hyphen

       ELSE LTRIM(RTRIM(SUBSTRING(

       /* <text>> */ ca_full_name,

       /* <start > */      CHARINDEX(' - ',ca_full_name,1) + 3,

       /* <length> */      CHARINDEX(' - ', SUBSTRING(ca_full_name,

                            CHARINDEX(' - ', ca_full_name, 1)

+1,    99)) - 2 ) ) )

       END    AS Category_2,

So this time, there are two steps.  If there isn’t a second hyphen i.e. there are only two categories in the field, start not at the beginning but at the first hyphen, then read to the end.  The Ltrim() and Rtrim() bits get rid of any leading or trailing spaces, so it looks nice. 

If there IS a second hyphen, Stephen’s code starts at the first hyphen, then reads to the second hyphen. 

Category 3

Okay, nearly there. 

--  cat 3 - after second hyphen (if present)

       CASE

       -- when no second hyphen,

       WHEN CHARINDEX(' - ',(SUBSTRING(ca_full_name,

CHARINDEX(' - ',ca_full_name,1)+1,99))) = 0

       -- then print nothing

       THEN (SUBSTRING(ca_full_name,0,0))

       ELSE

       -- read back from the end until you get to the hyphen

       reverse(substring(reverse(ca_full_name), 1,

charindex(' - ', reverse(ca_full_name))-1))

       END                   as Category_3

      

So, if there isn’t a second hyphen, then print nothing at all (start at 0, stop at 0).  Because there isn’t any third bit, see?

But if there is a third bit, what I’ve done is read it from the end using the Reverse() function

select reverse ('Antidisestablishmentarianism')

Which of course gives us msinairatnemhsilbatsesiditnA. 

The code reads in the text, reverses it, reads through it until it gets to the hyphen.  Then it takes the backwards text up to that point and reverses it again so that it comes out facing the right way.  So we end up with this:

Category_1	Category_2	Category_3
Local Government	Whole of Government Accounts	Other
Probation	Accounts and Accounting	Service concessions and leases
Probation	IFRS Conversion	Employee benefits
Local Government	Capital Finance	Financial Instruments
Local Government	Operational and Legal Environment	Other
Other	Trash
Foundation Trusts	Additional Services	Other
Ethics	Non-Audit Services	Other
Foundation Trusts	Accounting	Service concessions and leases
Probation	Audit Responsibilities
Local Government	IFRS Conversion	IFRS conversion
Local Government	Operational and Legal Environment	Other

All done!  I hope this is useful.  And just think – none of this would have been necessary with a better database design in the first place.