Upgrade – Page 2 – The White Pages

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

Confusion When Upgrading from SharePoint 2007 to SharePoint 2010 In Place

Published April 30, 2010

When You install SharePoint 2010 on a server that already has SharePoint 2007 installed on it, it will install all of the 2010 bits on that server in a side-by-side fashion with the 2007 bits. This is a good thing, for all sorts of reasons. However, it can be the source of some confusion, and at least once already in my case, an opportunity for slip ups.

After you run the new SharePoint Products Configuration Wizard (note the new lack of “And Technologies” in the name), you will have converted your farm, which is all of your databases. However, you still have access to all of the code from the 2007 install. This includes the SharePoint Products and Technologies Configuration wizard from the 2007 install.

This can be a real problem, particularly if you have always used a shortcut to run it, and you expect the shortcut to be upgraded. It won’t be… Take care that you’re running the right tool. It’s now installed off the start menu in the “Microsoft SharePoint 2010 Products” folder, and its name is slightly different,“SharePoint 2010 Products Configuration Wizard”. The chrome around it is slightly different,but not enough to tell, but one visual clue that you’ll get while inside the wizard is that the prompt that warns you about services restarting has “v4” in most of the Service names.

Required SQL Server Versions and Patch Levels for SharePoint 2010

Published April 28, 2010

Microsoft has been very clear about the requirements for installing SharePoint 2010. The biggest thing in this release is that it’s 64 bit only, not just on the Operating System side, but also in the SQL Server requirements. In addition, it’s also quite fussy about which versions it supports.

On the operating system side, a complete list can be found here. On the SQL Server side, it’s generally thought that it supports only SQL Server 2008 and above. However, this isn’t true – it supports SQL Server 2005 – provided that it’s 64 bit mode. However, the devil is in the details. It’s very specific about the patch level that you’re running. Glenn Berry has a list of the supported versions, with patch levels, and the SQL script for determining your precise version levels. There are only 3, so I’ll repeat them below:

SQL Server 2005 SP3 CU3 (Build 4220) or greater
SQL Server 2008 SP1 CU2 (Build 2714) or greater
SQL Server 2008 R2

The kicker is that neither the SP3 level of 2005, nor the SP1 level of 2008 will cut it,and you will have a failure if you do not conform to these versions. This failure will not appear until you run the SharePoint Products configuration wizard,which is pretty much past the point of commitment. In addition, the stsam.exe –o preupgradecheck command on a 2007 farm does not appear to detect this deficiency (at least it hasn’t yet in my experience).

Do yourself a favour, and patch up your SQL servers before you start your install/upgrade. The most recent cumulative update packages, as of this writing are:

I’ve used CU7 for 2008 on my installs thus far, and can attest that it works. I’m still waiting, hopefully not long now, for 2008 R2…