T-SQL Tuesday

Thanks to Erin Stellato for hosting this months #TSQL2sday. Erin wanted to know all about what we do every day!

Interestingly, when I was much younger, I wanted to be a Fire-fighter or a Pilot. I’m still quite keen on learning to fly, but that’s looking less likely as time is going by (Eyesight, time, age and cost in that order).

Now though, and for the past 12 years or so, I work as a Consultant. It’s a nice, vague title. It started out as ‘Technical Consultant’, moved through Systems Consultant, and CRM Consultant. It’s currently bouncing between BI Consultant and Data Warehousing Consultant depending on the project I’m working on.

2012-07-11 07.31.33
My Journey to Work

2012-07-12 07.33.32
The Office

My Day!

The day started by sitting in a traffic jam. Pretty common that, unfortunately.

However, when I made it to my desk, I did a couple of checks of a server that I was running maintenance jobs on overnight. All was well, so I dived into email.

A couple of interesting items in there, one was a link about a Pigeon with a USB stick being faster than UK broadband (BBC link here). Also, was an invitation to the Microsoft Hadoop on Azure trial, which looks really interesting, and something I’ll have a look at next week (link here).

The Morning

Then, I started work on a Customer project that I’m working on this week. It’s effectively adding two additional country feeds (Spain and France, since you asked), to a data warehouse. The customer is using WhereScape RED, so it was a pretty straightforward matter of dragging and dropping the tables from the DB2 source system, into the ETL tool. WhereScape RED then generates the stored procedures to allow the ETL process to run, to get the data into the DWH.

Sounds a pretty straightforward process, however, there are 91 tables, and a couple of minor modifications to each one. So that took up all of my morning.

The Afternoon

The afternoon was pretty much taken up by an interesting problem with a BusinessObjects (XI4)environment. It was apparently continually running a query against the SQL Server database. We managed to prove it was the BO server doing this by changing the service account it was running as. The query could be seen in sp_whoisactive (thank you @AdamMachanic) to be run by a different user. The query was proceeding to take the server utilisation to 100%, which meant that the other databases on the server couldn’t effectively service user queries.

To temporarily resolve this issue, we put Resource Governor on, which restricted the BusinessObjects service to 25% of the CPU power, thereby letting the other users have some resources.

I found a really helpful query that helped me to find the queries that were being run. The query (from SQLAuthority, is copied here).

SELECT sqltext.TEXT, req.session_id, req.status,
req.command, req.cpu_time, req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Copied from http://blog.sqlauthority.com/2009/01/07/sql-server-find-currently-running-query-t-sql/

The final solution to the issue was to apply the BO XI4 SP4 patch, which appears to have resolved the issue.

There was also a couple of questions on licencing, to which both answers were ‘If it looks to be too good to be true, it probably is’.

Sadly, I didn’t get any pictures of the Red Arrows flying around the Farnborough Airshow, which is just up the road from us, or any pictures of the White-tailed Kite we saw flying over the motorway.

And that, is pretty much my day; a comparatively quiet one, and for a change, I made it out the door and home at a reasonable time. I hope you found this interesting, and I look forward to reading about your day.

Thanks again to Erin for hosting.


One more SQL Server 2008 Certification under my belt. I passed the ‘PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008’ exam on Friday, which gives me the MS ITP (IT Professional) Database Developer 2008 certification.


To get through this certification, I used a number of sources, however, one of the most useful was a blog series by Eric Wisdahl (http://ericwisdahl.wordpress.com/2011/08/20/mcitp-70-451-links-page/) where he covered each of the sections listed in the exam topics.

Next up will be the 70-450 DBA certification. Once I’ve completed this exam, I’ll have completed the prerequisites for the MCM SQL Server 2008 certification, which is then another two exams!

Interestingly, to me at least, this was my 18th Microsoft certification, having taking my first back in 1999 (Implementing and Supporting NT Server 4.0)!.

Since then I’ve done:

5 x Windows NT 4
1 x Windows 2000 Professional
2 x Microsoft CRM v1.2
3 x Microsoft CRM v3
2 x SharePoint 2007 / WSS 3
1 x SQL Server 2005
4 x SQL Server 2008

And, I’m planning to do another 3 in the next 6 months.

Certified or Certifiable, you decide!

So that’s another one completed, Smile which is the third of the SQL 2008 TS level exams (there should be an Achievement for that – ‘SQL Nutter – SQL TS Hat Trick Unlocked!’.

I took the exam this afternoon, and it went better than I expected, though not as well as some colleagues thought! However, the revision material I used was predominantly experience based, though I did also use the MS Press Training book (available here, at Amazon) to focus in on the exam requirements.

I’ve been running through the MCM training material that SQL Skills and Microsoft have provided (http://www.sqlskills.com/T_MCMVideos.asp), since that is my end goal!

Next up will be the 70-450 exam sometime in July, which is the first PRO level exam I’ll have done! I would do it sooner, but I’m attending the SQLSkills Immersion course in London in June, and from what I’ve heard that’ll be a little intense. Cool!

And how did I celebrate passing the exam ? With a great friend, Colonel Sanders.


Well, that’s a mouthful for a title.

I took this exam this afternoon and passed that with 826.

To prepare for it I used the Microsoft Press book (on here at Amazon UK), and also used the Transcender exam.

I found the Transcender exam to be really helpful, and was substantially easier to use than the MeasureUp exam that was supplied with the MS Press exam.

As an aside, I would highly recommend Mass Systems (in Wokingham) for taking certifications as they have decent LCD screens, rather than CRT’s (as at some other providers)..

So I took the SQL Server 2008 Database Developer exam today, and passed (Hooray!!), with 715. It was a close one. I was expecting to do better on that, as I’ve got project experience and went through the Microsoft Press training book on this (here on Amazon UK).

I’ve got the 70-448 SQL Server 2008 BI Development and Maintenance exam in a few weeks time, so I think I’ll get the Transcender exam for that as well.