Unghosting SharePoint Pages

Introduction
A very cool aspect of SharePoint’s implementation is the mechanism by which it ghosts pages, that is, stores pages altered with FrontPage in the _site database so they are available to all of the web servers in the server farm.

The Problem
However, because ghosted pages alter the way in which custom assemblies are loaded by SharePoint, it is undesirable to have a page ghosted for no reason, such as adding a DataView web part in FrontPage (something that does not require a ghosted page). Also, ghosted pages will not be affected by template charges so they can make it difficult to rebrand a site. System admins generally get tasked with cleaning up these pages.

The Fix
This solution is primarily targeted to systems admin who want to un-ghost many pages in one shot.

1. Open SQL Enterprise Manager
2. Navigate to the _SITE database that corresponds to the site WSS or portal site you want to clean up.
3. Open the Docs table in Query mode
4. Create a query of the form: SELECT DirName, LeafName, ContentFROM DocsWHERE LeafName = N’default.aspx’ AND Content IS NOT NULLThis query lets you see which pages will be affected. You can further constrain the potential changes by adding more conditions to the DirName field in the Where clause. Just make sure to use the full not relative path in the filter:

   SELECT DirName, LeafName, Content
   FROM DocsWHERE LeafName = N’default.aspx’ AND Content IS NOT NULL AND DirName = N’/Topics’

5. The last step is to change the query to an Update query and set the Content field to null. This is where the ghosted content lives:

   UPDATE Docs
   SET Content = NULL
   WHERE LeafName = N’default.aspx’ AND Content IS NOT NULL AND
   DirName = N’/Topics’

Stripping a time component off of a datetime field using SQL without parsing as a varchar

Introduction
I’ve often needed to seperate out the date or time components from a SQL datetime field. Traditionally, I and many colleagues have converted the datetime to a varchar, used string functions to parse out the desired component, and then converted the string back to a datetime. This approach has some serious issues:

• The string format for a datetime depends on the culture of the SQL Server excuting the query or function
• There is a prety big performance hit doing a lot of string parsing; you don’t want to have this in a sub query that generates a lot of rows or as part of a join expression

A new approach
Basically, a datetime field is stored internally as a float (double precision floating point number). The date part is everything to the left of the decimal point (the integer or whole number part). The time component is everything to the right of the decimal point (the fractional part).

Here’s an example: 38351.602349537 = 1/1/2005 2:27:23 PM

Stripping a off a time component
SELECT CONVERT (datetime, CONVERT (int, CONVERT (float, CONVERT (datetime, [DateToStrip])) * 10) / 10)

CREATE FUNCTION dbo.StripTime (@DateToStrip DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN CONVERT (DATETIME, CONVERT (INT, CONVERT (FLOAT, CONVERT (DATETIME, @DateToStrip)) * 10) / 10)
END

To remove the time component, we cast the datetime as a float, cast as an integer to strip off the fractional component, then cast back to a datetime. This is orders of magnitudes faster than converting to a varchar and using string operations to strip off the time component.

We multiply the float version of the date by 10 and then divide the integer version by 10 to simulate a conversion to an integer without rounding. Otherwise, datetimes that have a 12:00 PM or later time (represented as .5 to .99… in the decimal component) will be converted to a date for tomorrow when the time is stripped off.

Stripping off a date component
SELECT CONVERT (datetime, (1 – (CONVERT (float, [DateToStrip]) – CONVERT (int, CONVERT (float, [DateToStrip])))) * – 1 – 1)

CREATE FUNCTION dbo.StripDate (@DateToStrip DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN CONVERT (DATETIME, (1 – (CONVERT (FLOAT, @DateToStrip) – CONVERT (INT, CONVERT (FLOAT, @DateToStrip)))) * – 1 – 1)
END

To remove the date component, we need to get just the floating point part. So, we subtract the integer portion of the float from the whole float. This leaves us with just the decimal component. Next, we need to do some bit shifting, so we subtract the float component from 1. This leaves us with a the correctr bits, except for the sign bit, so we multiply by -1. All that’s left is to set the integer part to 1, so we subtract another 1 from the result.