Home > Administration and Configuartion, Microsoft Office SharePoint Server 2007 > “User modifications to the SharePoint content database” error message when you upgrade a SharePoint Server 2007 content database to SharePoint Server 2010

“User modifications to the SharePoint content database” error message when you upgrade a SharePoint Server 2007 content database to SharePoint Server 2010

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 calledsysdiagrams 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.
stored_procs

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.

references:

http://support.microsoft.com/kb/982579

http://selfinflictedsharepoint.blogspot.com/2010/06/sharepoint-pre-upgrade-checker-fails.html

Advertisements
  1. March 6, 2012 at 8:00 am

    I just like the valuable information you provide for your articles. I’ll bookmark your blog and test again right here frequently. I am somewhat sure I will learn many new stuff right right here! Best of luck for the following!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: