Hagrin.com

While extremely unrecommended, sometimes you just want to fix a problem quickly and without jumping through a ton of hoops using the SharePoint SDK and writing a small C# program. I recently encountered this when I needed to delete a Folder from a whole bunch of Document Libraries across hundreds of sites. Making sure that I checked to see if the folder was there first, calling the right Web, calling the right List, getting the right Folder GUID, etc. all seemed like a lot of work for a simple SQL statement so I cheated.

Folders all stored in the AllUserData table with a tp_ContentType = 'Folder'. I was able to write a simple UPDATE statement like the following to delete the unwanted folder across the sites I wanted -

Quick and very dirty. Not recommended, kids don't try this at home.

Just a FYI to the 3 of you reading this site (Hi Mom and Dad!), I made an update to the Recreating the SharePoint Quick Launch story I posted back in February. I made some code fixes that were mainly introduced with the release of the 40 Free WSS Application Templates made by Microsoft.

Hope the bug fixes help!

When working with SharePoint v3 list data, it's important to understand the data structure used to store list items. List data is stored in the AllUserData table and the name should be your first indication that you might run into a problem if trying to pull or manipulate data stored in this table. Why? Well, "all" user data is stored in that table including deleted data and you will need to know how to filter out deleted items and that is where the tp_DeleteTransactionID column comes into focus.

The tp_DeleteTransactionID column is a varbinary field so you cannot readily read the contents of that column in SQL Server Enterprise Manager. This coulmn acts as a "deleted flag" and you will need to be able to know what items you have removed from your list, but you forgot to remove from your Recycle Bin. List items that you delete from your list, but that remain in your Recycle Bin will have the value of the tp_DeleteTransactionID column updated from the default value of 0x. Once you remove the items from your Recycle Bin, the item's corresponding rows will be deleted from the AllUserData, but until you do so those item's rows will remain in the AllUserData table.

So, if you want to filter out deleted content for the purpose of data manipulation or transformation, but you don't want to have to worry that the user has remembered to empty the Recycle Bin, add the following WHERE clause to your SQL statements -

Notice, that the value 0x is not surrounded by value defining apostrophes since the value is not a true string, numeric, date, etc. value, but a hex representation for the varbinary field.

Today, I encountered an error that I wasn't immediately sure how to fix while creating a new web part. After uploading the .DWP file to the web part gallery, I attempted to add the web part to a recently created web part page. Even though I had a Try/Catch/Finally wrapper around my code, I received a browser alert that had the following error:

After a very quick search for that error on Google's main search index and Google Groups, I found zero results. Then, I remembered that I didn't make the necessary corrections to the .DWP file - in particular, I left the Assembly field blank. After adding the correct assembly name, I saved the file, deleted the previously uploaded DWP file, re-uploaded the new DWP file and was able to successfuly add the web part. Success!

Anyone who has worked with SharePoint extensively knows that when troubleshooting SharePoint errors you will be confornted with some of the most meaningless, generic error messages of any major application around. However, thanks to articles like this (which is where I first read this tip), SharePoint developers and administrators can turn on more descriptive, "friendly" error messages by making two changes to your web applications web.config file -

1. Set customErrors=off
2. Set CallStack="true"

Making these two changes in your development environment will really help your SharePoint developers and admins troubleshoot technical issues.

UPDATE: 2007 May 2nd - I've updated this article to include a lot of bug fixes that I have encountered along the way. I will maintain some type of versioning at the end of this post so readers can follow code changes that have occurred over time.

Microsoft's SharePoint Services 2007 (v3) provides so many additional features and bug fixes to their previous version that I was extremely anxious to upgrade our company's intranet to the newer code. However, as with any upgrade, you eventually run into stumbling blocks that will make your life miserable as a programmer/designer.

I recently came across one such issue with SharePoint Services 2007 - when you create a new page through the front-end, the Quick Launch menu is lost. Huh?

The Quick Launch menu is the standard left side navigation module that holds link information. In addition to the site specific links that you add to the Quick Launch, a static "View All Site Content" link is docked at the top of this list (which allows users to see a listing of all the Lists, Document Libraries, Picture Libraries, Surveys, Discussions and more. The items displayed in the quick launch are managed through the "Site Settings" option underneath the "Site Actions" button.

However, there is one major problem with the default SharePoint implementaion - when a user creates a new page (whether a basic or web part page), the Quick Launch menu does not attach itself to that new page. For some unknown reason, a major navigational module is dropped from secondary pages created through the SharePoint front-end. I would say that this decision is dubious at best. Since our company wanted to dock our Quick Launch onto every new page created on every site we maintain, we needed to programmatically come up with a solution. I decided to solve this problem by recreating the Quick Launch as a custom web part.

How do you reconstruct the Quick Launch from scratch? First, as with most advanced custom SharePoint tasks, you need to understand how the database is structured. Many SharePoint admins will tell you to "leave the SharePoint database alone", but simple reads to the database will not cause a problem (in fact, as long as you understand the database structure, (although not supported) I have created procedures that update the SharePoint database directly by pulling information from other data sources). The Quick Launch information is stored in two tables - Webs and NavNodes. The Webs table stores information concerning the different sites that have been created. It's important to first pull the 'id' for the for the site the user is currently browsing.

How do you tell what site a user is on? One such method would be to get the URL through Page.Request.Url.AbsoluteUri, then get the array(3) value and use that value for the 'fullurl' where clause(and if your dataset = 0 rows, you are on the default site, otherwise you are on a subsite). One problem with this method is that you cannot have a Document Library that shares the same name as a subsite (however, this is the option we implemented for now until I develop something better). Next, you take that 'id' value and query the NavNodes table for the record containing the root Quick Launch information for that site. You'll grab the 'eid' value for that record and then use that value to grab all the records where that eid value is found in the 'eidparent' column (for that root site and subsite).

This will give you all the "Link Headings" for that site's Quick Launch. Read these values into an array. First, render these headings using the ms-navheader class. Before moving onto the next "Link Heading", we must check to see if there are any "children" or "Link Items" for this heading. That would be done by running the following query:

If your dataset count is > 0, then read these "Link Items" into an array and render them all out while applying the ms-navitem class. Once all the "Link Items" are printed out, move to the next "Link Heading" until you have read and rendered all headings. So, there's the logic behind recreating the Quick Launch. Here's a snippet of some of the code I used to render my Quick Launch recreation (GUIDs changed to protect the innocent):

If you see any improvements that I can make, let me know by leaving me a comment. Hope this helps someone.

Versioning

1. Version 1.1 - (2007 May 2) - Bug fixes which include:
   • Applying Quick Launch logic that is used in the SharePoint WSS 40 Free Application templates.
   • Wrapping data retreived from the database and other sources in System.Web.HttpUtility.UrlPathEncode() to prepare for URL formation and usage.
   • Colorized the comments (I'm looking for a Drupal module to automatically do this for me)
2. Version 1.0 - (2007 Feb 7) - Original Article on recreating the SharePoint v3 Quick Launch through a Web Part