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 2 (SP2) 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, http://support.microsoft.com/kb/935897/ 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 http://support.microsoft.com/kb/937137.  In addition to information about the seven (going on eight) releases of “post-SP2 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]

 

The latest downloadable post-SP2 cumulative update package (#7) can be found here: http://support.microsoft.com/kb/949095/

 

 

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'

Oh Where Oh Where Has My Little [DelayActivty] Gone, Oh Where Oh Where Can He Be?

Ever wonder where SharePoint keeps it’s “To Do List of things I should remember about currently running DelayActivities”?

Want to figure out if there are any “dead timer events” associated with any of your workflow DelayActivities that will never be delivered?

Here’s a piece of SQL that will dump out all the event deliveries that are pending or overdue.

Just connect to your SharePoint SQL Server database, adjust the name of the content database (if necessary) and run it. By default, the example dumps out entries from the last seven days. If you want to dump out the entire table, just comment out the AND in the example below (which helps explain the weird-looking 1 = 1 condition).

If DeliveryDate is more than 10-20 minutes behind (adjusted for UTC to your local time zone conversion), chances are good that your DelayActivity will never “pop” at the desired time.

The mystery is why, which only Microsoft can answer.

At least you now have a tool to help discover these “dead timers”.

Note: I convert the DateTime fields to ISO 8601 strings for easier viewing and importing into tools such as Microsoft Excel.

--
-- What DelayActivities were supposed to "pop" in the last few days?
USE [WSS_Content]
GO

SELECT Convert(varchar, GetDate(), 120) [CurrentDateTime]
      ,Convert(varchar, wf.[Created], 120) wf_CREATED
      ,Convert(varchar, swi.[Created], 120) Created
      ,Convert(varchar, swi.[DeliveryDate], 120) DeliveryDate
      ,swi.[InternalState]
      ,wf.[SiteId]
      ,wf.[Id]
      ,[Type]
      ,[ParentId]
      ,wf.[ItemId]
      ,wf.[ItemGuid]
      ,[BatchId]
      ,wf.[WebId]
      ,[UserId]
      ,[BinaryPayload]
      ,[TextPayload]
      ,[ProcessingId]
      ,[ProcessMachineId]
      ,[ProcessMachinePID]
  FROM [dbo].[Workflow] wf
  INNER JOIN [dbo].[ScheduledWorkItems] swi
  ON wf.Id = swi.Id
WHERE 1 = 1
  AND swi.[Created] > DateAdd(Day, -7, GetDate())
ORDER BY
	swi.[Created] DESC

Prevent Workflow Tasks From Being Deleted

Here’s a simple way to prevent users from deleting their assigned tasks (who would ever want to do that??).

Start by coding an ItemDeleting event receiver that cancels the attempt to delete the task.

If you want to drive your users crazy, you can just set the Status to CancelNoError.
Try it out first before you decide to go that route.

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SharePoint;

namespace SameNameSpaceAsTheRestOfYourWorkflow
{
    class PreventWorkflowTaskDelete : SPItemEventReceiver
    {
        ///
        /// Prevent a workflow task from being deleted.
        ///
        ///
        public override void ItemDeleting(SPItemEventProperties properties)
        {
            string errorMessage = string.Empty;
            try
            {
                errorMessage = string.Format("User {0} is not allowed to delete this Task.", properties.UserDisplayName);
            }
            catch
            {
                errorMessage = "You are not allowed to delete a task.";
            }
            finally
            {
                properties.ErrorMessage = errorMessage;
            }
            properties.Cancel = true;
            properties.Status = SPEventReceiverStatus.CancelWithError;
        }
    }
}

Next, when your workflow starts, call a method similar to the following to register the custom event receiver.
My example includes calls to my custom trace feature, so you’ll have to eliminate them before you can use this code.

#region PreventWorkflowTaskDeletion
        ///
        /// Prevent workflow tasks from being deleted by adding an event receiver that will cancel any
        /// attempt to delete a task from the workflow task list.
        ///
        private void PreventWorkflowTaskDeletion()
        {
            string methodName = MethodBase.GetCurrentMethod().Name;
            TraceEnter(methodName);

            string message = string.Empty;

            // prevent multiple ItemDeleting event receivers from being attached to the task list by removing any that may already exist.
            // NOTE: has the added benefit of replacing existing event receiver if we made a code change and redeployed recently.
            SPEventReceiverDefinitionCollection evrs = workflowProperties.Workflow.TaskList.EventReceivers;
            List ersToDelete = new List();
            foreach (SPEventReceiverDefinition er in evrs)
            {
                if (er.Type == SPEventReceiverType.ItemDeleting)
                {
                    // add the GUID index of the Event Receiver to the list of ones we need to get rid of
                    ersToDelete.Add(er.Id);
                }
            }

            // have to wait until outside of the above loop to delete; otherwise, you get this error:
            // System.InvalidOperationException: Collection was modified; enumeration operation may not execute.
            foreach (Guid i in ersToDelete)
            {
                message = string.Format("Removed ItemDeleting event reciever with GUID of {0}", i.ToString());
                TraceInfo(message);
                evrs[i].Delete();
                workflowProperties.Workflow.TaskList.Update();
            }

            // Example: "Microsoft.Office.Samples.ECM.Workflow.ReplicatorContactSelectorSample, Version=3.0.0.0, Culture=neutral, PublicKeyToken=ec457ebe7d96977c";
            string assembly = Assembly.GetExecutingAssembly().FullName;
            message = string.Format("assembly = {0}", assembly);
            TraceInfo(message);

            // Example: "Microsoft.Office.Samples.ECM.Workflow.PreventWorkflowTaskDelete";
            string className = typeof(PreventWorkflowTaskDelete).FullName;
            message = string.Format("className = {0}", className);
            TraceInfo(message);

            // add the ItemDeleting event receiver to the current workflow task list
            try
            {
                workflowProperties.Workflow.TaskList.EventReceivers.Add(SPEventReceiverType.ItemDeleting, assembly, className);
                workflowProperties.Workflow.TaskList.Update();
                message = string.Format("Added ItemDeleting event");
            }
            catch (Exception ex)
            {
                message = string.Format("ERROR: Method {0} had an exception while attempting workflowProperties.Workflow.TaskList.EventReceivers.Add : {0}", ex.Message);
                TraceError(message);
                throw new SPException(message);
            }
            finally
            {
                TraceInfo(message);
            }

        }
        #endregion

In the above code, I was making frequent changes to the event receiver, thus the need to remove and re-add the event receiver each time.

PowerShell Script To Display Settings Critical To Proper Operation Of DelayActivity

Run this while logged on to your SharePoint server to see the current settings that may affect the proper operation of the DelayActivity in your workflows.  WARNING: Your auditor may not like the output from Job-Workflow-AutoClean. 

# PowerShell script to display various SharePoint internal settings that may affect DelayActivity event delivery
# Author: Fred Morrison
# Company: Exostar: The Trusted Workspace for Global Partner Networks - www.exostar.com
# Last Modification Date: 2008-04-02
# Name: SpGetSettings.ps1

# start by creating an alias for the stsadm command, allowing for 32-bit vs. 64-bit differences
Set-Alias -Name stsadm 	-Value $env:CommonProgramFiles'\Microsoft Shared\Web Server Extensions\12\BIN\STSADM.EXE'

#reference: http://technet.microsoft.com/en-us/library/cc424970.aspx
Write-Host "Job-Workflow"
stsadm -o getproperty -pn job-workflow -url http://localhost
Write-Host ""

#reference: http://technet.microsoft.com/en-us/library/cc262432.aspx
Write-Host "Job-Immdiate-Alerts"
stsadm -o getproperty -pn job-immediate-alerts -url http://localhost
Write-Host ""

#reference: http://technet.microsoft.com/en-us/library/cc424968.aspx
Write-Host "Job-Workflow-AutoClean"
stsadm -o getproperty -pn job-workflow-autoclean -url http://localhost
Write-Host ""

#reference: http://technet.microsoft.com/en-us/library/cc424943.aspx
Write-Host "Job-Workflow-Failover"
stsadm -o getproperty -pn job-workflow-failover -url http://localhost
Write-Host ""

#reference: http://technet.microsoft.com/en-us/library/cc263016.aspx
# NOTE: above documenation is in error - valid values seem to be "yes" or "no", not "a value between 1 and 500"
#       compare with Alerts-Maximum to see source of possible copy/paste error during creation of documentation.
Write-Host "Alerts-Limited"
stsadm -o getproperty -pn alerts-limited -url http://localhost
Write-Host ""

#reference: http://technet.microsoft.com/en-us/library/cc262842.aspx
Write-Host "Alerts-Maximum"
stsadm -o getproperty -pn alerts-maximum -url http://localhost
Write-Host ""

#reference: http://technet.microsoft.com/en-us/library/cc262968.aspx
Write-Host "Workflow-EventDelivery-Timeout"
stsadm -o getproperty -pn workflow-eventdelivery-timeout -url http://localhost
Write-Host ""

#reference http://technet.microsoft.com/en-us/library/cc261828.aspx
Write-Host "Workflow-EventDelivery-BatchSize"
stsadm -o getproperty -pn workflow-eventdelivery-batchsize -url http://localhost
Write-Host ""

#reference: http://technet.microsoft.com/en-us/library/cc262997.aspx
Write-Host "Workflow-EventDelivery-Throttle"
stsadm -o getproperty -pn workflow-eventdelivery-throttle -url http://localhost
Write-Host ""

Write-Host "Workflow-Cpu-Throttle (obsolete)"
stsadm -o getproperty -pn workflow-cpu-throttle -url http://localhost
Write-Host ""

Write-Host "Workflow-TimerJob-Cpu-Throttle (obsolete)"
stsadm -o getproperty -pn workflow-timerjob-cpu-throttle -url http://localhost
Write-Host ""

#reference: http://technet.microsoft.com/en-us/library/cc263471.aspx
Write-Host "Workitem-EventDelivery-BatchSize"
stsadm -o getproperty -pn workitem-eventdelivery-batchsize -url http://localhost
Write-Host ""

#reference: http://technet.microsoft.com/en-us/library/cc263141.aspx
Write-Host "Workitem-EventDelivery-Throttle"
stsadm -o getproperty -pn workitem-eventdelivery-throttle -url http://localhost

 

Workflow Task Due Dates - Give Them The Entire Last Day To Finish Their Task

Are you setting the DueDate property of your workflow tasks to a date that you pluck from an InfoPath 2007 workflow initialization form using logic similar to the following?

 // capture task due date from XSD-generated InfoPath form class module
ApproverDueDate = frmInit.ApproveDueDate;

// set up SPWorkflowProperties of the task
taskProps.StartDate = DateTime.Now;
taskProps.DueDate = ApproverDueDate;
taskProps.PercentComplete = 0.0f;

// later on, after a DelayActivity finishes waiting for the due date to arrive so it can check for incomplete tasks...
if {DateTime.Now > ApproverDueDate)
{
    // perform some actions that result in automatic task completion
}

Please realize that if ApproverDueDate is 2008-12-31, the task will be considered overdue at 2008-12-31 00:00:00, exactly one day less than what the task assignee might expect.  If you want to give the task assignee until the end of the day of the task due date to complete his task, you should change your code to something more like this:

// allow task to be completed up until 23:59:59 of the due date
ApproverDueDate = frmInit.ApproveDueDate.AddDays(1).AddSeconds(-1);

Keep the above in mind for overdue task reminder dates too!

Create a Discussion Thread on a Discussion Board with Author Override

So, there I was, trying to figure out how to create a new Discussion Thread on a SharePoint Discussion Board using the ‘feedback’ comments from a just-completed SharePoint task. 

It’s a great way to put all the comments from each completed workflow task in a SharePoint Discussion Board, providing an easy, ‘one-stop shopping’ view of all the feedback from all the workflow participants.  They can even engage in threaded conversations, if desired.

A quick Google search and I was off to the races using SPUtility.CreateNewDiscussion.  Should have been what I call a ‘quick victory’ in terms of time and code required.  It should have been even quicker because I wrote a small, one-step ‘proof of concept’ workflow using Visual Studio 2008 to verify how things should work.  VS 2008 streamlines the time needed to go from code to running a workflow by a factor of 4:1 compared to VS 2005.

Experience tells me there is a lot that ‘Mother [Microsoft] Never Told You About <<fill in subject>>…’, and this situation, of course, was no different. 

It seems that the ‘Author’ of the conversation thread cannot be specified via the CreateNewDicussion API.  It took some digging/experimentation (mostly try this, fail, try something else, fail) – the usual pattern in other words.  Even when I thought I had the solution, I got tripped up by the ‘freaky string format syntax’ required to change the ‘Author’ (and ‘Editor’ too as it turns out) to something other than the default of ‘System Account’ (or whatever account runs the SharePoint ‘engine’). 

So what exactly was it that ‘Mother [Microsoft] Never Told You About Specifying the Author of a SharePoint Discussion Thread’?  In code, it looks like the following, paying particular attention to the string.Format syntax. 

My question to anyone who might have some insight is this: was the person that dreamed up the freaky “;#” syntax needed to set the ‘Author’ and ‘Editor’ fields on drugs at the time he/she architected that part of SharePoint?  What happened to the KISS principle on this one?  Remember: Eschew Obfuscation!

/// <summary>
/// Creates a new Discussion thread on the specified Discussion Board, while associating the creator of the thread
/// to the specified person, not the default of 'System Account'
/// (or whatever account is running the SharePoint 'engine').
/// </summary>
/// <param name="ThreadCreator">SPUser object instance of the person who should become the thread Author.</param>
/// <param name="DiscussionBoard">SPList object instance of the Discussion Board a new Discussion Thread will be added too.</param>
/// <param name="Title">String value of the title of the new Discussion Thread.</param>
/// <param name="Body">String value of the body of the new Discussion Thread.</param>
/// <returns>SPListItem instance pointing to the newly created Discussion Thread</returns>
private SPListItem CreateDiscussionThread(SPUser ThreadCreator, SPList DiscussionBoard, string Title, string Body)
{
    SPListItem returnValue = SPUtility.CreateNewDiscussion(DiscussionBoard.Items, Title);
    returnValue[SPBuiltInFieldId.Body] = Body;
    string userInformation = string.Format("{0};#{1}", ThreadCreator.ID, ThreadCreator.Name);
    returnValue[SPBuiltInFieldId.Author] = userInformation;
    returnValue[SPBuiltInFieldId.Editor] = userInformation;
    returnValue.Update();
    return returnValue;
}

SharePoint Forums

http://mssharepointforums.com/

http://www.sharepointblogs.com/

Andrew Connell’s article on Features

http://www.andrewconnell.com/blog/archive/2007/09/27/6120.aspx