Second Life of a Hungarian SharePoint Geek

May 22, 2007

Primary key violation when using the SPExport object

Filed under: Content Deployment, SharePoint — Tags: , — Peter Holpar @ 23:32

Note: This is a repost, the original one was published on SharePoint Blogs on May 22, 2007.

Last month we made a simple utility that creates backup for a single MOSS list/document library or a given set of lists/document libraries, and imports the backup. It was similar to the code you can read on Ton Stegeman’s blog.

After using it successfully for a few weeks we found that in some cases it throws an exception like this: Violation of PRIMARY KEY constraint ‘PK__#ExportObjects____XXXXXXXX’. Cannot insert duplicate key in object ‘dbo.#ExportObjects’. The exception was thrown at the "Progress: Calculating Objects to Export." phase of the export.

This message was not new for us, because we had this previously when running content deployment in incremental mode. At that time we found that it is a known issue, since Ryan Steeno posted a comment on this on Ton Stegeman’s blog. Later we found a similar post on Jespers’ blog. Unfortunately none of the version setting workarounds suggested (Site Collection Images, Site Collection Style Library, and we tried several others) worked for us, so we left with the full deployment option.

Getting this exception from the utility was a new thing we haven’t read about. Since for this problem we haven’t found workaround and we were curious we decided to create SQL trace to catch the source of the problem.

We found that the SPExport.Run() calls several proc_Depl* stored procedures. In the case of the error, the proc_DeplAddListItemDependencies SP (that is called directly by the proc_DeplAddExportObjectDependencies SP) causes the PKV. We checked the code and found that the first part of this SP is responsible for the error. This SP inserts new records to the #ExportObject table, and although there is a check that only items that have IDs not existing already in the table, nothing guaranties that the ID of the inserted items are unique.

In this case, for the items with type 8 (that seems to be content type) in #ExportObject, the ID is calculated from the last 16 digits of the content type ID. Since the INSERT statement tries to insert a single content type several times (seems to have a single item for each content type –folder pair), the same ID is calculated for the same content type each time, so PKV is not a bug surprise.

We modified (hacked) the proc_DeplAddExportObjectDependencies in our test system to have only a single item for each content type. Alternative is to have several content type – folder pairs for each content types and generate the ID with NEWID(). This latter one also requires a helper temporary table because using NEWID() simple in the SELECT statement would cause additional records because of the DISTINCT used.

With this modification the incremental content deployment is working, and also there is no exception with the export utility. But this is for sure not a suggested (or supported) scenario for a production environment.

After doing this, we found that exporting sites with STSADM is working. Since from the output messages it was clear that the STSADM should use the SPExport API as well, we hoped that by checking its code we will found a correct resolution for our problem.

The site export function in the STSADM is done by Microsoft.SharePoint.StsAdmin.SPExportOperation.Run(). There were several differences between the SPExportSetting we used in our code and the one was used in STSADM. We found that the PKV problem can be solved by using the ExcludeDependencies = true setting.

Today we were notified that the hotfix for the incremental content deployment PKV is available from Microsoft, and we read the same information on William Cornwill’s blog. Although the export utility now runs with this setting we hope the fix will solve the issue of running SPExport with the default SPExportSettings ExcludeDependencies = false as well.


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

Create a free website or blog at

%d bloggers like this: