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

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

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

BEGIN

SET NOCOUNT ON

DECLARE @MaxAttempts INT
DECLARE @Counter 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 )

BEGIN   

 -- 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   
 
 
 BEGIN TRY
 
  -- Run the program you want to 'retry' 
  EXEC myschema.mystoredProc
  SELECT  @ErrorMessage = ERROR_MESSAGE() ,  @ErrorSeverity = ERROR_SEVERITY()  
 
 END TRY
 
 BEGIN CATCH

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

    EXEC msdb.dbo.sp_send_dbmail 
       @profile_name='SQL Profile'
     , @recipients='recipent@domain.com'
     , @body=@EmailMessage
     , @subject=@EmailMessage
     , @importance='High'
     
  END
 
 END CATCH

END  

END

Free Online Storage : Dropbox

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

Using MAXDOP to throttle an index build

CREATE UNIQUE CLUSTERED INDEX CI_MyIndex
ON Processiing.myPartitionedTable (DateTime, ID) WITH (MAXDOP=1,DATA_COMPRESSION = PAGE,ONLINE = ON)
ON PScheme_YearDateRange (DateTime) 
GO

Links :

MAXDOP WITH ALTER INDEX
http://www.sqldev.org/sql-server-database-engine/how-does-maxdop-work-with-alter-index-87328.shtml

MAXDOP
http://blogs.msdn.com/arali/archive/2009/11/26/sql-server-max-degree-of-parallelism-maxdop.aspx

Partitioning walkthrough
http://blog.sqlauthority.com/2008/01/25/sql-server-2005-database-table-partitioning-tutorial-how-to-horizontal-partition-database-table/

Bookmark : Burnout & Recovery

SQL Server MVP Gail Shaw blogs today on "burnout and recovery"

A refreshingly honest post that I can relate to. She reflects on what has happened and suggests what steps she should have taken.

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
http://consultingblogs.emc.com/jamiethomson/archive/2007/11/16/Katmai_3A00_-SSIS_3A00_-Lookup-component-gets-a-makeover.aspx


Lookup Component Cache Modes
http://blogs.msdn.com/mattm/archive/2008/10/18/lookup-cache-modes.aspx

Using the SQL 2008 Lookup and Cache Transforms in a SQL Server Integration Services (SSIS) Package
http://www.mssqltips.com/tip.asp?tip=1511

SSIS : Incremental persistent cache updates
http://blogs.msdn.com/mattm/archive/2008/11/23/lookup-pattern-incremental-persistent-cache-updates.aspx

SSIS 2008 - Incrementally Updating the Lookup Cache File
http://agilebi.com/jwelch/2008/05/06/ssis-2008-incrementally-updating-the-lookup-cache-file/

Cascading Lookup
http://blogs.msdn.com/mattm/archive/2008/11/22/lookup-pattern-cascading.aspx

Lookup component - Ensure varchar columns are not padded
http://consultingblogs.emc.com/kristianwedberg/archive/2006/02/22/2955.aspx

Handling Early Arriving Facts in SQL Server Integration Services SSIS
http://www.mssqltips.com/tip.asp?tip=1446

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 -

BEGIN TRY
   BEGIN TRANSACTION 

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

   COMMIT
END TRY

BEGIN CATCH
  IF @@TRANCOUNT > 0
     ROLLBACK

 DECLARE @ErrorMessage NVARCHAR(4000)
 DECLARE @ErrorSeverity INT

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

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

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 : http://technet.microsoft.com/en-us/library/ms175976.asp