In a follow up to my post yesterday -How to download Umbraco content properties into a crosstab table this is the follow up SQL Script that makes it even easier to download any Umbraco document type into Excel.

This SQL Script is fairly simple, basically what it does is it gets the properties associated with the specified document type and then pivots the values so you end up with a table of data that looks like this:

How to use the script

All you need to do is set the parameter "@ContentTypeId" to the document type you want (as in my previous post you can get this by checking out the link on the document type).

Once you set the id, just run the script and voila there's the data.

If you run the code and get "Command(s) completed successfully" then you've not set the id right so double check and try again.

The Script

DECLARE @cols NVARCHAR(max), @ContentTypeId int
SET @ContentTypeId = 1074

SELECT  @cols = STUFF(( 
	SELECT DISTINCT TOP 100 PERCENT
        '],[' 
        + CONVERT(varchar, Name + ' (' + CONVERT(varchar, id) + ')', 255)
    FROM
		dbo.cmsPropertyType
	WHERE
		contentTypeId = @ContentTypeId
    ORDER BY
        '],[' 
        + CONVERT(varchar, Name + ' (' + CONVERT(varchar, id) + ')', 255)
    FOR XML PATH('')
), 1, 2, '') + ']'
--SELECT  @cols

DECLARE @query NVARCHAR(max)
SET @query = N'SELECT Id, ' + @cols + '
FROM
  (
		SELECT
			CONVERT(varchar, t.Name + '' ('' + CONVERT(varchar, t.id) + '')'', 255) As [PropId],
			contentNodeId As [Id],
			ISNULL(dataNvarchar, ISNULL(CONVERT(varchar, dataDate), ISNULL(CONVERT(varchar, dataInt), dataNtext))) As [Value]
		FROM
			dbo.cmsPropertyType t LEFT JOIN dbo.cmsPropertyData d ON t.id = d.propertytypeid
		WHERE
			contentTypeId = ' + CONVERT(varchar, @ContentTypeId) + ' 
) p
PIVOT
(
	MAX(Value) 
	FOR PropId IN ( '+ @cols +' )
) AS pvt
ORDER BY Id ASC'

--PRINT(@query)
EXECUTE(@query)

Have you ever needed to get a download of your Umbraco content nodes in a cross-tab query e.g. download contact form data from Doc2Form? Using Umbraco's Contour product makes this a breeze but what about older systems? Thankfully, it's not actually that difficult.

We have a contact us form on one of our sites which uses an old version of Doc2Form which emails the customer details of the enquiry. One benefit is it also saves it to the recycle bin as a document with the name: "RE: SYSTEM DATA: umbraco master root". With that in mind, we can use SQL Server's PIVOT functionality to pull the data out in a nicely formatted manner.

Firstly you'll need to know the id's of the document type's properties, there are numerous ways to do this:

1. Just look at the cmsPropertyData table for a couple of content nodes (I could spot the ones I was after fairly easily)

2. Query the cmsPropertyType table:

Find the contentTypeId of the document type -you can do this by hovering your mouse over the document type in the tree and checking out the status bar (you can see the contentTypeId in brackets in the bottom left -mine here is 1074):

Once you have the contentTypeId of the document type, you can then get the ids of all the properties you're after by replacing "xxx" with your property id in the following script:

SELECT * FROM dbo.cmsPropertyType WHERE contentTypeId = xxx

3. Get it from the source of the document type editor

An alternative way is to examine the HTML of the Document Type editor. If you view the source on the "Generic Properties" tab and scroll to the section you're interested in (there'll be a h2 with the same name) you will find a ul that has the class of "genericPropertyList".

Each li of that ul will have the relevant id as part of it's id e.g. for a section called "Enquiry Form" the id will be: "EnquiryFormContents_49" where "49" is the id we're interested in. You can see mine (49, 50, 51 and 52 below):

Once you have these ids to hand (mine were 49, 50, 51 and 52) you just need to update the code below and run against your Umbraco database:

SELECT
	contentNodeId As [Id], 
	[49] As [Name], 
	[50] As [Telephone], 
	[51] As [Email Address], 
	[52] As [Notes]
FROM
(
	SELECT contentNodeId, propertytypeid, ISNULL(dataNvarchar, dataNtext) As [Value]
	FROM dbo.cmsPropertyData
) As src
PIVOT (
	MAX(Value) 
	FOR propertytypeid in ([49], [50], [51], [52])
) aS pvt
WHERE [50] IS NOT NULL OR [51] IS NOT NULL
ORDER BY contentNodeId

That will then produce some lovely formatted data for you, my example above produced:

It's also possible to automate this entire script so you don't need to find out the property ids, I'll post that separately.

Probably one of the most common features of an ecommerce systems is to "retrieve my details" when logging in -after all that's why you create an account with the seller isn't it?

Out of the box, uCommerce has XSLT to retrieve the customer's last x addresses but one thing it didn't do was automatically re-assign the customer's details when logging in using the built in Umbraco membership code so we need to work around it ourselves -don't worry, it's not too hard (all the code is below for you).

Background

All customer addresses are stored in the uCommerce_Address table automatically, there should be one unique address per customer however if you're on an earlier release you may find you have several copies of the same address for each customer -this is a bug that's been sorted in v1.0.5.0 so upgrade if you can.

Now you'd be forgiven for thinking that you can just select the address from the uCommerce_Address table and then assign the id to the BillingAddressId property of your purchase order however if you do that, you'll find you get the error:

The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_uCommerce_PurchaseOrder_uCommerce_OrderAddress". 
The conflict occurred in database "CommsReadyCMS", table "dbo.uCommerce_OrderAddress", column 'OrderAddressId'.
The statement has been terminated.

You'll get this because there is also a second table involved -uCommerce_OrderAddress. uCommerce_OrderAddress stores the actual address used throughout the order process incase the customer changes an address in the future, the order will always have the correct address.

The Solution

Working around this isn't actually too difficult as mentioned before. The easiest solution is to create a new User Control in Visual Studio (I'll call mine login.ascx) and hook into the LoggedIn event. Once logged in, get the Umbraco member and from that, get the customer's billing address.

There's one caveat that I found with uCommerce and that's the way it gets the address. At the moment, there is a function on customer "GetAddress", this is great however if you check out the code it calls, it actually gets the customer's first address from the database -rather than the last address used. I don't think this is a bug as in most cases the first address you enter is your main address. I'll blog separately about managing a default address within the members section.

The code below however retrieves the most recently added address from the database

Login.ascx

<asp:literal runat="server" ID="litLoggedIn" />
<asp:literal runat="server" ID="litLoggedOut" />
<asp:Login runat="server" id="lgnForm" CssClass="checkout-details" 
	DisplayRememberMe="false" TitleText="" OnLoggedIn="lgnForm_LoggedIn"
	UserNameLabelText="Email Address" />

Login.ascx.cs

protected void lgnForm_LoggedIn(object sender, EventArgs e)
{
    //If the user has a basket, wire up the shipping address with their last order details
    var basket = SiteContext.Current.OrderContext.GetBasket(true);
    if (basket != null)
    {
        //Get the customers current order
        var po = basket.PurchaseOrder;
        //Look for a shipping address
        var add = po.GetBillingAddress();
        //We only need to assign the address if there isn't already one assigned to this order
        if (add == null)
        {
            //Get the customer who's just logged in
            var mem = Membership.GetUser(lgnForm.UserName);
            //To be safe check that we have a member
            if (mem != null)
            {
                //Find the customer
                var customer = Customer.ForUmbracoMember(Convert.ToInt32(mem.ProviderUserKey));
                if (customer != null)
                {
                    //Get the customer's most recent address
                    var previousAddress = customer.Addresses.ToList().LastOrDefault(a => a.AddressName == "Billing");
                    //If you want to get the customer's first address just uncomment this line
                    //var previousAddress = customer.GetAddress("Billing");

                    //Populate the billing address with the address)
                    if (previousAddress != null)
                    {
                        OrderAddress address = new OrderAddress
                                {
                                    FirstName = previousAddress.FirstName,
                                    LastName = previousAddress.LastName,
                                    EmailAddress = previousAddress.EmailAddress,
                                    PhoneNumber = previousAddress.PhoneNumber,
                                    MobilePhoneNumber = previousAddress.MobilePhoneNumber,
                                    CompanyName = previousAddress.CompanyName,
                                    Line1 = previousAddress.Line1,
                                    Line2 = previousAddress.Line2,
                                    PostalCode = previousAddress.PostalCode,
                                    City = previousAddress.City,
                                    State = previousAddress.State,
                                    Attention = previousAddress.Attention,
                                    CountryId = previousAddress.CountryId,
                                    AddressName = "Billing",
                                    OrderId = new int?(po.OrderId)
                                };
                        //Store the address in the database
                        address.Save();
                        //Assign the address to the purchase order
                        po.BillingAddressId = new int?(address.OrderAddressId);
                        //Save the purchase order (shopping cart)
                        po.Save();
                    }
                }
            }
        }
    }
}

After my last UCommerce post on how to delete test orders and baskets from UCommerce, Søren suggested I extended the delete all baskets code to take into account when it was created. As my last code was relating to deleting test orders/baskets (and so would want to get rid of them all), I decided to post this one separately.

Delete all baskets older than x days

To use this, all you need to do is change the @addedBefore parameter to whatever date/time you want (or just adjust the –7 which represents seven days in the past.

--Delete all carts purchaseorders and associated data within x days
DECLARE @addedBefore smalldatetime
--By default the script deletes everything older than 7 days
SET @addedBefore = DATEADD(dd, -7, GETDATE())

BEGIN TRAN

UPDATE uCommerce_PurchaseOrder SET BillingAddressId = NULL WHERE OrderNumber IS NULL AND CreatedDate <= @addedBefore
DELETE a FROM uCommerce_Shipment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_OrderAddress a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_OrderProperty a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_OrderLine a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_Payment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_OrderStatusAudit a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE FROM uCommerce_PurchaseOrder WHERE OrderNumber IS NULL AND CreatedDate <= @addedBefore

--Uncomment this
--COMMIT TRAN

--And comment out this
ROLLBACK TRAN

Although Søren has posted a helpful post on how to delete entire purchase orders from the database here, we needed something a little less “all or nothing” so put the below together.

Delete a specific order id

--Delete purchaseorders and associated data based on order id
DECLARE @OrderNumber nvarchar(50)
SET @OrderNumber = 'TEST-40'

BEGIN TRAN

UPDATE uCommerce_PurchaseOrder SET BillingAddressId = NULL WHERE OrderNumber = @OrderNumber
DELETE a FROM uCommerce_Shipment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_OrderAddress a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_OrderProperty a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_OrderLine a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_Payment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_OrderStatusAudit a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE FROM uCommerce_PurchaseOrder WHERE OrderNumber = @OrderNumber

--TODO: Expand this so it checks for other orders
--DELETE a FROM uCommerce_Address a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
--DELETE a FROM uCommerce_Customer a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber

--Uncomment this
--COMMIT TRAN

--And comment out this
ROLLBACK TRAN

Delete all baskets

--Delete all carts purchaseorders and associated data

BEGIN TRAN

UPDATE uCommerce_PurchaseOrder SET BillingAddressId = NULL WHERE OrderNumber IS NULL
DELETE a FROM uCommerce_Shipment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_OrderAddress a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_OrderProperty a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_OrderLine a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_Payment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_OrderStatusAudit a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE FROM uCommerce_PurchaseOrder WHERE OrderNumber IS NULL

--TODO: Expand this so it checks for other orders
--DELETE a FROM uCommerce_Address a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.NULL = @NULL
--DELETE a FROM uCommerce_Customer a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.NULL = @NULL


--Uncomment this
--COMMIT TRAN

--And comment out this
ROLLBACK TRAN

Update: At the request of Søren, I’ve altered the delete all baskets post so it allows you to delete all baskets older than a given date, see: Delete all UCommerce baskets older than x days

I thought seeing as uCommerce is now an actual product I would start to overview an install/configuration of uCommerce assuming no prior knowledge of uCommerce. Firstly, let me start of by saying that once you've got your head around uCommerce and some of it's complexities, you'll find it a fantastic product that makes creating a new ecommerce website as easy as setting up a standard Umbraco website. It is still missing a few features, but you can easily work around these with a bit of custom XSLT/C#.

Ok, back to setting up your first uCommerce website. I've grouped these into what I feel are logical sections but if I've missed something, please let me know.

1. Install the uCommerce Package

If you've not already done so, go to the uCommerce Download page and download the uCommerce package (at time of writing, I'm using 1.0.4.2) and then download the uCommerce Store package (currently 1.0.1.2).

Install the uCommerce package as you do any other package in Umbraco. Once installed you'll be able to install the store package.

Assuming all your permissions on your Umbraco install are correct, refresh your browser and you should have a new section "Commerce". If they're not right, you'll be told to add a few web.config settings.

2. Wire up the catalog

This is the step that I didn’t “do” when we first got started and it turns out it’s one of the most important steps as it joins the uCommerce catalog to the front end.

1. Go to your Umbraco "Content" section
2. Right click on the page you would like to be the store's "home" page (in the example store, this would be "Shop")
3. Click "Manage hostnames" (see figure below)
   
4. Enter your hostname (the domain name the site runs on) in the "Domain" box and then choose the default language for the website
   
5. Click "Add new Domain" and then "Close this window"
6. Click the "Commerce" section button (in the bottom left)
7. Click the little arrow to the left of "Product Catalog"
8. Left click the relevant catalog (if you've installed the store package this will be "uCommerce")
9. Select your new domain from the "Host name" drop down list
   
10. Click the save disk button in the top left
    

3. Setup Your Product Definitions

A “Product Definition” is uCommerce’s concept of document types, it allows you to add additional information to the product. If you’re using the uCommerce starter store, you’ll get a couple of product definitions out of the box –software and support. At the moment, you can't add additional properties through the uCommerce back end (i.e. if you wanted to add additional information such as Meta Keywords/Descriptions etc -I'll cover how we got around this in a later post) but there are a number of default the category/product properties (I've put their XML reference in brackets where relevant):

uCommerce Category Properties

• Image (@image)
• Display Name (@displayName)
• Description (@description)

The default XML looks like this:

<category parentCategoryId="" parentCategoryName="" index="0" id="67" name="Software" displayName="Software" displayOnSite="True" description="" image="" />

uCommerce Product Properties

• SKU (@sku)
• Internal name
• Display on web site (@displayOnSite)
• Allow ordering (@allowOrdering)
• Thumbnail (@thumbnailImage)
• Primary image (@primaryImage)
• Display name (@displayName)
• Short description (@shortDescription)
• Long description (@longDescription)

The default XML looks like this (the variants are not standard but are there because they're setup as part of the store package):

<product index="0" sku="100-000-001" displayName="uCommerce 1.0 RTM" shortDescription="uCommerce is a full featured e-commerce platform with content management features powered by Umbraco. Everything you need to build a killer e-commerce solution for your clients!" longDescription="uCommerce is fully integrated with the content management system Umbraco, which provides not only the frontend renderendering enabling you to create beautifully designed stores, but also the back office capabilities where you configure and cuztomize the store to your liking.&#xD;&#xA;&#xD;&#xA;uCommerce_ foundations provide the basis for an e-commerce solution. Each foundation addresses a specific need for providing a full e-commerce solution to your clients. foundations in the box include a Catalog Foundation, a Transactions Foundation, and an Analytics Foundation.&#xD;&#xA;&#xD;&#xA;Each of the foundations within uCommerce_ are fully configurable right in Umbraco. No need to switch between a multitude of tools to manage your stores. It's all available as you would expect in one convenient location." thumbnailImage="1097" primaryImage="1097" allowOrdering="True" isVariant="False" displayOnSite="True" hasVariants="True" price="3495.0000" currency="EUR">
  <variants>
    <product index="0" sku="100-000-001" displayName="Developer Edition" shortDescription="" longDescription="" thumbnailImage="0" primaryImage="0" allowOrdering="False" isVariant="True" displayOnSite="False" hasVariants="False" variantSku="001" price="0.0000" currency="EUR" Downloadable="on" License="Dev" />
    <product index="1" sku="100-000-001" displayName="30 Days Evaluation" shortDescription="" longDescription="" thumbnailImage="0" primaryImage="0" allowOrdering="False" isVariant="True" displayOnSite="False" hasVariants="False" variantSku="002" price="3495.0000" currency="EUR" Downloadable="on" License="Eval" />
    <product index="2" sku="100-000-001" displayName="Go-Live" shortDescription="" longDescription="" thumbnailImage="0" primaryImage="0" allowOrdering="False" isVariant="True" displayOnSite="False" hasVariants="False" variantSku="003" price="3495.0000" currency="EUR" Downloadable="on" License="Live" />
  </variants>
</product>

Adding additional product properties is simple.

1. Click the "Commerce" section button
2. Navigate to: Settings --> Catalog --> Product Definitions
3. Choose the product definition you would like to edit (or create a new one in the same way that you would with Umbraco document types)
4. Right click the product definition you need to add extra properties to and click "Create"
5. Type in a name for your new property i.e. Size
6. Choose the Data Type for the property (if you need something that's not listed see "Creating your own Data Type" below):
   • ShortText -A textbox
   • LongText -A text area
   • Number -Beleive it or not, a numeric value
   • Boolean -A checkbox
   • Image -A media selector
7. Click the "Create" button
8. You can now choose a few additional options for the new property including how it should be shown to the user and whether it's Multilingual.
   • Name -the text used as the label in the uCommerce product editor (it's also the name of the attribute on the XML that will contain it's value)
   • Data Type -the type of control to render in the uCommerce product editor
   • Multilingual -whether the control should be shown on the "Common" tab of the uCommerce product editor or the language specific tab
   • Display On Web Site -A flag that's sent out in the XML so you can decide whether or not to show it on the website
   • Variant Property -Whether this should appear as a table column heading under the "Variants" tab (I'll go into variants more in a later post)
     Note: Do not set Multilingual and Variant property to both true as at the moment, it won't be shown in the uCommerce product editor -you've been warned!
   • Render in Editor -Whether the control should be shown in the uCommerce product editor screen or hidden from the administrator (i.e. for data you want to use internally only and should be editable)
9. Finally you'll need to enter in a Display Name for the various languages. This is what's shown to the user if you dynamically pull through the various properties on the product details page.

4. Creating Your Own Data Type

Now, you may be thinking that using that set of data types is a little limiting for something like "Size" or "Colour" and you might want to display something a little more flexible to the user -such as a drop down list. This is easy enough:

1. Right click the "Data Types" node
2. Enter a name i.e. "Size"
3. Choose the definition for the Data Type (for size we will use "Enum")
4. Save and Refresh the "Data Types" node
5. Right click your new Data Type and click Create
6. Enter your Option's value i.e. "Small"
7. Repeat 5-6 until all your options are set i.e. add "Medium" and "Large"

Note: At the moment, the enum values cannot be re-ordered through the UI so make sure you add them in the order you want them in the editor!

5. Load Your Catalog

Once you've finished creating your various product types, it's time to create your catalog. Creating categories and products within uCommerce is as simple as creating pages in Umbraco. Using the same right click menu concept you can create nested categories as deep as your catalog requires. You can add products and categories at any level by choosing either the "Category" or "Product" radio button and choosing your product type.

6. You're Done!

Assuming you've followed the steps above, you should now have a (fairly basic) store setup. Go to your site's homepage and click the "uCommerce" menu item and voila, your categories and products should be listed.

Not getting the categories you were expecting? Perform the helpful xsl “copy-of” trick within either the "RootCategories[XSLT].xslt" file or "Category[XSLT].xslt" file:

<pre><xsl:copy-of select="$categories" /></pre>

and then have a look at the output:

<errors><error>No product catalog group found supporting the current URL.</error></errors>

If you're getting the above error, currently (and this may be a misunderstanding/changed later) you have to have the catalog and catalogue group names the same –in the example site, they’re both “uCommerce”.

As I think the concept store offered with Software/Support isn't particularly real-world, I'm going to work on creating a basic store that you can use to better understand uCommerce and it's intricacies.

Check back soon as I'll be posting an overview of the checkout process, the various XSLT files and integrating payment gateways into uCommerce (initially SagePay, PayPoint, WorldPay and PayPal).

If you're not configuring Umbraco through a web installer, you've had your installs in place for years and never checked the permissions or whoever set the permissions up was lazy and gave IIS write access to the entire folder, there will come a time when you want to restrict modify access to just those user(s) who should have access.

You can find a (pretty) complete list of the files/folders that the Umbraco install should have access to here but assigning them across 101 different installs is a PITA . Thanks to a helpful PowerShell script to set folder permissions from PowerShell.nu you can easily automate the process.

For those of you not familiar with PowerShell (like me) complete instructions are below. For the rest, here's the command:

Get-ChildItem -path ##PATH TO YOUR INSTALL## 
| Where { $_.name -eq "Bin" -or $_.name -eq "Config" -or $_.name -eq "Css" -or $_.name -eq "Data" -or $_.name -eq "Masterpages" -or $_.name -eq "Media" -or $_.name -eq "Scripts" -or $_.name -eq "Umbraco" -or $_.name -eq "Umbraco_client" -or $_.name -eq "UserControls" -or $_.name -eq "Xslt" } 
| ForEach {./SetFolderPermission.ps1 -path $_.Fullname -Access "NETWORK SERVICE" -Permission Modify}

Instructions:

1. Save the SetFolderPermission.ps1 script to your server
2. Open your PowerShell console (I think it's installed by default if not, you can download PowerShell here)
3. Copy the above PowerShell command into notepad
4. Update "##PATH TO YOUR INSTALL##" to your Umbraco install
5. If your IIS install doesn't use NETWORK SERVICE as the default user, update it to your user
6. Make sure it's all on a single line
7. Copy/Paste/Run in PowerShell

Bonus

If you're uber lazy and just have a web folder of Umbraco installs you can set the path to the folder of Umbraco installs and use:

Get-ChildItem -path ##PATH TO YOUR FOLDER## -recurse
| Where { $_.name -eq "Bin" -or $_.name -eq "Config" -or $_.name -eq "Css" -or $_.name -eq "Data" -or $_.name -eq "Masterpages" -or $_.name -eq "Media" -or $_.name -eq "Scripts" -or $_.name -eq "Umbraco" -or $_.name -eq "Umbraco_client" -or $_.name -eq "UserControls" -or $_.name -eq "Xslt" } 
| ForEach {./SetFolderPermission.ps1 -path $_.Fullname -Access "NETWORK SERVICE" -Permission Modify}

I've not tried this mind you and can't recommend it but hey, it's there if you want it ;)

A while ago I posted a quick one question questionnaire to gauge how much people were charging for the "average" Umbraco install. This was partially to settle an internal debate but also for an post I've not had time to post yet so in the meantime I'll post the results.

Having a quick look at the results you'll see that 64% of the people that answered (around 100) charge £2,500 or less for a "standard" install with a whopping 96% of people charging less than £10,000. I'll explain how this can be used for pricing your Umbraco packages soon!

It's taken some time to get here and there's still more to add as I think this is a pretty big topic but I thought I'd get started. I wanted to keep the session more focused on the selling points of Umbraco and how people pitch Umbraco to the clients than selling techniques which on the whole we managed to do.

The first thing I stressed was that I wasn't going to teach you how to sell or selling techniques as I've never found that hard selling works -though I'm not saying it doesn't, I just prefer to educate the client into the most suitable solution (even if that isn't us).

There were a number of questions that were raised and I'll answer what I can here, if you were at the session and I've missed something, please let me know and I'll get it added:

1. What are the key selling points of Umbraco
2. How do you pitch Umbraco
3. Do you tell clients it's open source (or use that as a sales point)?
4. How do you price Umbraco
5. Once you've won, what do you ask your client
6. How do you support Umbraco
7. How do you get around the question of "What happens if you get hit by a bus?"

What are the key selling points of Umbraco

A couple of the attendees came up with better 30second sales pitches so I'm sure they'll post those up shortly but here's a few I remember:

• It's easy to use -you don't need any previous computer experience
• You can edit any page's content yourself at any time
• It's highly flexible and lightweight
• It's search engine friendly
• It's open source (this really can be a selling point at the right time)

Do you tell clients it's open source (or use that as a sales point)?

We do and we don't. Again it really comes down to who you're pitching Umbraco to. Where the client has had issues with developers not releasing source etc then it's clearly a selling point.

Generally we do tend to explain to clients that we will base their website on an open source project that we then build on and customise further to suit their needs and that by using best practice methodologies, any developer can in theory pick up the system and continue to develop it (even if they have no experience of Umbraco).

How do you price Umbraco

This question was asked in a couple of different ways throughout the session and it's a topic in itself (see the article I wrote a while ago about pricing your work).

If you look at Umbraco in the right way you'll see that it's actually rather easy to price as there are a few components that you can sell either individually or together:

• Installation and configuration
• Customisation
• Hosting
• Support

All you need to do is work out a minimum cost for each component and then that will give you a core system cost.

Once you have your core Umbraco costs (don't forget to factor in your license costs) you can then alter the costs accordingly for your client -and this has to be on a case-by-case basis. 

How do you pitch Umbraco

This is easy, there are so many selling points to Umbraco that regardless of what the client is looking for, as long as it's CMS based, Umbraco will have some benefit you can overview to the client.

When pitching Umbraco, we have found educating the user as to the benefits and what the client should be looking for in other systems. If you do this, then the majority of the time, the rest of the competition falls by the wayside.

If the client is a large corporate it's always worth mentioning that it offers much of the functionality that SharePoint does but with little of the cost (or setup pain!).

Once you've won the contract, what do you ask your client

The first thing to do is to get all the information you need to complete your contract (or at least tell your client what you'll need and when). You should know what you'll need already but we tend to ask for:

• Design inspiration (websites the client does and doesn't like -and why)
• Logos and other source imagery
• Text for the website (you'd be best to load the initial content during training but get the client to think about it while you're developing or you'll never get there!)

Next, you'll need to make sure your paperwork is in order. Once you have agreed the general premise of your contract, it's important that you confirm all deliverables (what you'll be doing for the client) in a work order with the client. This avoids an ambiguity on what you'll be delivering and when. This doesn't need to be pages of text (though sometimes it needs to be) but avoids disagreements later.

You should always request signed work order and deposit (we request a minimum of 20% regardless of project spend) at a minimum before starting any work.

Once you have the signed work order (you sign one for the client to keep and keep one yourself), you can start thinking about the project. If it'll take longer than a week to deliver, I recommend you provide the client with rough timescales, this will have the added benefit of helping you focus your mind.

How do you support Umbraco

This is something that Paul Sterling addressed through another session and if he doesn't write up his notes I'll make a few notes in another post.

How do you get around the question of "What happens if you get hit by a bus?"

Although this was asked a couple of times throughout the session, I avoided answering it a little due to a conflict of interest. For the past few months we've been working hard on a new system called Crisis Cover which has been designed to help you with this exact question.

Crisis Cover monitors you to ensure that you're still around and if you don't respond to a number of alerts, it will contact your clients informing there's something wrong.

I'll post more information about Crisis Cover, but if you're interested in getting involved with the beta, leave me your email and I'll get one sent out.

In Closing

There is a lot of information about selling and business in general in my previous post "Business start-up advice" which if you're starting out, I really recommend you reading as it should give you a really good start (and includes example Service Level Agreements, Contracts and other useful documents).

Those of you lucky enough to go to CodeGarden '09 you'll know the format of the Open Space already but for those of you who didn't, Open Space is the time that the attendees are invited to talk about something they're interested in so I proposed two:

1. Space 1: Selling Umbraco
2. Space 2: Exception handing and error reporting in Umbraco (and other .net websites/applications)

I'll write up the Selling Umbraco talk shortly but I wanted to put a few resources together for it first so decided to write this one up first.

First of all we had a brief chat about how everyone handles errors in their applications and the various error handling options available. We discussed three options:

1. Error Handler v2.0
2. ELMAH
3. Exceptioneer

I've only had a brief look at ELMAH and found at the time it was a little too much in the way of RSS feeds etc and I just want an email alert, that said, Lee Kelleher has written a good article about integrating ELMAH with Umbraco here and I've written another article about integrating Error Handler v2.0 into Umbraco here so I'll overview how to integrate Exceptioneer into Umbraco here instead.

Wiring up Exceptioneer with your site couldn't be easier, the best bit is that they do all the hard work for you with their "Integrate" section of the site but to give you a quick snapshot of how easy it is, first of all, download the dll and pop it into your bin folder. Then edit your web.config:

          <?xml
version="1.0"?> <configuration> <configSections> <section name="Exceptioneer"
type="Exceptioneer.WebClient.ClientModuleConfiguration, Exceptioneer.WebClient" requirePermission="true"
/> </configSections> <!-- This is where you get to specify your API Key
and Application Name --> <Exceptioneer ApiKey="XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"
ApplicationName="YOUR APPLICATION NAME" /> <!-- If you're using IIS 6.0 or Visual
Studio's built in web server you'll need to add this bit --> <system.web>
<httpModules> <add name="Exceptioneer" type="Exceptioneer.WebClient.ClientModule,
Exceptioneer.WebClient" /> </httpModules> <!-- If you want to use the
JavaScript handling then add the Http Handler as so --> <httpHandlers> <add
path="ExceptioneerJavaScript.axd" verb="GET,POST" type="Exceptioneer.WebClient.JavaScriptHandler,
Exceptioneer.WebClient" /> </httpHandlers> </system.web> <!-- If
you're using IIS 7.0 you'll need to add this bit too --> <system.webServer>
<validation validateIntegratedModeConfiguration="false"/> <modules> <add
name="Exceptioneer" preCondition="managedHandler" type="Exceptioneer.WebClient.ClientModule,
Exceptioneer.WebClient" /> </modules> <handlers> <add name="ExceptioneerJavaScript"
path="ExceptioneerJavaScript.axd" verb="GET,POST" type="Exceptioneer.WebClient.JavaScriptHandler,
Exceptioneer.WebClient" /> </handlers> </system.webServer> </configuration>
        

Now, one of the coolest things about Exceptioneer is that you can now also debug JavaScript errors! To debug the javascript errors, just include this script in your templates:

<script src="/ExceptioneerJavaScript.axd?Reporter=true" type="text/javascript"></script>

That's it, you're done. Easy eh? If you want to know more about what it can do, Phil's put together this "lovely" video overview. Exceptioneer have done a great comparison of the main features of comparison Exceptioneer and ELMAH here, the downside though is Exceptioneer is still in beta.

Remember, regardless of how good you think your code is, you should always integrate some form of error handling in your website even if it is just an email to alert you to the fact.

The Error

For those of you who have tried to rename your Umbraco installation directory to something other than the default /umbraco/ you'll have found that TreeInit.aspx throws a JavaScript error along the lines of:

Message: Object expected
Line: 1
Char: 4236
Code: 0
URI: http://www.yourdomain.co.uk/youradmindirector/js/xloadtree.js

As this only really affects the refresh of the tree/close of a couple of dialogues I've not bothered fixing it but basically the issue is outlined well here: http://tinyurl.com/cx9atv

The Fix

If you're using extension less URLs already then it's easy as pie to sort:

1. Open your UrlRewriting config file (/config/UrlRewriting.config)
2. Add this above "</rewrites>":

<...>

< add name = "missingjs" 
    virtualUrl="^~/## YOUR ADMIN DIRECTORY GOES HERE ##_client/ui/(.*).js" 
    rewriteUrlParameter="ExcludeFromClientQueryString" 
    destinationUrl="~/umbraco_client/ui/$1.js" 
    ignoreCase="true" />

If you've not already using extension less URLs don't panic, that's easy to setup you can read all about it here. Alternatively you could just copy the js files from one folder to another ;)

The Why

I don't know how many people already rename their admin dir from something else but as Umbraco becomes a more popular choice of CMS you really should consider hiding the folder (the more popular it becomes, the more people will become more familiar with the default admin directory of /umbraco/).

Although there hasn't yet been a breach ( AFAIAA ) if a vulnerability is found, the first step in prevention is obfuscation -hide your admin directory! A quick Google search will show you how easy some developers have made it for you to find their admin sites.

If you've been following my blog you'll know that I've been raving about error reporting within ASP.Net (you can see my ASP.Net Error Reporting category for a couple of them if you like) but until now it's been limited to those sites that you have access to the global.asax file.

One of the irritations I've found with Umbraco and dasBlog is that I don't get notified of errors as they're just logged to a text file/database somewhere. This is fine if you run 2 or 3 sites but we manage too many to check them all everyday. Instead we rely on email error notifications which until today have been a PITA to integrate into Umbraco.

Today I'd like to introduce to you Error Handling v2.0 which instead of relying on the global.asax file for the error hooks, uses a HttpModule which means you can install it into any existing/pre-built application such as Umbraco and dasBlog.

Adding it into the site is simple, you'll need to install the module into the web.config file and add the configuration section a sample (cut down) web.config is below:

< ?xml version = "1.0" ? > 
<configuration> 
    <configSections> 
        <section name="tsdErrorsConfigSection" allowExeDefinition="MachineToApplication" restartOnExternalChanges="true" type="System.Configuration.NameValueFileSectionHandler, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> 
    </configSections> 
 
    <tsdErrorsConfigSection file="ErrorHandling.config"/> 
 
    <system.net> 
        <mailSettings> 
            <smtp from="you@yourdomain.com"> 
                <network host="127.0.0.1" port="25" /> 
            </smtp> 
        </mailSettings> 
    </system.net> 
 
    <system.web> 
        <httpModules> 
            <add name="ErrorModule" type="TheSiteDoctor.ErrorHandling.ErrorModule, TheSiteDoctor.ErrorHandling" /> 
        </httpModules> 
    </system.web> 

<!--...-->

<!--  
IIS 7 Settings 
    <system.webServer> 
        <validation validateIntegratedModeConfiguration="false" /> 
        <modules> 
            <add name="ErrorModule" type="TheSiteDoctor.ErrorHandling.ErrorModule, TheSiteDoctor.ErrorHandling" /> 
        </modules> 
    </system.webServer> 
-->

</configuration>

Then you'll need to check all the settings -I recommend storing these in another .config file for clarities sake. Make sure you've configured your SMTP settings and you should be good to go.

If you want to test your settings, I've included a test page for you that will check your settings and show you the defaults if you've not set them. I've got this running now on a couple of Umbraco and dasBlog installs without an issue.

There's also a useful logging system in it which I'll look to overview in a later post but if you want to see it, check out the included aspx file.

Download ErrorHandling_v2.0.zip (25Kb)

If you do use this code I'd be interested to hear how you get on, I think it requires a little more refinement un some areas but it's pretty robust.

Enjoy.

I thought I'd share this as it's something I've been thinking about trying for a while. Umbraco is great but sometimes you want the default document selected when creating a page to be one that isn't the alphabetically first one.

To work around this I tend to prefix the important Umbraco document types with a symbol (or you could use 1. etc I guess) but if instead you use a space (" ") before the name of your document type, Umbraco will place it at the top of the list for you.

The nice thing to note here is that they obviously trim the name first so it just appears as "Text Page" rather than " Text Page".

I found this out on our latest site which is just about to go live: www.nhshistopathology.net -check it out and let me know what you think.

Enjoy!

When uploading some new media items for a client today we noticed that if you selected "Remove" before saving, it doesn't actually remove the file from the FileSystem. Having a quick look around the forums I saw there are a few posts already pointing this out so I thought I'd fix it.

This is a little application that simply checks the media items in the database and then compares it against a folder you select on your machine. If the file is in use according to the database then it's ignored otherwise it will remove it.

To use:

1. Enter your server's login details
2. Click "Test Connection"
3. Select the relevant database from the drop down
4. Check the "Media Folder Name" matches your Umbraco's installation
5. Locate your Media Folder on your computer
6. Click "Check Media Folder" -this will then list all the orphan files
7. If it looks right, click "Delete" -with caution
8. Job done

There are a few checks in place to avoid mishap but it's not 100% foolproof as I needed something rough and ready to sort a couple of installations out. If this is something that's seen as useful I'll extend it a touch, some ideas I've got already:

• Check that the selected media folder matches that of the database
• Check that the media id's are the same (to avoid wiping another installation)
• Save config settings for easy re-use
• Use webservices rather than a direct connection to the database
• Enable FTP useage

Please note: I accept no responsibility if anything was to go horribly wrong with this. I would backup your folder first just in case!

You can download the MediaFolderCleaner application here

As requested on the forum, we've got a map to try and work out where it should be placed, if you want to come along get yourself added: http://tinyurl.com/3oaf8x

Instructions from Google:

Adding and Editing Placemarks

To add a placemark to your map:

1. Create or open a map.
2. Click . Your cursor changes into a placemark icon with an "X" crosshairs. The crosshairs indicate where the placemark will fall.
   
3. Move the cursor to the appropriate location. If you want to dismiss this placemark, press the Escape key.
4. Click your mouse button to place your placemark. It should bounce into place.
5. Add a title and description.
6. You can also change the icon for your placemark by clicking the icon in the top right corner of the info window. You can also add your own icon.
7. Click OK to save your placemark.

To move or edit a placemark:

1. Click Edit in the left panel.
2. Drag and drop the appropriate placemark to the new location. Note that you can only edit or move placemarks on your maps, not others.
3. To edit a placemark's title or description, click on it to open the info window. Edit the title and description and click OK.
4. Click Done in the left panel when you are finished.

In a previous post about CodeGarden 08, I asked people to get in touch if they'd be interested in a UK Umbraco meet up. I've had a fair few people get in touch so I think it's something worthwhile pursuing further. The nest stage from my POV is working out the location and potential content of the meet so I thought I'd open it up to the floor.

With the forthcoming DDD7 , I thought it might be a ready-built platform that we could use but I agree with Phil that DDD7 may not be a suitable platform for a multitude of reasons.

As I've had people from the South West and Scotland voice an interest, I don't think it'll suit the majority of people to have it based in London so suggest it is based in the Midlands -probably Birmingham as it's easy to get to (M6 from the North, M4 from London, M5 from the South -or train!) and there are plenty of places to have the meet.

In regards the format/content of the meet, does anyone have any suggestions? We could follow Niels' and Per's open format or we can have a more structured theme? I've not had too much of a think as to subject matter but some I have come up with so far:

• An introduction to Umbraco and what it is (many of the people I've spoken to have only just started using Umbraco)
• Examples of Umbraco how Umbraco can be used
• More advanced Umbraco functionality (membership etc)
• Getting to grips with XSLT
• How to sell Umbraco to your clients

So that's where I've got to so far, does anyone have anything to add?

BTW the logo is just a working logo atm, need to have Niels approve it ;)

Update: I have posted a post on the Umbraco forums about a UK Umbraco meet here

So things have been manic here the past week, for those of you who didn't know, I popped over to Denmark at the last minute to attend Umbraco's CodeGarden 08. It was great fun and I have to thank Niels Hartvig and Per Ploug Hansen for putting on a great couple of days.

You can check out my photos from the event on Flickr (bear with me, I'm just getting started with Flickr).

I'm sure a fair few people have blogged about the highlights (if you're interested check www.umbraco.org) but the biggy was announcing the release of Umbraco v3.14.0 which is pretty exciting news as it has a ton of feature enhancements and UI improvements. Also, you'll be pleased to hear that they're making 2008 the year of Umbraco documentation!

Another interesting points from the conference was the pending release of Umbraco.TV which will feature tutorial videos and insights from the core team on how to use Umbraco and the Umbraco store which allows you to easily distribute the packages you make :) All in all some interesting developments.

There were also a fair few English developers at the conference so discussion inevitably turned to a UK meet (I know there are a fair few designers and developers here that couldn't justify the expense) so that's something that I'm going to look into setting up. If this is something you'd be interested in, leave a comment or drop me an email and we'll see how much interest there is.

To all the rest of you -it was great to meet you, you're all a lovely bunch and I look forward to meeting you again at CodeGarden 09!

The other thing I've finally clarified (this is for you Simon!) is the Umbraco licensing rules so if you're unsure on those, check out my post on when you need to purchase an Umbraco license (the answer is always -or never, it's up to you!).

This may seem a slightly obvious/silly post but the answer is simple -it's just not *that* well documented/explained.

In a nutshell there are three scenarios you need to worry about:

• Using Umbraco in a non-commercial environment with the branding (logos etc) intact -no fee
• Using Umbraco in a commercial environment with the branding (logos etc) intact -no fee
• Using Umbraco in a commercial environment without the branding (logos etc) -fee

So there you have it. But to be fair, you should always pay for it if you're using it in a commercial environment just because it's a great product (and it's good for your karma!)

I've seen a couple of posts recently about how to apply headings to text within Umbraco and the main suggestion is that you apply them by hacking about with the StyleSheets that just doesn't sit right with me.

We use a different method -that is to enable the format drop down list. To me it just seems cleaner but I get the feeling I'm about to start a big debate. The method we use was originally discussed here but in short download this zip file with the updated DLLs in and instructions.

6291-leftinymce.zip (241KB)

Following on from a recent post of mine about how to setup changeable headers using the media picker in Umbraco a new site I have been working on required something a little extra -they wanted the headers to simply be chosen at random. from a given media folder.

First, create  a new (blank) XSLT file and add the following:

Random header images XSLT

< ?xml version = "1.0" encoding = "UTF-8" ? >
<! DOCTYPE xsl:Stylesheet [ <!ENTITY nbsp " "> ]>

<...>

<xsl:stylesheet 
    version="1.0" 
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
    xmlns:msxml="urn:schemas-microsoft-com:xslt"
    xmlns:umbraco.library="urn:umbraco.library"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt"
    xmlns:math="urn:schemas-hizi-nl:math"
    xmlns:Exslt.ExsltStrings="urn:Exslt.ExsltStrings"
    xmlns:Exslt.ExsltMath="urn:Exslt.ExsltMath"
    exclude-result-prefixes="msxml Exslt.ExsltMath Exslt.ExsltStrings math umbraco.library">

<xsl:output method="xml" omit-xml-declaration="yes"/>

<xsl:param name="currentPage"/>

<msxml:script language="JavaScript" implements-prefix="math">
function random(numDie,numMax,numMin){
if (numMin==null){numMin=1;}
var sum=0;
for (var index=0;index&lt;numDie;index++){ 
sum+=Math.floor(Math.random()*(numMax-numMin) + numMin);
}
return "" + sum;
}
function floorme(numFloor){
return "" + Math.floor(numFloor);
}
</msxml:script>

<xsl:variable name="StartNode" select="/macro/StartNode/node/@id" />
<xsl:variable name="parent" select="umbraco.library:GetMedia($StartNode, 'false')" /> 
<xsl:variable name="random" select="math:random(1, count($parent/node)+1, 1)"/>

<xsl:template match="/">

    <xsl:for-each select="$parent/node">
        <xsl:if test="position()=$random">
            <xsl:if test="./data [@alias = 'umbracoExtension'] = 'gif' or ./data [@alias = 'umbracoExtension'] = 'jpg' or ./data [@alias = 'umbracoExtension'] = 'jpeg' or ./data [@alias = 'umbracoExtension'] = 'png'">
                <style type="text/css">
                #header{
                    background-image: url(<xsl:value-of select="./data [@alias = 'umbracoFile']"/>);
                }
                </style>
            </xsl:if>
        </xsl:if>
    </xsl:for-each>
</xsl:template>

</xsl:stylesheet>

What this does is it uses the StartNode (a media folder) passed in from the macro to loop through any valid files (in this case jpg/gif/png and pull out the image if it's valid. I was thinking about replacing the for-each loop and simply using the index but I'm not sure if there would be any performance improvement except for if there were a lot of header images in the folder.

You'll then need to create a new macro and add a parameter with the name "StartNode" and select "mediaCurrent" as the Type. That's it :)

Having only recently started to use Umbraco I've taken a couple of days to familiarise myself with the way it works and try and get a few best practices in place, I expect these will be updated over time but you've got to start somewhere ;)

As with any code, I think it's very important to follow a consistent naming convention -whether it's the same one everyone else follows or not, you need to be able to pickup code you wrote months/years/decades ago and still understand it. Your styles will no doubt change over the years but you get the idea.

I've chosen to follow the following "style":

• Document Types: Lowercase the first letter of the aliases followed by capitals for the new words (similar to Hungarian Notation). Use descriptive names i.e. Home Page for the document type as it'll be client facing. Suffix with "Page" if it is a page document type (as opposed to i.e. a screen shot)
• Templates: If the template is specifically for a document type, use the same name for the template, if it relates to multiple document types name it logically i.e. "Master Template" or "Left Menu"
• Macros: Prefix the macro alias with uppercase TSD to avoid conflicts with other macros. Prefix the name with [Source of the macro] i.e. [XSLT] or [User Control]. This is something I picked up from the sample package created by Warren Buckley that I think makes it easier to understand what's going on
• XSLT Files: Prefix the name with the site's abbreviation i.e. for www.thesitedoctor.co.uk it would be TSD or for www.wineandhampergifts.co.uk WAHG if it's a site specific XSLT file otherwise name conventionally i.e. CamelCase

I'm currently investigating a new Open Source ASP.Net CMS system called Umbraco. It looks very promising as it can be fully accessible and has full support for XHTML among many other interesting features. I met with a couple of the guys that are developing the system in Manchester a couple of weeks ago and they've got big plans so keep an eye on it.

Anyway yesterday while setting up the new The Site Doctor site on Umbraco I ran into an issue where by I had "broken" the menu. Basically I had deleted the template from the system which should have deleted all copies of the pages that use that template but instead a cache remained somewhere. I ran through a number of steps to re-create the cache but nothing worked.

If you run into the same issue as I did, follow these steps:

1. Find out the ID of the page in question (easiest way is just to add the ID to the output of the menu XSLT)
2. Search the database for the ID using the T-SQL I posted in "How to search every table and field in a SQL Server Database"
3. Download and install a Unicode and UTF-8 search program such as Text Workbench and search for the ID (I only found it in /data/Umbraco.config)
4. Reset the applications cache -the easiest way to do this is to simply re-upload the web.config file

That should sort it :)