SP Insider - A SharePoint Blog

Querying Sharepoint List Item Versions using SQL

The request was simple enough, get the total number of days for a status change from one custom list field to another custom list field  in a Sharepoint List Item. There’s a few ways to do this. Assume you have a Sharepoint workflow that changes a list item through various statuses and various fields. A simple way is to create a Date Changed field for each field that you want to track, then to populate it in your workflow as it goes along.

If you don’t have date fields set up in your list, you can take advantage of the version history if version history is turned on. The key here is to loop through your list items, know which field is the field you want, and then take the first version where the value has changed to the desired value. For example, if you are waiting for a “Status” [...]

Creating a new version of a custom Sharepoint workflow

This is more for my benefit as a reminder of steps, but if you use Makecab.exe for your .wsp packages, it is important to install new versions that don’t step on old versions.  I keep old versions set to “No New Instance” and then New versions pick up the new version of the workflow.

New Version of Sharepoint workflow using a .wsp Package
Solution – add Solution folder “Deploy”
Add Manifest.xml (xml)
Add Solution.ddf (text)
1. Manifest.xml – change SolutionID using New Guid
2. Manifest.xml-change Destination Location folder to include version number.
3. Change Assembly version to next version, Properties, Application, Assembly Information
4. Solution.ddf – change CabinetNameTemplate
5. Solution.ddf – increment version on Feature Folder name (2nd name, leave first folder same since this is the source folder).
6. Feature.xml, change feature ID to new GUID.
7. Feature.xml- change Title to include version number
8. Feature.xml-Change Description to include version number
9. Workflow.xml –Change Name  to include [...]

Stupid attachments in Sharepoint, adding email attachments

You would think taking  attachments from a  Sharepoint list and adding them to a mail message would be easy.  Well, not that easy.  They make you jump through hoops and stream the attachment with the file name.  Would have been nice to just take an attachment off a list and then add it to an email using the same attachment object.  Oh well.  Here’s the code I use to take an attachment off a list item, look at the file name, then attach it to an email using System.Mail.

 MailMessage mail = new MailMessage(); 
 SmtpClient smtp = new SmtpClient this.workflowProperties.Web.Site.WebApplication.OutboundMailServiceInstance.Server.Address); 
 mail.Subject = “Subject”

mail.From = new MailAddress(wfConfig.EmailFrom);
mail.Body =”body”:
mail.IsBodyHtml = true;


 if (workflowProperties.Item.Attachments.Count > 0)
      foreach (string fileName in workflowProperties.Item.Attachments)
      SPFile file = workflowProperties.Item.ParentList.ParentWeb.GetFile(
          workflowProperties.Item.Attachments.UrlPrefix + fileName);
          if (file.Name.ToUpper().Contains(DocNameToCheck))
            Attachment attachment = new Attachment(file.OpenBinaryStream(), fileName, string.Empty);

By |January 25th, 2011|Uncategorized|4 Comments|

The Sharepoint way for Application Error Alerts

 By creating a list of application errors, you can take advantage of all the Sharepoint goodness like metadata, alerts, etc.

 This post shows how to add a complete Error Management system for custom sharepoint errors, with alerts .  It’s really pretty simple, use the existing Sharepoint lists and alerts to help you stay up to date with any application errors.

Create a list to hold your application errors.  See below for a screenshot.

Inner Exception
Stack Trace

  click to expand

 2. From your custom code, create a utility method (I usually have this in a utility class) to add items into the Application Error list.  Pass in the Error, the url, the Title, and the url to your web that contains the error list.

 I tend to use constants as much as I can. Note that I also write the error into the Event Log, for another layer of reporting.  [...]

How to get SSRS Report Usage from Sharepoint in Sharepoint Integrated Mode

SQL Reporting Services in Sharepoint Integrated Mode (MOSS 2007) has always been a little quirky to install, but can be very convenient to organize reports, by leveraging all the Sharepoint metadata, permissions, grouping, filtering and other user interface features.

Reporting Services defaults to 60 days for the execution log.  To change it, go to Central Administration, Application Management, Reporting Services, Set Server Defaults and change the Report Processing Log.

To find out how often reports are being used in your sharepoint installation, you can query the RSSharepoint.Catalog table, join on the Execution Log, and then join against the SP databases to get the metadata for the reports. 

Below is a sample query: (replace list ID with your Report Library list ID)

 –Gets metadata from SP database
Use WSS_Content_XX_DB1
SELECT  A.tp_leafname As RDL, A.ntext2 as Description, A.ntext3 as Business_Group, A.ntext4 as Application
INTO #Temp1
FROM AllUserData A
ON A.tp_ListID = L.TP_ID

Microsoft Support Lifecycles

As developers we always look for the latest technology, but after many years, clients tend to keep applications beyond their expected time.  I am lucky? enough to have an application that has been in use for close to 10 years.  The problem with this is that the underlying server software is no longer supported and we have to migrate the application. So, the decision is keep it on an unsupported platform, or move to a newer platform.

Here’s the site that shows the support dates for MS products:


Here’s a sample of the support lifecycles:

Windows Server products are at the top and all the Sharepoint products are at the bottom.

Products Released
General Availability Date
Mainstream Support End Date
Extended Support End Date
Service Pack Support End Date

Windows 2000 Advanced Server

Windows 2000 Datacenter Server

Windows 2000 Professional Edition

Windows 2000 Server


Blue Surf Tech is now Surfpoint Technology, Inc.,  providing consulting services for large enterprises and medium-sized businesses. Located in San Diego, California, Surfpoint Technology offers expertise in Microsoft based technology solutions.

By |January 17th, 2011|Uncategorized|0 Comments|