Over the past few weeks I have been working on fixing up a site created in SharePoint Designer, converting it to a WSPBuilder solution package so that it is easier to maintain and deploy. In some places the site is an impressive example of what you can do in SharePoint even without the proper tools. In others it’s an example of just how badly SharePoint Designer lets you screw up.

 

An important part of this project is copying data from the existing production site to the new one created from the solution package. The existing site has got into a state where standard methods don’t work - the 20MB database produces a 350MB backup file that takes forever to restore unsuccessfully. Echo also has problems with the site - I had to leave it running overnight to generate the transfer script, and after that it just produced errors importing every row. As a result I have written a custom migration app that accesses list items through the same interface as the standard front end, that being the only one that has had any sort of testing.

 

Yesterday I updated one of the list schemas in the solution, using a list template saved from the production site that had some additional columns added. On running the import, a new error message showed up - Import process failed with a ComException 0x80040E14, which means “Something went wrong with the database”.

 

The SharePoint Log contains a more helpful error message:

 

Unexpected query execution failure, error code 8143. Additional error information from SQL Server is included below. “Parameter ‘@nvarchar10’ was supplied multiple times.”

 

I searched schema.xml for ColName=”nvarchar10” and it showed up twice, as did several other column names. This file was created by saving a list template on the working production system, so I was fairly surprised to find invalid columns. It seems that SharePoint is quite happy to allow multiple fields to use a single database column, probably as part of supporting the system fields that use fixed column names. It is only when you try to use those columns together (as happens on an import setting all fields) that the system notices something isn’t right.

 

To fix the problem, I removed all the column name attributes (except the ones on system columns). This  means that when the list is created, SharePoint will create a new column name, ensuring that there aren’t any duplicates.

 

The site/list I initially deployed the schema to can be considered permanently broken, as any existing colname settings will not be changed. However, creating a new site/list using the updated schema allows the import to complete without errors.

 

I can’t be sure exactly how the site ended up in this state, but the most likely explanation is that someone created additional columns by copying CAML from another list schema without changing any of the attributes. Quite understandable given the state of Microsoft’s CAML documentation.