Keeping SQL Server 2005 Patched

SharePoint depends on SQL Server 2005.  Therefore, keeping SQL Server 2005 patched is something every developer should be aware of.

You may think putting Service Pack 3 (SP3) on is all you need to do to keep SQL Server 2005 up-to-date.  Not true.  To understand how/why Microsoft delivers fixes for SQL Server 2005, the support article that describes SQL Server Incremental Servicing Model  is a “must read”, not just for DBA’s, but anybody who deals with SQL Server 2005, even indirectly (e.g., SharePoint administrator or SharePoint developer).

While I’m at it, visit The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 3 was released.  In addition to information about the three releases of “post-SP3 cumulative update packages”, you’ll see the latest SQL Server 2005 internal numbering.

To see what version of SQL Server 2005 you are running, run the following query:

SET NOCOUNT ON;

SELECT serverproperty('Edition') [Edition],

        CASE serverproperty('EngineEdition')

               WHEN 2 THEN 'Standard or Workgroup'

               WHEN 3 THEN 'Enterprise, Enterprise Evaluation, or Developer'

               WHEN 4 THEN 'Express, Express Edition with Advanced Services, or Windows Embedded SQL'

               ELSE '{unknown}'

        END [Engine Edition],

        serverproperty('ProductVersion') [Product Version],

        serverproperty('ProductLevel') [Product Level]

 

With just Service Pack 3 installed on my SharePoint development box, the output from the above command is:

Engine: Enterprise Edition

Engine Edition: Enterprise, Enterprise Evaluation, or Developer

Product Version: 9.00.4035.00

Product Level: SP3

 

The latest downloadable post-SP3 cumulative update package (#1) can be found here.

Using PowerShell With SharePoint – an example

I recently needed to clean out some tasks from the task lists on my development box.  Unfortunately, I had attached an ItemDeleting event receiver to most of the task lists as part of a custom workflow.  The event receiver prevents a task assignee from deleting their assigned task.  Instead of writing a C# console application, I wrote the PowerShell utility (using my favorite tool, PowerGUI) shown below and simply typed the following at a PowerShell prompt to remove all the ItemDeleting event receivers from all the lists:

SPRemoveItemDeletingEventReceivers http://workflow2/FredsWfTestSite

# SPRemoveItemDeletingEventReceivers.ps1
#
# Purpose: Removes any ItemDeleting Event Receivers from all lists on the specified site collection
#
# Example: SPRemoveItemDeletingEventReceivers http://workflow2/FredsWfTestSite
#
# show what we expect in the form of parameters to be passed to this script
param([string] $siteName)

# following makes it easier to work with SharePoint and also means you have to run this script on the SharePoint server
[void] [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SharePoint' ) | Out-Null

$site = New-Object -TypeName Microsoft.SharePoint.SPSite $siteName
[Microsoft.SharePoint.SPWeb] $web = $site.OpenWeb()
[Microsoft.SharePoint.SPListCollection] $lists = $web.Lists
Write-Host $sites.Count
[Microsoft.SharePoint.SPList] $list = $null
[int] $iList = 0
for( $iList=0; $iList -lt $lists.Count; $iList++)
{
	$list = $lists[$iList]
	# since we only attached an ItemDeleting event receiver to task lists, only look at the those types
	if ( $list.BaseTemplate -eq 'Tasks' )
	{
		[string] $listTitle = $list.Title
		Write-Host $list.Title $list.EventReceivers.Count
		[int] $evIdx = 0
		[bool] $found = $false
		# for every Event Receiver defined for the list ...
		for($evIdx = 0; $evIdx -lt $list.EventReceivers.Count; $evIdx++)
		{
			# get a reference to the Event Receiver
			[Microsoft.SharePoint.SPEventReceiverDefinition] $er = $list.EventReceivers[$evIdx]
			# if it's a type we care about ...
			if ( $er.Type -eq 'ItemDeleting' )
			{
			    [string] $erType = $list.EventReceivers[$evIdx].Type
				# get rid of it
				$list.EventReceivers[$evIdx].Delete()
				Write-Host "Removed an $erType Event Receiver from $listTitle"
				$found = $true
			}
			# if we removed any of the specified Event Receivers from the list, update the list
			if ($found)
			{
				$list.Update()
			}
		}
	}
}
Write-Host 'Done'