Hannah Scott's Blog: List View Lookup Threshold

Hannah Scott's Blog: List View Lookup Threshold

Alerts Not Working SharePoint 2007

Over the past several months I have written a couple of solutions that rely on SharePoint Alerts in order to let individuals in the organization know when activities occur that affect them.

Because these solutions utilize the SharePoint's Alerts mechanism, it is vitally important that the Alert infrastructure functions properly, however this is rarely the case in our Farm.

Our topology is a Medium Server Farm with 2 WFEs, 1 App Server, and a SQL cluster for the databases.

The issue that I have been dealing with is that for an as yet undetermined reason, the server that has established the TimerLock for a particular Site Collection's content database will no longer be able to send emails.

I am able to identify the server which has the TimerLock by running the following SQL command against the content database for the site collection containing the list on which alerts are set:

USE content_database

SELECT * FROM timerlock WITH (nolock)

These are Immediate Alerts that I am working with so in order to see if they get queued up, I query the eventcache table, again in the content database for the site collection containing the list on which alerts are set:

USE content_database
SELECT * FROM eventcache WITH (nolock) WHERE EventData is not null

My observations show that when the Timer Job runs (owstimer.exe), these events are processed and subsequently removed from the eventcache table leading me to believe that everything is working fine.

I've had my network security guy take a look at the firewall traffic, and he can see that traffic from the SharePoint server with the TimerLock to the SMTP server makes it through the firewall without issue, however no email is ever received for the alerts. It should be noted that at the same time that this server fails to send email, the other WFE may have a TimerLock for a different Site Collection's database, and those Alerts send email just fine!

Although everything appears to be working as it should, I am intermittently  left without alert emails.

The one thing that seems to work is to reset the local cache on the server that has the Timer Lock. This is accomplished by performing the following actions:

On the server with the Timer Lock:

1. Stop the Windows SharePoint Services Timer service
2. Navigate to "C:\Documents and Settings\All Users\Application Data\Microsoft\SharePoint\Config\ and delete all the .xml files - DO NOT DELETE THE cache.ini FILE!
3. Open the cache.ini file in notepad and change the number value to 1 then save the file.
4. Start the Windows SharePoint Services Timer service.

Once this action is taken, it usually takes an hour or two before alerts start processing though this server again. In the mean time, any activities that trigger alerts for this server will be queued up in the eventcache table and will be sent when the server resumes processing.

UPDATE:

I am happy to report that since I added a scheduled job to run the following batch script every morning at 4:45 am, Alerts have been runnining without fail. In order for this to work, you need to make a copy of the cache.ini file with the number value set to 1 and placed it in the C:\Documents and Settings\All Users\Application Data\Microsoft\SharePoint\Config\ directory.

net stop "Windows SharePoint Services Timer"

del /F /Q "C:\Documents and Settings\All Users\Application Data\Microsoft\SharePoint\Config\a58ec05c-344f-487c-a8e6-cf0365b86458\*.*"

xcopy "C:\Documents and Settings\All Users\Application Data\Microsoft\SharePoint\Config\cache.ini" "C:\Documents and Settings\All Users\Application Data\Microsoft\SharePoint\Config\a58ec05c-344f-487c-a8e6-cf0365b86458\*.ini" /Y

net start "Windows SharePoint Services Timer"

Reference
 More information about clearing the file cache is available from http://support.microsoft.com/kb/939308

Failed : Pending upgrade operation detected in this farm

Issue:

Pending upgrade is detected on this farm. Either upgrade has not been run, or has failed after a recent update of the SharePoint software.
Run the SharePoint Products and Technologies Configuration Wizard to finish the pending upgrade. For more information about this rule, see KB article 954775 in the rule article list at http://go.microsoft.com/fwlink/?LinkID=120257.

I re-ran the SharePoint Products and Technologies Configuration Wizard on all the servers in the Farm (2 -WFE - 1 - APP - 1 Index) and still encountered this Failure

Final Solution:

I ran the following command from the command line on the Apps/Admin server:

psconfig -cmd upgrade -inplace b2b -wait

Explanation:

When updating your MOSS 2007 installation to SP2, it asks you to run SharePoint Technology and Products configuration wizard at the end. This wizard runs psconfig command, however it still fails to upgrade the MOSS completely for some reason.

When you run above command, the upgrade is completed. The next time you run the upgrade checker tool, the error should be gone.

Reference:
http://nilaykothari.spaces.live.com/blog/cns!2C4C906977BEA6CB!167.entry

Using View Styles to add document link to Title column

First of all, do not do anything in the following post. It is unsupported and most likely fall under the "Not Best Practices" column.

OK, if you are still reading you must be a rebel and are willing to take a few risks in order to satisfy your users. Read on, but be sure to back up any files that you change, and document the process so that you can repeat it when you upgrade because these changes will definitely be overwritten by Service Packs and upgrades.

Still with me? OK, so here we go...

One of the frustrations I have is that the maximum length for an URL is 255 characters and so it is wise make the names of files in document libraries as short as possible. In order to do this, I routinely drop spaces and vowels in the names of files. While the file name is still readable, it usually ends up a little confusing for users who are browsing my document libraries.

The answer of course is to use the Title field to provide a nice descriptive title for the document. Great answer, however someone failed to provide a Title field that is linked to the document. I find it less than satisfactory that I need to display the Name field (linked to document), or rely on the user to figure out that the Icon is linked to the document in order to provide the user with a link to the document.

Andrew Connell describes a method through which he adds a new site definition and then within this site definition, he creates a new column for creating a link that opens in a new window.
His method could quickly be modified to create a linked title column.

If you are like me however, you already have sites created, and you want to add the linked Title to existing document library views.
The approach that I will describe below uses a custom View Style to modify the existing Title field as it is being rendered in order to encapsulate it with an <a> tag linking to the document.

Background

Each Document Library view uses a View Style to define how the view is
rendered. OOTB view styles are:

• Basic Table
• Document Details
• Newsletter
• Newsletter, no lines
• Shaded
• Preview Pane
• Default

These View Styles are defined in a file called: VWSTYLES.XML that is found in the 12 hive at C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\GLOBAL\XML.

Before you make any changes to this document, MAKE BACKUP COPIES. I highly recommend that you add the file to Source Safe or some other code repository under a SharePoint Customization project. If you mess this file up and do not have a backup, you will be sad, and possibly unemployed.

OK, now that you have safely backed up the VWSTYLES.XML file you may proceed. Open the VWSTYLES.XML file in both a browser window, and a text editor such as notepad. The browser is a great place to read the file because it allows you to collapse different elements and to easily view the hierarchy of the file.

The OOTB VWSTYLES.XML file defines 14 view styles, each with a unique ID.
You can easily create a view style by copying the CAML for an existing view style using the text editor, and then making changes to suit your needs. For this example, copy the the Basic Table view style ( ViewStyle, ID="0" ) to use as a template and paste it at the bottom of the file, just before the closing </ViewStyles> tag. Be sure to get all the CAML code from the opening <ViewStyle ID="0" ... tag and up to and including the closing </ViewStyle> tag.

Since each view style must have a unique ID, change the ID in the new view style from 0 to 50. You will also want to change the DisplayName attribute to "Default with Linked Title", the resulting <ViewStyle> tag should now read as follows:

<ViewStyle ID="50" DisplayName="Default with Linked Title" Preview="_layouts/images/prvbasic.gif" Description="$Resources:core,ViewStyleBasicTableDesc;">

You also need to set the ID value in the g_RequiredFields array declaration found in the <SCRIPT> element. The <Script> element is the last child element of <ViewStyle>.

<Script>g_RequiredFields[50] = new Array;</Script>

While you are in this section, add the Title field as a required field by
adding the following line:

g_RequiredFields[50] ["Title"] = true;

The final script tag should read as:

<Script>g_RequiredFields[50] = new Array; g_RequiredFields[50] ["Title"] =
true;</Script>

Save your changes and run iisreset on your SharePoint Front End server. Open
up a Document Library and either create a new view, or edit an existing view. The new View
Style should now appear in the list under the Styles section as show in the
following example.

Adding to Code to Create a Linked Title Field

So far all we've done is created a new view style that is identical to the Basic Table view style. In order to make the desired changes, we'll have to go a little deeper into the child elements of the <ViewStyle> element.

The following shows the hierarchy of the ViewStyle element that we copied
from the Basic Table ViewStyle with the <ViewBody> and <Fields> elements
expanded:

• <GroupByHeader>
• <GroupByFooter>
• <ViewHeader>
• <ViewBody>
  
  • <HTML>
  • <GetVar Name="AlternateStyle" />
  • <HTML>
  • <IfEqual>
  • <Fields>
    
    • <HTML>
    • <FieldSwitch>
    • <HTML>
    • <Field/>
    • <HTML>
  • </Fields>
  • <HTML>
• </ViewBody>
• <ViewFooter>
• <PagedRowset>
• <PagedClientCallbackRowset>
• <PagedRecurrenceRowset>
• <ViewEmpty>
• <Script>

The ViewBody element defines how the rows of data are going to be rendered. Feel free to investigate the different elements, but for this post, we'll be focusing on the <Fields> element.

Within the <ViewBody> element, locate the Fields child element. The Fields element is used in rendering the list for the browser. When used inside a view, it iterates over the view fields.

Within this element, we want to introduce some logic that will add the document link while iterating over the Title field.

Modify the Fields element to add the IfEqual structure shown in the following example:

• <Fields>
  
  • <HTML>
  • <FieldSwitch>
  • <HTML>
  • <IfEqual>
    
    • <Expr1><Field Name="Title" /></Expr1>
    • <Expr2><Field/></Expr2>
    • <Then>
      
      • <HTML><![CDATA[<a href="]]></HTML>
      • <Field Name="EncodedAbsUrl"/>
      • <HTML><![CDATA[">]]></HTML>
      • <Field/>
      • <HTML><![CDATA[</a>]]></HTML>
    • </Then>
    • <Else>
      
      • <Field/>
    • </Else>
  •  </IfEqual>
  
  
• </Fields>

Save your changes and run iisreset on your SharePoint Front End server. Open up a your Document Library and either create a new view, or edit an existing view. Set the View Style to Default with Linked Title, click Ok and voila, you should now see the document titles displayed as hyperlinks to the document.

Conclusion

As stated previously, the changes that you make to the VWSTYLES.XML file will be overwritten when you apply Service Packs or upgrades to SharePoint. It is very important that you not only make backups of the original file, but also backups of the file after you have made these changes. When, not if, VWSTYLES.XML is overwritten during an update, you will be able to retrieve the backup and restore this functionality.

It is also recommend that you add this procedure to your SharePoint deployment configuration documentation in the form of release notes. These notes should be easy to follow so that anyone in your organization can make these updates after you have won the lottery and moved to Belize : )

Overcoming Read-Only Condition in Datasheet View

I ran into a situation while working on a SharePoint calendar that was set up to require approvals. We decided to add a new column in order to create sub-calendar views that would be filtered off of this new column. The problem was that we had several hunderd events already entered into the calendar, and the thought of opening each item and editing its contents to insert a value in the new column made my user's eyes roll back in their head.

The obvious solution was to use the Open With Access option on the actions menu of the calendar however, after opening the calendar with Access, none of the cells were editable. OK, let's try something else; I selected the All Events view of the calendar and selected Edit in Datasheet, which was now available from the Actions menu. Foiled again, any attempt to edit the cells poped up a Windows Internet Explorer dialog box with the message "The selected cells are read-only."

The solution was ultimately derived from Larry Kuhn's Simplify List Item Approval using the Datasheet View. It seems that the Approve/reject Items view of a list that requries approval for items allows the Approval Status to be edited.

A few clicks reveals that all the columns in this view are editable, so I added the column for my new filter field to this view and then was able to use this Datasheet view to quickly update hundreds of event records in short order.

SharePoint Alerts: Workflow

Overview

The Workflow for SharePoint Category Alerts as described in the overview post is a SharePoint Designer workflow. This workflow is used on the Master Calendar list to set the StatusType based on that status of the event: Approved, Pending, or Rejected. The StatusType field is required due to the fact that you cannot access the Approval Status field in a formula for a calculated field. The calculated fields BGColor, Border, and Color use the StatusType field for applying the appropriate color based on the event's Approval Status.

Setting up the Workflow

On the Master Calendar SharePoint list set the following Conditions and Actions within an If/Else if structure:

Condition: If Approval Status equals 0;#Approved
Action: Set StatusType to 0

Condition: Else If Approval Status equals 1;#Rejected
Action: Set StatusType to 1

Condition: Else If Approval Status equals 2;#Pending
Action: Set StatusType to 2

Condition: Else If Approval Status equals 3;#Draft
Action: Set StatusType to 3

Condition: Else If Approval Status equals 4;#Scheduled
Action: Set StatusType to 4

That's it for the workflow. This workflow should be set to run Automatically when a new list item is created.

SharePoint Alerts

We have a Master Calendar for the organization the includes calendar events that are identified by category. Each category also belongs to a category grouping as shown in the following sample hierarchy:

• Accountability
• Alt MSA Window
• Assessment Training
• Curriculum & Instruction
  
  • Athletic Directors
  • General Curriculum Committee

The requirement was to provide the ability for users to subscribe to a category of events so the creation of a new event within a subscribed category, or any change to an event that belonged to a subscribed category would generate an e-mail alert for a user.
For this post, I am going to describe the component of the solutions with subsequent posts on the details of the customization required.
The components of this solution consist of the following:

SQL Database

The SQL database consists of a single table for maintaining the list of user subscription records. (You could also use a SharePoint list to maintain this data)

CREATE TABLE [dbo].[MC_Subscriptions](IDENTITY(1,1) NOT NULL,NOT NULL,NOT NULL,(50) NULL,(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,CONSTRAINT [PK_MC_Subscriptions] PRIMARY KEY CLUSTERED [SubscriptionId] [int] [SubscriptionUserEid] [int] [SubscriptionCategory] [int] [TimeStamp] [binary] [SubscriptionUserEmail] [nvarchar]

SharePoint Site and Lists

A SharePoint Site created from the Blank site template. This site contains two lists: CalendarCategories and Master Calendar.
CalendarCategories List: Custom SharePoint List for managing the Category and Category Grouping values

Type	Required
Title	Single line of text	required
Group	Choice	required
CategoryChoice	Calculated (calculation based on other columns) =CONCATENATE(Group,": ",Title)
CategoryDescription	Multiple lines of text
Created By	Person or Group
Modified By	Person or Group

Master Calendar List: Calendar list for maintaining events. This list is configured with a Content Type called Event that enables certain field to be hidden from the user when they are entering events, but more on this later. List definition:

Column	Type	Used in
BGColor	Calculated (calculation based on other columns) =IF(StatusType=0,"#FFFFFF",IF(StatusType=1,"#DEDEDE",IF(StatusType=2,"#FFFF00",IF(StatusType=3,"#FFFFEE",IF(StatusType=4,"#DDEEFF","#EEDDFF")))))	Event
Border	Calculated (calculation based on other columns) =IF(StatusType=1,"#FF0000","#CCCCCC")	Event
Category	Lookup	Event
Color	Calculated (calculation based on other columns) =IF(StatusType=1,"#A6A6A","#000000")	Event
Description	Multiple lines of text	Event
Display	Calculated (calculation based on other columns) ="<DIV style='border: 1px "&Border&" solid; padding:5px; margin: 0px; color:"&Color&"; background-color:"&BGColor&";'/>"&Title&"</DIV>"	Event
End Time	Date and Time	Event
Location	Single line of text	Event
Start Time	Date and Time	Event
StatusType	Number	Event
Title	Single line of text	Event
WeekDisplay	Calculated (calculation based on other columns) ="<SPAN style='border: none; padding:1px 10px 1px 10px; margin: 0px;color:"&Color&"; background-color:"&BGColor&";'/>"&Title&"</SPAN>"	Event
Created By	Person or Group
Modified By	Person or Group

List Content Type: Event

Columns Name Type Status Source Title Single line of text Required Item Category Lookup Required Location Single line of text Optional Event Start Time Date and Time Required Event End Time Date and Time Required Event Description Multiple lines of text Optional Event All Day Event All Day Event Optional Event Recurrence Recurrence Optional Event Workspace Cross Project Link Optional Event StatusType Number Hidden Color Calculated Hidden Border Calculated Hidden BGColor Calculated Hidden Display Calculated Hidden WeekDisplay Calculated Hidden

Workflow

A workflow is used on the Master Calendar list to set the StatusType based on that status of the event: Approved, Pending, or Rejected. The StatusType field is required due to the fact that you cannot access the Approval Status field in a formula for a calculated field. The calculated fields BGColor, Border, and Color use the StatusType field for applying the appropriate color based on the event's Approval Status. 

Web Parts

There are two Web Parts used in this solution. The first web part is used to display a summary of the user's category subscriptions. I placed this web part on the home page of the Master Calendar site along side the Month View of the Master Calendar list. The second web part allows the user to manage their subscription alerts. This Manage Alert subscriptions web part resides on a page within the AppPages document library. when creating this library, select Web Part Page as the default document template type.

Custom E-mail Alert Event Handler

In order to fire off emails to users who are subscribe to a category, the solution intercepts alerts that are triggered based on an alert that is set up for a designated user. The interception occurs in a custom e-mail alert notification handler that was create according to the information provided in kb948321. Rather than modifying the Events alert template, I also created a new alert template within the customalerttemplates.xml file and registered this template with my Master Calendar event list so that it specifically used this custom event template, but other lists used the standard template.
I wrote a simple console app to register the alert template to my list:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;

namespace RegisterAlertTemplateToList
{
class Program
{
static void Main(string[] args)
{
using (SPSite site = new SPSite("http://mysite"))
{
using (SPWeb web = site.OpenWeb("mc"))
{
SPList list = web.Lists["Master Calendar"];
SPAlertTemplateCollection atc = new SPAlertTemplateCollection((SPWebService)site.WebApplication.Parent);
SPAlertTemplate newTemplate = atc["SPAlertTemplateType.CustomMasterCalendar"];
if (newTemplate == null)
{
throw new Exception(String.Format("Alert Template not found. List: {0}", list.Title));
}
else
{
list.AlertTemplate = newTemplate;
list.ParentWeb.AllowUnsafeUpdates = true;
list.Update();
}
}
}
}
}

Next:

Setting up the Workflow

References:

• How to attach a custom alert to a particular list
• Custom alert in SharePoint - SharePoint Kings
• Custom Alert Handlers: Part 1 of 2
• Troubleshooting Alerts