Tag: SharePoint 2010

SharePoint 2010 Upgrade breaks Microsoft Access Client Application

Published September 16, 2011

I have been doing quite a few 2007-2010 upgrades lately, and suffering the appropriate slings and arrows. A recent upgrade resulted in a few issues, the strangest one was that Microsoft Access could no longer open a SharePoint list.

For quite some time now, Microsoft has been able to read and write data from SharePoint lists as if they were active Access tables. This is distinct from Access Services, which ships with the Enterprise version of SharePoint Server. Access Services lets you “convert” your entire Access application to a SharePoint site at which point the Access client is no longer required (for a user).

Our situation was much simpler. We were dealing with a Power user that was good with Access, and had leveraged the list read/write capability quite heavily with 2007. However, after the upgrade, Access 2007 couldn’t open some of the lists that it could previously. Compounding this problem was that Access 2010 didn’t have this problem on the lists in question. The browser could also open these lists just fine.

The answer to this one came from what appeared to be a different problem. Some of the other lists in the site couldn’t be opened by the browser. Instead, the user received the message “The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator”:

SharePoint has a bad reputation for “Unknown Error” messages, but this one is really quite good. This one pointed squarely at the list throttling features available in SharePoint 2010 that of course weren’t there in 2007. Basically, 2010 allows an administrator to throttle, or prevent poorly performing functions from slowing down the system for everyone. One such expensive operation is performing lookups, and the default limit is set to 8.

Dina Ayoub has a good post here on the throttling features if you would like to learn more, but the important thing to note here is that this setting affects not just lookup fields, but Person/Group and Workflow Status fields as well, so if you have 8 or more of them, the list will simply stop working.

This setting is scoped to the application level, so if it is changed, you affect all site collections in that application. (It also means that you can’t change it at all in Office 365.) You set it through the Resource Throttling settings in Central Administration. Once in CA, click on Application Management, highlight the application to be changed, and in the General Settings dropdown, pick Resource Throttling.

Scroll down to the section titled “List View Lookup Threshold”:

Here, you can simply increase its value to where you need it.

Changing the values should be done with considerable care. These throttling features were implemented for very good reasons, and changing them risks overloading your SQL server. A much better approach would be to go back and rethink the design of your list, if that’s an option. If it isn’t then this is a decent plan B. You can always buy more hardware…….

So this fixed our post upgrade list issue in the browser, how does this relate to our Access problem? Well, it turns out that they were one and the same, just manifesting differently. It seems that Access does something when it opens a list that adds a few more lookup type items to the Query, or at least it behaves that way. It also appears that Access 2010 and Access 2007 behave differently in this regard. In the end, increasing this value sufficiently solved the Access problems.

I haven’t found anything definitive out there, but anecdotally at least, you should be aware that when you use Access to open up a SharePoint list, you pay a “”List View Lookup Threshold” penalty.

1 Comment

SharePoint Upgrade Failure – Orphaned Documents not Orphaned Lists

Published August 31, 2011

Upgrading to SharePoint 2010 from 2007 is well worthwhile, and is significantly easier than the upgrade from 2003 to 2007 was. With that said, there are a number of things to look out for, and a number of bumps along the way. Running the stsadm –o preupgradecheck on your SharePoint 2007 farm identifies a number of the potential pitfalls and alerts you to their dangers.

Recently, I was at a client site doing a database attach upgrade, and we ran into a warning about orphaned objects. Orphaned objects will cause the upgrade to fail, so it needs to be remedied prior to the upgrade.

Orphaned objects are items that exist in the database, but aren’t properly connected to anything in the site collection. Orphaned objects are typically sites or lists. Joel Oleson has a good article on deleting orphaned sites, Orphaned lists are trickier with much of the guidance pointing to removing and then re-adding the content database. Also there is an stsadm command that should repair orphaned objects. The syntax is:

stsadm –o databaserepair –url yoururl –databasename contentDB –deletecorruption

Using the –deletecorruption directive goes ahead and fixes the problem, without it, it just tells you where the problem lies.

Unfortunately, nothing worked in my case. Running databaserepair simply resulted in a return that looked like the following:

<OrphanedObjects Count="1">
  <Orphan Type="SPList" Id="{787A6375-ABA3-4475-AE64-230853EB4448}" SiteId="{13AB0F9E-386B-4128-916C-E70BFC6A45F3}" />
</OrphanedObjects>

This discussion (in which the response marked as an answer isn’t the answer) got me pointed in the right direction. I am loathe to do anything directly in the content database, but if I had an orphan, I could at least find it there. I therefore opened up the AllLists table in the content database and did a SQL search for my list, like:

select * from AllLists Where tp_ID=’787A6375-ABA3-4475-AE64-230853EB4448’

Unfortunately, I got no results. This was baffling.

I had the GUID of the offending list, but it didn’t exist in the list table. I started rooting around elsewhere. It wasn’t too long before I found a reference to it in the AllDocs table, in the ListId column. To me, that meant that I didn’t have an orphaned list, but an orphaned document that was referring to a non-existent list..

After determining that the document was in fact disposable, I deleted its record with some simple SQL

DELETE FROM AllDocs Where ListID = ‘787A6375-ABA3-4475-AE64-230853EB4448’

Once that was done, I ran the preupgrade check, and all was clear – no more orphaned objects. We could proceed to the next roadblock… (more on that later).

This solution worked in my case, but as it involves monkeying with the content database, use at your own risk, and whatever you do – have a backup of the database available!

2 Comments

Using Nintex Workflow and SharePoint to Create InfoPath Forms on a Schedule

Published June 24, 2011

A common customer requirement that we have come across involves periodic reporting requirements. Basically a number of departments, or individuals are required to fill out a form on a periodic basis, and that form must be run through an approval workflow of some sort. This scenario can easily be accommodated with Nintex Workflow and SharePoint.

The solution will require at least two lists:

Reporters – this list will contain all of the entities that are required to do the reporting, along with parameters that will drive the destination approval workflow. We will use a custom SharePoint list for this purpose.

Reports – this will be a forms library that will contain the submitted forms, along with the approval workflow itself. Technically, this does not need to be a forms library with InfoPath 2010, but if the form is to contain embedded sections, it will need to be.

In summary, a workflow will be scheduled to run on all of the items in the Reporters list on a periodic basis. This workflow will create new items in the Reports library, and set a number of their values. The creation of these forms will initiate another workflow that will notify the reporter that the report is due, allow them to enter form data, and then move the form through an approval process. This walkthrough will cover only the basics, but all of the constituent items can be elaborated upon.

Step 1 – Create and publish a form

I’m going to assume that you know how to work with InfoPath forms in SharePoint already, but if not, there is a good walkthrough on this topic here. There are a few differences that you need to take into account when you want workflows (or data views) to be able to populate form data, so I’ll outline the steps required here. We’ll be working with the following simple form:

Not only will we want to promote the two highlighted fields into the SharePoint library, but we’ll want our automated workflow to fill them out for us ahead of time. The expense detail fields are repeating fields, and they can’t be promoted, except in aggregate. To do this, we’ll first need to publish our form to our SharePoint library. We’ll want to add or change our promoted fields, so we can’t use Quick Publish at this point. From the File menu, click Publish, and select SharePoint Server.

Follow the prompts for publishing, but stop at the field promotion screen. click add to add in a field to be promoted.

From here, you can select the fields that you want to promote. The critical part is that you must select the “Allow users to edit data in this field by using a datasheet or properties page” option. This allows direct editing of form data from SharePoint, and consequently, workflow.

Not all InfoPath field types support this capability, so you will need to work within these limits.

Complete the publishing wizard and test the form. Once you are happy with the result, it’s time to move to step 2.

Step 2 – Modify the form template to allow automated creation

Workflow processes, including Nintex workflows can create list items, documents, etc. This is also true of InfoPath forms. However, due to the way that an InfoPath form works, it’s not as simple as creating a new item using the built in template. With InfoPath, the standard template that gets published by the Designer is an XSN file, whereas the finished product is an XML file. Basically, we need to create a finished product, and use it as the template instead of the Designer generated XSN. We will want to keep the XSN around for future modifications though.

This step is also only pertinent if you’re using a forms library. If you’re just using InfoPath as a form for a SharePoint list, you can skip this step.

First, create a new item using your form. Immediately save, or submit your form, depending on how you’ve built it. In my case, I save it, and call it “ExpenseReportBlank”. Once the form is closed, you will see a new entry in the library. Now, you want to download it to a local file system. To do so, hover over the form, select the dropdown, and from the “Send To” item, select download.

The next step is to make this file available to the library as a template. To do that, we need to access the “Forms” folder for the library. The easiest way to do that is by opening the library in the Explorer view, navigating to the forms library, and copying the file to it.

Once it’s there, we can set it as the template for the library. To do so, click on Library settings, in the ribbon, and then Advanced settings. From there, you edit the Template URL to point to your new template. Once you do that, you need to click OK, and then come back into Advanced settings. Doing so “registers” the template. Once that’s done, you need to set the “Allow management of content types” option to yes, otherwise the form will not open in the browser.

Click OK, and return to the library. Test out your new template to make sure that all is well. If you’re happy with your form, we’re ready for the automation.

Step 3 – Add the workflow specific columns

At this point, we want to add in all of the columns that will drive our workflow. In a simple approval, you might want to use submitter, approver, and status. The submitter and approver fields will be “Person or Group” fields, and status will be a choice field consisting of submitted, approved, and rejected as possible choices.

The final list of columns in our example can be seen below:

Of course, the exact columns that you will need will vary with your workflow. We won’t be developing the actual approval workflow here today, but these are typically what you’d want at a minimum, and what we’ll be populating automatically.

Step 4 – Create the “Reporters” List

For the next step, we will create the list that contains the workflow to create the scheduled reports using the custom list template. The list will contain fields for submitter and approver. The title field will be renamed to employee in order to get the full display name of the employee as opposed to the account name (there are other ways to accomplish this, but this is simple for our purposes).

Next, create at least one entry in the list. We’ll use this for testing.

Step 5 – Build the Form Creation Workflow

The workflow to create the for is relatively straightforward. It needs to accomplish 3 things in our example.

Get the current date to be used for our Week Ending field
Build a form title that will be unique (Concatenate Employee and Date)
Create a new Report in the reports library

The complete workflow appears below:

The first three actions are just variable assignments and text manipulation, but the last action is the one that creates the report and is worth opening:

Once the workflow is built, go ahead and run it. If everything is working, you will see a new report in the Expense Reports library.

If you have an approval workflow defined to start when items are created in the destination library, it will be running. The last thing we need to do now is to schedule the workflows.

Step 6 – Schedule the Workflows

Nintex supports the scheduling of workflows. Essentially, you pick a time for workflows to start on individual list items. This is done on an item by item basis, and needs to be performed for each item in the source list.

From the “Reporters” list, hover over one of the configuration items, click the dropdown and select “Schedule Workflows”.

You will presented with a screen showing you the scheduled workflows for that item (likely empty). Click the “Add Schedule” button, fill out the schedule form, and click save.

In this case, our workflow “Create New Report” is scheduled to run every Saturday at 2PM indefinitely. This will create a new weekly report for each employee that is configured, and if configured will kick off a review and approval cycle.

4 Comments

Search Results Not Showing For Anonymous Users in A SharePoint Publishing Site

Published April 15, 2011

Whenever dealing with a public facing web site in SharePoint, my typical approach is to first set up the application using Windows authentication only, and then extend the application to an internet zone that would use the public URL, and allow anonymous authentication. Once I make the requisite site collection changes necessary for anonymous access, I actually go and remove NT Authentication from the Internet zone. This has benefits from both a security and testing standpoint.

This always worked like a charm in SharePoint 2007, but with SharePoint 2010, there’s a slight change in behaviour that affects search results. Typically, you will index the default zone URL, and when results are shown, they are translated into the correct zone’s URL. They are also security trimmed according to your credentials.

When I first started doing this with 2010 however, I observed that public (anonymous) users would get no search results whatsoever. A quick scan of the resources out there turn up a number of articles related to this behaviour such as this one, that indicate a change in the search visibility settings are in order. However, this didn’t seem to help my situation, and I suspect that that approach is pertinent to Team sites, not publishing sites.

A little more experimenting revealed the answer. As it turns out, it was my approach that was messing me up. It appears that anonymous visibility is now stored in the index as the content is indexed, instead of when the results are being rendered. Because the application that the indexer is reading from didn’t allow anonymous access, it assumed that the content wasn’t available to anonymous users generally.

The fix for this is simply to turn on anonymous access for your internal zone. This is actually a bit of a pain for internal users because they will not automatically be logged in to the site when they access it, but there are ways around that. Once anonymous access is enabled, all that you need to do is to run a full crawl on your content source, and you’re off to the races.

Hope this helps someone.

2 Comments

Building an Automatic XML Sitemap Generator for your SharePoint Site

Published March 23, 2011

Although SharePoint 2010 provides a top notch environment for building corporate web sites, one of the things that it does not do is to generate an XML sitemap file automatically. This is unfortunate, as this type of file is used by the major search engines to help discover content on your site. Luckily, the development tools for SharePoint make this process relatively straightforward. Below, I’ll walk through the process of creating a branded event receiver to rebuild the site map whenever a page is approved.

In order to follow along, you’ll need a copy of Visual Studio 2010 installed on a machine that also has SharePoint Server installed (SharePoint Foundation won’t cut it for this one – we’re using the publishing features). You’ll also need the Visual Studio Tools for SharePoint installed.

If you don’t want to walk through the whole creation process, and just want a site map builder, you can download the solution file from codeplex here. Just note that your web application will need an internet zone for this to work properly.

1. Create an Event Receiver Project

Open Visual Studio and create a new project. Select the SharePoint node, and the Event Receiver project template. Give the Solution and the Project a name, then click OK.

The project name will be the name of the SharePoint solution. It can be changed later, but it’s much easier to get it right ahead of time. The next prompt will ask for the debugging site, and whether this is a farm, or a sandbox solution. The debugging site will need to have the publishing features enabled (this can be done later, and the debugging site can be changed through the Project Properties). Select a farm solution and click Next.

The next screen will ask what type of event receiver that you want to build. The available options are a function of what is available in your debugging site (chosen previously). For example, the Pages library will not be an option for the event source if the Publishing infrastructure has not been enabled. For our purposes, we want this to un whenever a page in the Pages library has been updated. Therefore we select the type to be List Item Events, the source to be a Pages library, and the event to be “An item was updated”.

Click Finish when done. The system will create a feature and an event receiver for you.

2. Make Any Branding and Name Changes

This is not absolutely necessary, but the first thing that I like to do is to change my assembly name and my root namespace to distinguish the work done by my organization from any other things installed. To do this, you open the project properties page, click the Application tab, and change them there.

The Assembly Name controls the file name of the DLL that is generated, and the root namespace controls where your classes are found in the .Net Framework. Unfortunately changing the root namespace does not update the assembly references in the project, and if you attempt to debug the project at this point, you will receive this oh so helpful error:

“Error occurred in deployment step ‘Activate Features’: Operation is not valid due to the current state of the object.”

What you need to do is to update all references to the old namespace in the project. Specifically, the Elements.xml file in the event receiver folder needs the correct starting namespace. Open the file for editing and replace the old assembly name with your new one.

Save the file, and close it if you wish,but we will be coming back to it.

Next, we want to name our feature. The feature will have an internal name that is used when it is referred to programmatically (through powershell, sysadm, etc) and a display name (title), that will be used in the UI. First we’ll modify the internal name. The easiest way to do this is to open the Features folder, and rename the Feature1 node. We’ll call our feature xmlSiteMapBuilder.

The tools are smart about renaming everything in the features folder. Next, double click on the feature node (in our case, xmlSiteMapBuilder). This opens the feature designer. All that we need to do here is to change the title, the description and the scope. The first two are cosmetic (but important!). However, we want our event receiver to run on all pages in the site collection, so we need to change its scope from Web to Site.

At this point, it’s a good idea to run the project to make sure that everything is OK. One you’ve done so, and the browser window opens, go to Site Actions-Site Settings, and select Site Collection Features. You should see your feature there, in an activated state, with your title and description.

Next, we want to change the name of our event receiver to something other than “EventReceiver1”. click on the EventReciver1 folder and rename it, in our case to PageChangedEventReceiver. Then, rename your EventReceiver1 class in a similar fashion. You will be prompted to update all references to the class when you do this, so select yes. Unfortunately, the updates don’t completely affect our pesky Elements.xml file, and we need to perform these manually. Open this file and change all references to the old name to use the new one.

Now we’re ready to write some code!

3. Add the Logic

You can add all of your code directly into your event receiver class. However in our case, we need to perform the same functions not only when the event fires, but also when the feature is activated. Therefore, we add a new class to the project, simply called Builder. In addition, we will need to access the Microsoft.SharePoint.Publishing namespace, so we need to add a reference to it to our project.

Without going through it line by line, our code will walk through our site collection, find all of the pages, check to see if they have been published and then build a site map entry for each one, using the URL prefix for the Internet zone. The complete code is available on the Codeplex site mentioned above, but the content of the Builder class is below.

Imports System.Text
Imports Microsoft.SharePoint.Publishing
Imports System.IO
Imports Microsoft.SharePoint.Administration
 
Public Class BuilderMain
    Private _siteURL As String
    Private _SiteID As Guid
 
    Dim textWriter As StringBuilder = Nothing
    Dim dateFormatString As String = "yyyy'-'MM'-'dd"
   
    Public Sub New(ByVal siteID As Guid)
        _SiteID = siteID
    End Sub
    Public Sub New(ByVal Url As String)
        Dim st As New SPSite(Url)
 
        _SiteID = st.ID
    End Sub
 
    Public Sub Run()
        Try
            textWriter = New StringBuilder(String.Empty)
         
            Dim site As SPSite = New SPSite(_SiteID, SPUrlZone.Internet)
            With site
                Dim web As SPWeb = site.RootWeb
                With web
                    textWriter.AppendLine("<?xml version=""1.0"" encoding=""UTF-8""?>")
                    textWriter.AppendLine("<urlset xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xsi:schemaLocation=""http://www.sitemaps.org/schemas/sitemap/0.9 http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd"" xmlns=""http://www.sitemaps.org/schemas/sitemap/0.9"">")
                    LoadTreeViewForSubWebs(web) ' kick it off with the root web As 
                    textWriter.AppendLine("</urlset>")
                    Dim stream As MemoryStream = New MemoryStream(Encoding.UTF8.GetBytes(textWriter.ToString()))
                    web.Files.Add("sitemap.xml", stream, True)
                    stream.Close()
                End With
            End With
 
        Catch ex As Exception
            errorHandler(ex, "BuildSitemap")
        End Try
    End Sub
 
    Private Sub LoadTreeViewForSubWebs(ByVal currentWeb As SPWeb)
        Dim pPageCol As PublishingPageCollection = Nothing
        Dim tagLabel As String = String.Empty
        If PublishingWeb.IsPublishingWeb(currentWeb) Then
            Dim pWeb As PublishingWeb = PublishingWeb.GetPublishingWeb(currentWeb)
            pPageCol = pWeb.GetPublishingPages()
        End If
        'create xml link to site
        'to remove the link to the site (without a page), remove the next line
        writeSitemapNode(currentWeb.Url + "/", currentWeb.LastItemModifiedDate.ToString(dateFormatString))
 
        'create xml links to site pages
        If Not pPageCol Is Nothing Then
            LoadTreeViewForSubWebPages(pPageCol)
            pPageCol = Nothing
        End If
        For Each web As SPWeb In currentWeb.Webs
            Dim _file As SPFile = web.GetFile(web.Url.ToString() + "nopost.xml")
            If Not _file.Exists Then
                LoadTreeViewForSubWebs(web)
            End If
        Next
        currentWeb.Close()
 
    End Sub
 
    Private Sub LoadTreeViewForSubWebPages(ByVal currentPages As PublishingPageCollection)
        Dim page As PublishingPage
        For Each page In currentPages
            If page.Url.EndsWith("aspx") Then
                If page.ListItem.HasPublishedVersion Then
                    writeSitemapNode(page.PublishingWeb.Url + "/" + page.Url, page.LastModifiedDate.ToString(dateFormatString))
                End If
 
            End If
        Next
    End Sub
 
    Private Sub writeSitemapNode(ByVal pageLocation As String, ByVal lastModified As String)
        textWriter.AppendLine(vbTab + "<url>")
        'replace secured links with non-secured links
        textWriter.AppendLine(vbTab + vbTab + "<loc>" + pageLocation.Replace("https:", "http:") + "</loc>")
        textWriter.AppendLine(vbTab + vbTab + "<lastmod>" + lastModified + "</lastmod>")
        textWriter.AppendLine(vbTab + "</url>")
    End Sub
 
 
    Private Sub errorHandler(ByVal errorMessage As Exception, ByVal errorLocation As String)
 
        Try
            Dim _eventLog As System.Diagnostics.EventLog = New System.Diagnostics.EventLog("Timer Jobs")
            _eventLog.Source = "Sitemap Generator"
            _eventLog.WriteEntry("Error (" + errorLocation.ToString() + "): " + errorMessage.Message.ToString())
            _eventLog.Close()
            _eventLog = Nothing
        Catch
 
        End Try
    End Sub
End Class
 

Next, we need to call our builder from our event receiver. Our code will go into the ItemUpdated sub. The builder constructor takes either a URL or a Site ID as an argument, and since the item can be obtained through the properties object, our job is pretty straightforward. All we need to do is to check to see if the item has been approved.

Public Overrides Sub ItemUpdated(ByVal properties As SPItemEventProperties) 
   MyBase.ItemUpdated(properties)
   If properties.ListItem.ModerationInformation.Status = SPModerationStatusType.Approved Then
        Dim smb As New BuilderMain(properties.SiteId)
        smb.Run()    
    End If
End Sub

4. Add a Feature Receiver

Of course, we don’t want to wait until a page is edited, we want to build a site map as soon as the feature is activated. To do that, we need to add a feature event receiver. To do this, we simply right click on our feature node (in this case, xmlSiteMapBuilder) and select Add Event Receiver. The designer will open the new class, and the 4 event receivers will be commented out. Simply uncomment the FeatureActivated Sub, and add the required code.

Public Overrides Sub FeatureActivated(ByVal properties As SPFeatureReceiverProperties)
    Dim Parent As SPSite = CType(properties.Feature.Parent, SPSite)
    Dim smb As New BuilderMain(Parent.ID)
    smb.Run()
End Sub

We don’t need to clean anything up when the feature is deactivated, so this is the only code that we need to add. Go ahead and run the project, and you should find a brand new sitemap.xml file in the root of your site collection. You can use SharePoint Designer to see it, or just use the browser with a url of http://yoursitecollectionurl/sitemap.xml

That’s all there is to it. A little bit of code, and you’re well on your way to Search Engine Optimization.

4 Comments