Friday, 31 December 2010


Back in January 2010, I set myself some goals.
I reviewed them in June and had made good progress. Now the year is over, it’s time to review the remainder....

Improve my web presence -

Tidy sqlsolace, improve tagging and searchability -

Achieved :
1. New template implemented giving site a fresh look and full screen width.
2. Reviewed some old posts
3. Categorised old content

Review (& either scrap or post) my backlog of scripts and notes -

Achieved : 50 posts from old notes

Review and remove old/unprofessional material from my personal web site

Achieved : Site Tidy up completed. Some sarcasm remains but nobody’s perfect...

Community -

Publish further scripts on other sites (September 2009 : had 1st published on SSC) -

Achieved : I have now had 7 scripts published @ SQLServerCentral . Not as many as I would like, but I’d rather they were useful than just fulfil a quota.

Publish an article

Achieved : SQL Server Club featured an exploration of understanding SQL Server Collations.

Attend Sql Server events / meetups

Achieved : 3
SQLBits VI in London, April 2010
Kent SQL User Group, August 2010
SQLBits VII in York, October 2010

Network more

Achieved : Connections on LinkedIn and Twitter with the UK Sql server community (mostly met through SQLBits)

Learning – 

Continue with SSIS development to further my ETL skills.

Achieved : 3 SSIS Solutions are now in production

Business Requirements and company direction have sadly prevented any new SSIS projects being commissioned. Scaling up SSIS remains a professional goal.

Obtain Microsoft BI certifications

Achieved : Passed MCTS (exam 70-448) and MCITP (exam 70-452) in SQL 2008 Business Intelligence in September 2010. They took a month of study , by which I mean I did nothing else with my evenings except study!

Having achieved this, I’m itching to get my teeth into an Analysis Services project.

Tackle the mountain of books, whitepapers and printed articles in my office.

Achieved :

None (in their entirety that is)
I have 3 books on the go, each of which I am half way through. These are -
  1. SQL Server MVP Deep Dives
  2. Inside Microsoft SQL Server 2008 T-SQL Querying
  3. Professional Sql 2008 Internals and Troubleshooting

Saturday, 18 December 2010

Bookmark : Role of the Infrastructure DBA

A great post from Gavin Payne explaining how the DBA role has evolved.
I can certainly relate to this having spent a lot of the last 2 years running sql server on Hyper-V and having configured my own Windows 2008 Active Directory domain too.

Link : The Role of the Infrastructure DBA

Wednesday, 15 December 2010

RETRY mechanism with WHILE loop (tsql template)

This code demonstrates a RETRY mechanism with a WHILE loop.
The loop continues until the task succeeds or @MaxAttempts is reached.
If @MaxAttempts is reached and the task still fails, an email is sent



DECLARE @MaxAttempts INT
DECLARE @CounterText VARCHAR(30)
DECLARE @ErrorMessage NVARCHAR(4000)  
DECLARE @ErrorSeverity INT  
DECLARE @EmailMessage VARCHAR(100)

SET @ErrorMessage = 'run_attempt'
SET @Counter = 0
SET @maxAttempts = 3

-- @ErrorMessage will be NULL after a successful execution of the program.
-- Checking for IS NOT NULL will mean the loop continues until success.

WHILE (@ErrorMessage IS NOT NULL) AND (@ErrorMessage NOT LIKE 'Warning: Null value%') AND (@Counter <= @MaxAttempts )


 -- Increment counter and display run number to the screen
 SELECT @Counter = @Counter + 1 
 SET @CounterText = CONVERT(VARCHAR(30),@Counter,23)
 RAISERROR (@CounterText, 10, 1) WITH NOWAIT   
  -- Run the program you want to 'retry' 
  EXEC myschema.mystoredProc
  SELECT  @ErrorMessage = ERROR_MESSAGE() ,  @ErrorSeverity = ERROR_SEVERITY()  

  IF @Counter = @MaxAttempts
    SET @EmailMessage = 'mystoredProc  : Run Attempt : ' + CONVERT(VARCHAR(30),@Counter,23) + ' failed '

    EXEC msdb.dbo.sp_send_dbmail 
       @profile_name='SQL Profile'
     , @recipients=''
     , @body=@EmailMessage
     , @subject=@EmailMessage
     , @importance='High'



Saturday, 11 December 2010

Free Online Storage : Dropbox

Am really liking this tool, and am favouring it over my usb drive for smaller, more frequently accessed stuff.

Thursday, 9 December 2010

Wednesday, 8 December 2010

SSIS : The Lookup Component (bookmarks)

No point in reinventing the wheel, but some great reading on the Lookup component and some methods to adopt when using it.

SSIS 2008 lookup component

Lookup Component Cache Modes

Using the SQL 2008 Lookup and Cache Transforms in a SQL Server Integration Services (SSIS) Package

SSIS : Incremental persistent cache updates

SSIS 2008 - Incrementally Updating the Lookup Cache File

Cascading Lookup

Lookup component - Ensure varchar columns are not padded

Handling Early Arriving Facts in SQL Server Integration Services SSIS

Tuesday, 7 December 2010

Logging Errors to SQL logs via RAISERROR WITH LOG

Logging errors to SQL logs via RAISERROR >

1) The Code -
RAISERROR('Test of custom error logging', 18, 1) WITH LOG

2) Management Studio Results

3) Corresponding entry in the SQL Server log

Expanding on this we can pass the genuine error message through, like this -
1) The Code -

 -- Do action that we want rolled back if an error occurs
 DELETE Person.Address WHERE AddressID = 1  



 DECLARE @ErrorMessage NVARCHAR(4000)
 DECLARE @ErrorSeverity INT

  @ErrorMessage = ERROR_MESSAGE(),
  @ErrorSeverity = ERROR_SEVERITY()

 RAISERROR(@ErrorMessage, @ErrorSeverity, 1) WITH LOG

2) The SQL log -

You can do a variety of Error logging methods here in this way -
1 ) send email
2 ) write to table
3 ) log to sql via RAISERROR (as abover)

You have access to the following error functions -

ERROR_NUMBER() - returns the number of the error.
ERROR_SEVERITY() - returns the severity.
ERROR_STATE() - returns the error state number.
ERROR_PROCEDURE() - returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE() - returns the line number inside the routine that caused the error.
ERROR_MESSAGE() - returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

link :