SharePoint Pre-Upgrade Checker Fails

The Pre-Upgrade Checker for Microsoft Office SharePoint Server 2007 is designed to verify that your existing installation of SharePoint 2007 is ready for the upgrade to SharePoint 2010.  It verifies that the OS is the correct version, that there are no orphan sites or sub-sites, it verifies the web config file, the SQL database version and a host of other items. 

When going to run the Pre-Upgrade Checker included with SharePoint 2007, Service Pack 2, we got a nasty little surprise.  The Pre-Upgrade Checker told us that there was a problem with the database schema.  Uh-oh. 

It returned a potential blocking issue that our SQL database schema had some serious user modifications.  It read, in part:

Potential Upgrade Blocking Issues
Issue : Content database with modified database schemas
User modifications to the SharePoint content database, including but not limited to table schemas, index, stored procedures, are not supported and will cause upgrade to future versions of SharePoint to fail.  The databases in the following list seem to have been modified from the original schema:
Data Source=SERVER\SHAREPOINT;Initial Catalog=Content_Database;Integrated Security=True;Enlist=False;Connect Timeout=15


That could be a problem.  We went to the logs and had a look:

[InvalidDatabaseSchema] [DEBUG] [6/14/2010 10:39:19 AM]: Checking schemas in content database Content_Database...
[DatabaseSchema] [ERROR] [6/14/2010 10:39:32 AM]: [Content_Database].[sysdiagrams] EXTRA Table
[DatabaseSchema] [ERROR] [6/14/2010 10:39:36 AM]: [Content_Database] NON EQUAL Property Tables
[SPObjectProcessor] [DEBUG] [6/14/2010 10:39:42 AM]: The rule failed.


It appeared as if there was an extra table inserted into the database, one that was not present in the original SharePoint database schema.  Somehow, there was a table called sysdiagrams that was not created by SharePoint, and it was causing a mismatch with the Pre-Upgrade Checker.

Upon a little investigation within the SQL Server, the dbo.sysdiagrams table was located at Content_Database -> Tables -> System Tables. 

How was the dbo.sysdiagrams table created?  A little bit of searching revealed the answer.  If someone clicks on the Database Diagrams within your database, it will immediate prompt you to create the necessary objects for for database diagramming.  Clicking “Yes” to this prompt will result in the creation of the dbo.sysdiagrams tables and some related stored procedures.  While these don’t pose any obvious risks to SharePoint as the diagrams relate only to the database, the Pre-Upgrade Checker will throw an error if they are present. 

To correct the error, and allow the Pre-Upgrade Checker to run successfully, we performed the following:

• We backed up the SharePoint_Content Database before making any changes.  This is critical in case something goes wrong.
• I deleted the dbo.sysdiagrams table from Content_Database -> Tables -> System Tables
• I deleted the following stored procedures from Content_Database –> Programmability -> Stored Procedures -> System Stored Procedures

• sp_upgraddiagrams
• sp_helpdiagrams
• sp_helpdiagramdefinition
• sp_creatediagram
• sp_renamediagram
• sp_alterdiagram
• sp_dropdiagram

The screenshot  below illustrates these a little better.


After deleting the table and the stored procedures, we were able to run the SharePoint Pre-Upgrade Checker and our SharePoint site passed every check.

Missing User Name in SharePoint 2010

This situation occurred after the upgrade from MOSS 2007 to MSS 2010.  When adding a document, or listing a document owner, the user ID of the author of the document does not appear.  Typically a list item will read "Added By domain\username".  In this case it read "Added by [blank].  Permissions within the document library and across the SharePoint Farm are still enforced, and there appears to be no loss in functionality, other than the User ID is simply not displayed.  The picture below is from a site that had not had the visual upgrade applied.

To correct this, I logged in as an Administrator and added the user account (domain\user_acct) to the Site Collections Administrators group in SharePoint. This was in

Site Home -> Site Settings -> Site Collection Administrators

and added the user account temporarily to the Site Collection Admin group.  I resolved the name to be sure it was spelled correctly, and then clicked OK.

The 'modified by' field / attribute now displays properly.

I then removed the user account from the Site Collections Administrators group, and the 'modified by' attribute continued to display the user name correctly.

I still don’t know what caused this, but at least the problem has been corrected.

There is an official fix from Microsoft regarding the SharePoint 2007 Service Pack 2 expiration date issue. This fix can be applied either before or after the installation of Service Pack 2.

For all the details and the download the fix, visit http://support.microsoft.com/kb/971620

SharePoint Blog Posts and Searching

Yet again, this was something that I found out the hard way.  I created a simple, out of the box site based using the blog site template.  The site created successfully, and myself and my colleagues started posting.  After having generated several blog entries, I started a full crawl to get the blog posts included in the Search indexes. 

Once the crawl was completed, I performed a search against the contents of the blog posts.  Search was unable to index the contents of the blog posts.  It had crawled the titles of the posts, and linked to the page containing the list of posts.  Search would neither crawl posts nor display links to the actual blog post items themselves.  it always returned me to the entire list.

This just didn’t make any sense at all.  How could SharePoint offer Search, but not be able to crawl or index any of it’s own content? 

I thought that it might have something to do with the fact that blog post items are created as “enhanced rich text” instead of just “rich text” or “plain text”.

As a test, I went into the properties of the ‘body’ column of the post item and changed it from “enhanced rich text” to just “rich text”.  I initiated another full crawl (it’s a small MOSS site) and I got the same results (or rather, lack of results).  I also tried as plain text.  No luck.  So, there is something inherently different about blog posts in SharePoint that make it difficult for SharePoint Search to index.  I’m still investigating why this is.

As an alternative, I discovered the Community Kit for SharePoint (CKS) which has an Enhanced Blog Edition.  This SharePoint Solution Package is pretty straightforward to install.  The blog posts created when using it are searchable and the search results list directly to the post item itself, not back to the list of posts.  Beyond that, it has many themes to choose from and offers a few more features than the out of the box SharePoint Blog site.  You can find it on CodePlex.

App Offline File

A colleague pointed out this extremely useful tip for a quick and easy way to provide a friendly message to users when a SharePoint site is being upgraded or changes are  occurring behind the scenes.  This applies not just to SharePoint 2007 sites, but any .NET 2.0 application.

By simply placing an HTML file named app_offline.htm in the root of your web application (not necessarily the root of your web server), your users will get your custom HTML message instead of a nasty ASP.NET error message.   One caveat to this is to be sure that the file is at least 512 bytes in size.  If you don’t have enough text in your custom app_offline.htm page, just add a series of hidden comments. 

The beauty of this is that you can have this file ready and waiting, and it only takes a few seconds to copy it to the right folder before performing any maintenance. 

If you have a lot of SharePoint web applications and you were not consistent in your naming of their virtual directories (like I did), right click on your SharePoint web site in IIS 7, and select Explore.  This will open the root of your web application on the file system.  Drag and drop your app_offline.htm file to this folder.  When your maintenance is complete, drag this file out the folder.

A great resource and a simple example of this can be found at ScottGu's blog site.

SharePoint 2007 Service Pack 2 Expiration

Just saw on the Microsoft SharePoint Team blog that SharePoint 2007 Service Pack 2 contains a bug that activates a 'trial-mode' counter that will cause your SharePoint sites to expire after 180 days, just like an evaluation copy would.

There is no risk to data or system configuration or to any custom code, but your SharePoint servers will become inaccessible after the 180 day mark.

The products that this affects include Office SharePoint Server 2007, Form Server 2007, Search Server 2008 and Search Server 2008 Express. Windows SharePoint Services 3.0 in not affected.

Microsoft is working on a hotfix, which will be identified by KB article 971620. It is not currently available.

Create Your Own SharePoint SP2 SlipStream

1. Copy the original media to a folder on the local drive of the server. For example, c:\sharepoint_install.
2. Download WSS Service Pack 2 (wssv3sp2-kb953338-x86-fullfile-en-us.exe) KB Article 953338
3. Download Office SharePoint Server 2007 Service Pack 2 (officeserver2007sp2-kb953334-x86-fullfile-en-us.exe) KB Article 953334
4. Create the updates folder c:\sharepoint_install\updates
5. Extract both the WSS and the MOSS Service Pack files to the \updates folder of the original installation media. For example – c:\sharepoint_install\updates
   

For WSS 3.0 SP1

• Run the following command as an administrator:

c:\wssv3sp1-kb936988-x86-fullfile-en-us.exe /extract:c:\sharepoint_install\updates

• You will see a dialog box asking you to accept the license terms. Check the box and click continue. To suppress the dialog box add the /quiet switch when extracting the Service Pack files.
• The files will extract to the \updates folder.
  

For MOSS SP1

• Run the following command as an administrator

c:\officeserver2007sp1-kb936984-x86-fullfile-en-us.exe /extract:c:\sharepoint_install\updates

• You will see a dialog box asking you to accept the license terms. Check the box and click continue. To suppress the dialog box add the /quiet switch when extracting the Service Pack files.
• The files will extract to the \updates folder.

You now have an up-to-date installation package for installing SharePoint.