Cloud with a Sunny Outlook

Techniques to use when Variations Attack!

Jamie McAllister -

You know those moments where it goes horribly wrong? I've just fought my way through one of those, and it might be useful for you to know how.

We are talking again about MOSS Variations. I'm going to describe how they got trashed, and how we fixed them. This enables me to highlight a couple of useful techniques along the way.

A few days ago we created four new country variations in addition to our existing ten target variations. This should have been a simple procedure, but something went wrong. We created the new Variation Labels, and clicked 'Create Hierarchies'. Halfway through creating the site for Chile, we got a vague 'Thread was being aborted' error, and the Hierarchy Creation stopped.

This left the Chilean site missing much of the sub sites and content pages. We decided to delete the Chilean Site, delete the Chilean Variation label, and create it again.

We selected delete on the Chilean Label in VariationLabels.aspx. The page didn't respond for some time, and when it finally did the Chilean label was still there. Checking the site content and structure we found all our variations were now plain old sites. Our Variations had been trashed!

Having being burned in the past we had taken a backup of the site before deleting the label. We restored to this backup rather than trying some messy repair job. But we still had the problem of a half created Variation and apparently no way to create the hierarchy again.

I settled on the idea of not deleting the Variation Label, but on persuading Sharepoint that it hadn't created the hierarchy yet (so it would do it again!). This was done with three steps;

1. Delete the half created site
2. Clean up the invalid links in the Relationships List
3. Frig the Sharepoint content database to indicate that no Hierarchy exists for Chile.


Step 1 - Delete the half created site

The easiest step of all. I went into 'Manage Content and Structure' and deleted the site. Nice to start with a step that causes no problems!

Step 2 - Clean up the invalid links in the Relationships List

If you've read my other blog posts, or those of other Variations bloggers, you'll know a little about the Relationships List. It's a 'hidden' list in MOSS that stores information about which pages and sites in the Target Variations relate to which pages and sites in the Source Variation. It's at the heart of how variations work.

Now it was full of hundreds of rows of information about a site I'd just deleted!

It is possible to edit the Relationships list via the url http://YourMOSSPublishingSite/Relationships%20List but you'd find that interface very clunky when doing hundreds of edits! But you can edit it via MS Excel 2003 and above.

On the Relationships%20List page, click Actions-> Export to Spreadsheet. This allows you to download a Microsoft Office Excel Web Query File with a '.iqy' file extension.

 The 'igy' file is an XML snippet containing a little information about the site and Guids of the list and it's view. Double clicking the file opens up MS Excel with a representation of the Relationships List.

This is what you see;

I changed the Deleted flag to TRUE on the rows I didn't want. Then I synchronised the list changes by doing Data -> List -> Synchronize List in Excel. That completed step 2.


 Step 3 - Frig the Sharepoint content database to indicate that no Hierarchy exists for Chile

I've never blogged about frigging the Sharepoint database before, simply because you aren't supposed to do it. Microsoft state that changing the database in this way could leave the product unsupported. I don't recommend you do it yourself. Alas I was in a bit of a corner here, so this is what I did;

Looking in the markup for the Variation Labels page, I knew that there was likely to be an actual field called 'Is Hierarchy Created' somewhere in the database.

First I needed to find out where the 'Hierarchy Is Created' flag might be. I know from experience that much of the data you might be interested in resides in the AllUserData table in the Sharepoint Content DB. There are a heap of columns in that DB table sporting names based on their data types - like NVarchar1, NVarchar2 etc.

These generic names indicate that data for many purposes could be held in that column. (If you had a column called UserName you'd be pretty sure a UserName was stored in there always. This naming indicates that different data items of that data type can be stored in this column across different rows).

So how do you find out what the data in this particular row represents? Well, there's a column called tp_Fields in the AllLists table. tp_Fields contains an XML fragment that maps Sharepoint columns onto the columns in AllUserData, (along with some other meta data).

So, I did a query like this;

Select * from AllLists Where tp_Fields LIKE '%Hierarchy%'

This returned one row, which turned out to be the definition for the Variation Labels list. In the tp_Fields XML I found a column called Hierarchy_x0020_Is_x0020_Created which was mapped to column 'Bit2' in my content db. I must stress that this wouldn't necessarily be the same on another installation. You need to check tp_Fields every time to be sure which column holds your data.

So, I had the location of 'Hierarchy is Created'. But which row? Well, I knew this one from prior experience. If you want to find your variation labels in AllUserData table do this;

Select * from AllUserData where tp_DirName LIKE '%Variation Labels%'

I set the Bit2 field for my failed variation to be False (Hierarchy not created yet!).

Returning to the VariationLabels.aspx screen, my failed Variation now showed as not having it's hierarchy created, but when I clicked 'Create Hierarchy' it gave me a message about all hierarchies being created already. What had I missed?

I created a new Variation Label in my sandbox, and checked out how the column values differed between that and variations where hierarchy had been created. Two other fields were different - NVarChar8 and NVarChar9 didn't have values for the new variation, but held a url to the root of the Variation in the older variations.

Cross referencing to my earlier tp_Fields investigation I discovered that the Sharepoint column in question here is  'Top_x0020_Web_x0020_URL' (it's mapped to both columns). So, I went back to my troubled environment and set NVarChar8 and NVarChar9 to NULL.

Create Hierarchy now worked. My new Chilean Variation was created without problems. Content propagates without problems, and my Relationships List entries look healthy.

Wow, what a tacky hack that all was!

So, I suggest you take note of the techniques here, as they could help you out of a sticky situation.

They were;

Taking a backup before creating new variations.

Editing the Relationships List en masse using MS Excel.

Finding out where your Sharepoint Columns map to in AllUserData by querying tp_Fields


Once again though, I'm not advising you to tamper with the Sharepoint databases in any way - it's not recommended by Microsoft.