If you’re toying around with SharePoint and want to move a site to another location, you might run into what I’ve run into: broken Lookup field links.

SharePoint Server 2003 and Windows SharePoint Services 3.0 feature 20 server templates you can integrate within your SharePoint infrastructure with the command line utility stsadm.exe.

This command line utility also allows you to move sites. Windows SharePoint Services (WSS) does not have any features to really move sites, and so the advised way to move sites within and over WSS web farms is to use stsadm.exe export and import operations to export a given site, and import it on another location.

For instance, exporting a site from your WSS web farm goes somewhat like this:

stsadm.exe -o export -url http://mysharepoint/site/subsite -filename subsite.bak

Importing this site back into your WSS web farm goes like this:

stsadm.exe -o import -url http://mysharepoint/subsite -filename subsite.bak

You now have moved your subsite to the root site.  Or did you?

The data is present in the new location, but you can run into two problems.

If you exported the site to another Site Farm, you risk that you forgot to load up the server templates.  Make sure you do this before continuïng.

Second, when using your site, you might notice that some lookup fields have lost their links. You can have Knowledge Base articles without keywords, or Request for Change files without status.  When trying to edit these Keywords or Status columns, you notice you cannot modify their lookup list.  This behaviour will be especially apparent when you have removed the source site. Bummer!

To fix this, you need to go deep into your content database to fix the references.

When importing the site, the import process creates new elements in your SharePoint web farm, but it does not modify the GUID‘s of the elements it creates.  So you are stuck with Lookup fields which are protected (because they are set up in a server template) and wrongfully point to non-existing lists.

So dive into your SQL Server, open Microsoft SQL Server Management Studio, go down the tree to your content database, make sure there is a recent backup (you can make a backup to disk from within SQL Server) and open the table dbo.AllLists.

In this table, you can find the name of the list you have trouble with, by checking the tp_Title field.  If you need to reconnect the keywords of the Knowledge Base template, you need to look for Knoledge Base, since it is the main list that has the lookup field pointing to the wrong keywords list. Also, try to find the list containing your missing information, such as the Keywords list which needs to be linked to the Knowledge Base list.

Once you found these two entries in the table, take note of the tp_ID field of the Keywords entry.  This GUID is the reference you need to use to change the Knowledge Base entry.  Go back to that Knowledge Base entry and go all the way to the right, checking out the column tp_Fields.

tp_Fields contains in XML all fields that are defined in the List.  This is an ntext column, meaning that there will be more text than you can easily read on one line.  A good way to get a better overview, is to copy the contents of this entry and paste it into Notepad.  Make sure you turn word-wrapping on.

Contents of the tp_Fields column for the Knowledge Base template

You see this is not really written to be read by a human, but alas.  We only need to care for one field; “Keywords“.

As you can see, there is this bit of XML that defines the field “Keywords“:

<Field Name=”KBKeywords” DisplayName=”Keywords” ID=”{F6DC7D70-CDF6-4ae4-B9E6-7A5BDD79F0C7}” Type=”LookupMulti” Mult=”TRUE” Group=”_Hidden” ShowField=”Title” List=”{c0065a1c-4391-4cc5-b49a-d300e0f7fa5b}” Sealed=”TRUE” ColName=”int1″ RowOrdinal=”0″ StaticName=”KBKeywords” SourceID=”{FB8B32C7-E58D-4CD4-AB4C-9CBBFCE2BC89}” Description=””/>

As you can see, there are two GUIDs in this field defined.  The first, ID, is a GUID to define that this field in the Knowledge Base list is unique because of that number.  The second, SourceID, is our prize winner.

Make sure you keep one copy of the original XML, in case you mess up.  Modify the other copy so that SourceID contains the new GUID as defined by the tp_ID column for the record that defines the Keywords list.  Paste this new XML into the tp_Fields column for the record that defines the Knowledge Base list.

Make sure the change is committed to the database and reload the Knowledge Base list in your site.  You should now see the missing Keywords again, and be able to add and modify keywords for new and existing Knowledge Base articles and files.

Now you need to perform this logic to all other missing Lookup fields throughout your imported sites.  Good luck!