Programmatically Creating SharePoint Lists, Be Careful With Column Names and Watch Out for the Trailing Zero

Recently I was working on a Feature that when activated would create a new list in the site.  I was creating the list and its columns via code, and had a line that looked something like this:

SPFieldText fldGuid = (SPFieldText)newList.Fields.CreateNewField(SPFieldType.Text.ToString(), Constants.FieldGuid);
newList.Fields.Add(fldGuid);

So here I'm creating a new Text field with the name of "GUID" and adding it to the list.  This works great in the code, the list is created and my column is there.  This can be easily confirmed by surfing to list and visually checking.

Now I populate some data in the list with a valid GUID and start to query the list using CAML.  And, presto.......nothing comes back!  Even if I used the same GUID hard coded nothing will return.  In these scenarios its time to open CAML Designer and make sure my query is correct.  Looking at the list in Designer, I see that the column name has a trailing zero (GUID0)!

What?!  After some head scratching I remember that SharePoint has a set of reserved names that you cannot use for your column names.  GUID is definitely one of those.

You can get a complete list of the reserved names here: Reference list for SharePoint internal field names.

So, the moral of the story here is keep in mind reserved column names when programmatically creating lists and columns in SharePoint.  As you can see in this example, SharePoint will not throw any errors, just modify the internal name for you!

Enjoy!

Using Strings and Tokens as Querystring Parameters in SharePoint Links

At some point during your development adventures you'll need to put a rather generic link somewhere with the expectation that no matter where it is displayed you can get relevant information about the current site, list, etc.  For me this ended up being a Custom Action.  I needed to place a link on the List Settings page and append the list ID as a querystring parameter.  Luckily SharePoint has a set of URL tokens that help out in these scenarios.

First let's take a look at my Custom Action:

  <CustomAction Id="Custom.Configuration.ListCustomSettings"
    Title="My Awesome Link"
    Location="Microsoft.SharePoint.ListEdit"
    RequireSiteAdministrator="FALSE"
    GroupId="GeneralSettings"
    Sequence="1000" >
    <UrlAction Url="~site/_layouts/MyAdminPage.aspx?ListID={ListId}" />
  </CustomAction>

As you can see above the UrlAction Url property is taking advantage of two of these tokens:

• ~site - Web site (SPWeb) relative link.
• {ListId} - GUID that represents the list.

Now when this link is rendered on the List Settings page, the URL of the site and the list GUID will be populated in the link.  So this link can be rendered on any List Settings page throughout my Farm, and I can be guaranteed to get relevant information about where its being rendered.

MSDN has posts detailing all of the tokens available to you for your version of SharePoint:

SharePoint 2010 - http://msdn.microsoft.com/en-us/library/ms431831(v=office.14).aspx
SharePoint 2013 - http://msdn.microsoft.com/en-us/library/ms431831(v=office.15).aspx
Apps for SharePoint - http://msdn.microsoft.com/en-us/library/jj163816.aspx

Enjoy!

PowerShell for SharePoint Command Builder

The PowerShell for SharePoint Command Builder site has been around for a while now, since about November of 2012, but its an amazing tool that a lot of SharePoint folks don't know about.  The site is a single page HTML5 application designed to help you build PowerShell commands on the fly.  The page is broken into two categories, verbs and nouns.  Simply choose you verb, the choose your noun and you're on your way to building a PowerShell command for SharePoint.

So let's say we need to add a Farm solution.  We'll choose "Add" from the verbs section.  Instantly the "Nouns" section is filtered to what we can add, how about that.

Clicking the "Send" button in the "Verbs" section adds the verb to the Design Surface.  Now, click "Farm Solution" in the "Nouns" section and click "Send".

Now the Design Surface has our verb and noun, and some code is already generated.

You can see in the bottom pane the "Add-SPSolution" command is starting to form.  There is also a link to the MSDN content for the command which is great.  Let's fill the remaining information.  We'll add the "Solution Path" and the command will be updated.

And that's it!  We can click "Copy to Clipboard" to save the command and paste into PowerShell.  Clicking the "Clear Design Surface" will remove everything you've selected and put the page back into its starting point.

At the top of the page there are two dropdowns.  One provides options for your specific SharePoint environment.  The other provides quick pre-built commands you can pull up on demand.

This is a great page to help you build out your PowerShell commands.  If you're like me, you remember most of the ones you need, but there is no way you can remember every command along with its parameters.  This site provides a quick way to view all the commands and get links to MSDN, its invaluable as a SharePoint tool.

Enjoy!

Upload a List Template (.STP) File to the List Template Gallery in SharePoint 2010 using PowerShell

I recently needed to upload some custom list templates that were created in SharePoint to other environments, up from development and into UAT and Production.  Of course adding new list templates to the gallery is easy, just navigate to the gallery and upload them!  But I wanted to write some PowerShell so these could be scripted.  I needed a little search engine help to remember the exact PowerShell commands and lo and behold there wasn't a single post about this topic.  So here it is, the PowerShell needed to upload a list template to the List Template Gallery:

$web = Get-SPWeb http://yoururl
$spFolder = $web.GetFolder("List Template Gallery")
$spFileCollection = $spFolder.Files
$file = Get-ChildItem "C:\yourawesomelisttemplate.stp"
$spFileCollection.Add("_catalogs/lt/yourawesomelisttemplate.stp", $file.OpenRead(), $true)

Enjoy!

Annual Review of Documents Using PowerShell in SharePoint

In your SharePoint travels you may come across a requirement to have users review documents based on a timeframe.  For instance, the requirement may say something like:

"Users must review documents every 12 months to ensure content is correct and up to date. Etc, etc."
 

This can be accomplished in a number of different ways in SharePoint, but in this post we'll look at how to accomplish this using PowerShell.  This demo will show you how to query a document library for documents that fall within a date range and then notify the document's owner that they must review the document.

This script can be downloaded from the Microsoft TechNet Gallery.

You may ask yourself at this point, why not use a workflow.  This is a great question, as you could certainly use a workflow here.  However, consider an environment where you don't have any SharePoint administrators, or even any seasoned power users.  This is more common that some may think.  In these scenarios, you will most likely have seasoned server admins, who most likely have PowerShell experience. Using this method, a little bit of SharePoint knowledge can be applied to a PowerShell script to provide the intended goal.

So let's look at our requirements:

1. Query a specific document library and find documents with a review date 30 days from the day the script is run.
2. Email the document owner and notify them that the document must be reviewed.

Pretty simple!  At least for this demo.  Let's start with creating a document library.  It will be simple, just creating a out of the box library and adding two columns:

• Review Date - This is the date the document must be reviewed by.
• Document Owner - This is the owner of the document who will be emailed.

After creating, we just need to add a few documents:

We'll upload three documents and give two review dates 30 days out, and one 31 days out.  This will give us a good test as our script should find two documents.

Now let's create our PowerShell script.  We'll break this up into sections to make it easier to break down, but the entire script is at the bottom of the post ;)

Add in the SharePoint Snapin

if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}
 

First things first, we need to make sure the SharePoint snapin is loaded and if not, load it.  Without the snapin you won't be able to access anything in the SharePoint object model, so its important.

Variables

$webUrl = "http://sp2010"
$listDisplayName = "Annual Review Documents"
$dateColumnInternalName = "Review_x0020_Date"
$ownerColumnInternalName = "Document_x0020_Owner"
$reviewDateDaysOut = 30
$startDateRangeDays = 29
$endDateRangeDays = 31
$smtpServer = "191.168.1.1"
$emailFromAddress = "annualreview@dev.com"
$emailSubjectLine = "Annual Review Documents Notification"
# DO NOT CHANGE THE FOLLOWING VARIABLES!
$dateToStringFormat = "yyyy'-'MM'-'dd HH':'mm':'ss'Z'"
$logTextFileName = ".\log-" + (Get-date).ToString($dateToStringFormat) + ".txt" -replace " ","-" -replace ":","-"
$reviewDate = (Get-date).AddDays($reviewDateDaysOut).ToShortDateString()
$viewFilter = "?FilterField1=" + $dateColumnInternalName + "&FilterValue1=" + $reviewDate 

 Let's look at each of these variables:

• $webUrl - This is the URL to site where the library is located.
• $listDisplayName - This is the friendly name of the library we are searching.
• $dateColumnInternalName - This is the internal name of the library column that holds the review date.
• $ownerColumnInternalName - This is the internal name of the library column that holds the document owner.
• $reviewDateDaysOut - This is how many days in the future we want to search the library for.  In our script we set this to 30, so the day the script runs, we are looking for documents which expire 30 days from now.
• $startDateRangeDays - Since we are searching a date range (I'll explain later), we need to specify a start and end range.  Using 30 for how many days into the future to search, we'll set this to 29, one day prior.
• $endDateRangeDays - Set this to one day after your desired target days.  For our example it will be 31, one day after.
• $smtpServer - This is the IP address of your email server that will be used to send the notification emails.
• $emailFromAddress - This is the email address that will display in the From field.
• $emailSubjectLine - This is the email subject line.

The next set of variables should not be changed, which is why we've included the line "DO NOT CHANGE THE FOLLOWING VARIABLES!".  Let's look at these:

• $dateToStringFormat - This is a format that we apply to all the dates used in this script.  This will put dates into a format that SharePoint likes for querying (I'll explain later).
• $logTextFileName - Like any good script we'll do some logging of what we find and processed.  This line creates a log file name with a date/time stamp.  It will remove spaces and colons so the file name is valid for Windows.
• $reviewDate - This is a date that will be included in the notification email.  It simply gets today's date and adds the $reviewDateDaysOut number to it.
• $viewFilter = This is a querystring that will be appended to a link to the library we are searching.  We'll provide a link to the library in the email so a user can view the library filtered to the date we are searching for.

Now let's take a moment to explain all the items I said I would explain later.  These all deal with dates and why we search for a date range versus just searching for documents that expire on the exact date we're looking for.  This is because SharePoint stores a date and time for date fields.  Even if you have marked that field as Date Only, in the backend there is still a time associated with that date.  So simply searching for a date without a time will not return anything.  To get around this, we search for one day prior and one day after the target date.

Now the date format string will put the date time into a format that SharePoint likes and will make your CAML query successful.  Whew!  Not that we've gone into that, back to the script!

Functions

# Sends an email to the specified address
function SendEmail($ownerEmail, $docLibraryViewUrl, $documentName) {
 try {
    $smtp = new-object Net.Mail.SmtpClient($smtpServer)

  $msg = EmailStructure $ownerEmail $docLibraryViewUrl
 $documentUrl
    $smtp.Send($msg)
  $emailSentMessage = "Email sent to " + $ownerEmail
  AppendLogMessage $emailSentMessage
 }
 catch {
  Write-Host "Error sending email, check log file!" -ForegroundColor Red
  AppendLogMessage $error[0]
 }
}


# Builds the email message that will be sent
function EmailStructure($to, $docLibraryViewUrl
) {

   $msg = new-object Net.Mail.MailMessage
 
   $msg.IsBodyHtml = $true
   $msg.From = $emailFromAddress
   $msg.To.Add($to)
   $msg.Subject = $emailSubjectLine
   $msg.Body = "Review items for $listDisplayNameThe following document ($documentName) has a review date of $reviewDate.

Click here to review all documents which have a review date on $reviewDate."

  return $msg
}

# Appends text to the log message
function AppendLogMessage($text) {
 Add-Content $logTextFileName $text"`r" -Encoding UTF8
}
 

We have a few functions to assist with email and logging:

• SendEmail - This function will build the email message and send the email.
• EmailStructure - The SendEmail function calls this function to build the actual email message.
• AppendLogMessage - This function handles adding lines to the log file.  We set the encoding here as well to ensure we get English in the file!  If you need another encoding you can change it here.

Create the Log File

# Creating base file to append to, adding a new line for readability
"Log file created." >> $logTextFileName
AppendLogMessage " "
 

This simply creates the log file we'll be appending to.  We use the  $logTextFileName variable to create it, then we just add a single line with nothing in it to get the line break our AppendLogMessage adds.

Get Reference to Site and Library

# Get the access to SP lib, open it
$web = Get-SPWeb $webUrl
$docLib = $web.Lists[$listDisplayName]
 

Next we just grab a reference of the site and library.

Set the Date Range for CAML Query

# Set the date range
$reviewStartDate = (Get-date).AddDays($startDateRangeDays).ToString($dateToStringFormat)
$reviewEndDate = (Get-date).AddDays($endDateRangeDays).ToString($dateToStringFormat)

# Write out the dates we're looking for
$dateRangeText = "INFO: Searching for documents with review dates between " + $reviewStartDate + " - " + $reviewEndDate
Write-Host $dateRangeText -ForegroundColor Green
AppendLogMessage $dateRangeText
 

 Here we are just creating two new variables to store our date range values.  We'll use the variables we setup early to calculate these dates.  Then we log the date range we're searching for to the log file and write that same message out to the PowerShell window.

Query the Library

# Build the SPQuery
$camlQuery = '' + $reviewStartDate + '' + $reviewEndDate + ''
$spQuery = new-object Microsoft.SharePoint.SPQuery
$spQuery.Query = $camlQuery
$spListItems = $docLib.GetItems($spQuery)

# Write out the number of documents found
$docFoundText = "INFO: Found " + $spListItems.Count + " document(s)."
Write-Host $docFoundText -ForegroundColor Green
AppendLogMessage $docFoundText
 

 Next we'll build our CAML query to search using the date range variables.  As you can see in the query, we search for dates using the GT (greater than) and the LT (less than) tags.  Once we have the SPQuery object built, we call the GetItems method to search the library.  We'll log how many items we found and show that in the PowerShell window.

Process the Documents

# Log start of processing
$beginProcessingText = "INFO: Start processing documents."
Write-Host $beginProcessingText -ForegroundColor Green
AppendLogMessage $beginProcessingText

# Loop through items and write out info
foreach ($item in $spListItems) {

 # Get the SPUser object from the column
 $spFieldUser = [Microsoft.SharePoint.SPFieldUser]$item.Fields.GetField($ownerColumnInternalName);
 $spFieldUserValue = [Microsoft.SharePoint.SPFieldUserValue]$spFieldUser.GetFieldValue($item[$ownerColumnInternalName].ToString());
 $user = $spFieldUserValue.User;

 # Get the user email to test for null/empty
 $userEmail = $user.Email
 # If the User's email is present send the email, otherwise log the error
 if ($userEmail) {
  # Build URL for item
  $docLibraryViewUrl = $web.Url + "/" + $docLib.DefaultView.Url
  $ownerDocEmail = "INFO: Document '" + $item.Name + "' has owner " + $userEmail
  AppendLogMessage $ownerDocEmail
  SendEmail $userEmail $docLibraryViewUrl $item.Name
 }
 else {
  $emptyDocEmail = "ERROR: No email is set for '" + $item.Name + "' with document owner: " + $user.DisplayName
  Write-Host $emptyDocEmail -ForegroundColor Red
  AppendLogMessage $emptyDocEmail
 }
}
 

Once we have run our query we need to process the results.  We do this using a simple foreach loop.  If no items are returned these will just be ignored.  First we have to get the SPUser object out using our $ownerColumnInternalName variable.  There is a blog post here that explains this piece of the script.

Once we have an SPUser object pulled out, we need to get the email address out.  Then we test if the email address is null.  If the email address is present we build a URL for the library then call the SendEmail function passing the email address, library default view URL (which is appended with the filter querystring), and the name of the document.  We also log this as we go.

If no email has been set for the owner, we log that and move on.

Cleanup

# Log end of processing
$endProcessingText = "INFO: Finished processing documents."
Write-Host $endProcessingText -ForegroundColor Green
AppendLogMessage $endProcessingText

# Dispose of the web object
$web.Dispose()
 

At the end we log that we're done processing and we dispose of the web object we created at the very beginning.

That's it!  Its a long blog post, but the script is not too complicated.  Let's run this and see what happens.

First, we'll create a folder to hold the script and log files.  In this example we create a folder called "Annual-Review-Notifications" located on the C: drive.  We'll save a version of this script in that folder called "Document-Review-Task":

If you were to use this on your server, you would most likely create a folder for each script and name them something that relates to the library name so its easier to idenitfy.

Now let's launch a PowerShell window:

As you can see, this is a base PowerShell window that is pointing to the newly created folder.  Running the script we'll the output from the script in the window:

 

Oh noes!  We got an error!  Well this was to be expected as I do not have email addresses setup in my development environment ;)  Who wants to see a perfectly run script anyways?  Ignoring the error we'll see that the script kicked off and notified us that is was searching in a certain date range.  It then notifies how many documents it found and that its begun processing them.  It notifies that it could not find an email for the document owner then it finishes.

We'll also see a log file has been created in the folder with a timestamp in its name:

Opening the log file we'll see the same messages from the PowerShell window:

If your emails are setup correct, :/, you'll see a line that indicates an email was sent to a certain email address.

And that's it!  In most cases you'll want to set this up as a scheduled task and just let it run on the server each night.  You can easily copy this script and run one for each library where you need to send notifications.

Here is the full script:

###############################################################################
##  ADD IN SHAREPOINT SNAP IN IF NOT ALREADY LOADED ##
###############################################################################

if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}

###############################################################################
##  VARIABLES ##
###############################################################################

$webUrl = "http://sp2010"
$listDisplayName = "Annual Review Documents"
$dateColumnInternalName = "Review_x0020_Date"
$ownerColumnInternalName = "Document_x0020_Owner"
$reviewDateDaysOut = 30
$startDateRangeDays = 29
$endDateRangeDays = 31
$smtpServer = "191.168.1.1"

$emailFromAddress = "annualreview@dev.com"
$emailSubjectLine = "Annual Review Documents Notification"
# DO NOT CHANGE THE FOLLOWING VARIABLES!
$dateToStringFormat = "yyyy'-'MM'-'dd HH':'mm':'ss'Z'"
$logTextFileName = ".\log-" + (Get-date).ToString($dateToStringFormat) + ".txt" -replace " ","-" -replace ":","-"
$reviewDate = (Get-date).AddDays($reviewDateDaysOut).ToShortDateString()
$viewFilter = "?FilterField1=" + $dateColumnInternalName + "&FilterValue1=" + $reviewDate

###############################################################################
##  FUNCTIONS ##
###############################################################################

# Sends an email to the specified address
function SendEmail($ownerEmail, $docLibraryViewUrl, $documentName) {
 try {
    $smtp = new-object Net.Mail.SmtpClient($smtpServer)

  $msg = EmailStructure $ownerEmail $docLibraryViewUrl
 $documentUrl
    $smtp.Send($msg)
  $emailSentMessage = "Email sent to " + $ownerEmail
  AppendLogMessage $emailSentMessage
 }
 catch {
  Write-Host "Error sending email, check log file!" -ForegroundColor Red
  AppendLogMessage $error[0]
 }
}
# Builds the email message that will be sent
function EmailStructure($to, $docLibraryViewUrl
) {

   $msg = new-object Net.Mail.MailMessage
 
   $msg.IsBodyHtml = $true
   $msg.From = $emailFromAddress
   $msg.To.Add($to)
   $msg.Subject = $emailSubjectLine
   $msg.Body = "Review items for $listDisplayNameThe following document ($documentName) has a review date of $reviewDate.

Click here to review all documents which have a review date on $reviewDate."

  return $msg
}

# Appends text to the log message
function AppendLogMessage($text) {
 Add-Content $logTextFileName $text"`r" -Encoding UTF8
}

###############################################################################
##  CREATE LOG FILE ##
###############################################################################

# Creating base file to append to, adding a new line for readability
"Log file created." >> $logTextFileName
AppendLogMessage " "

###############################################################################
##  WEB AND DOCUMENT LIBRARY ##
###############################################################################

# Get the access to SP lib, open it
$web = Get-SPWeb $webUrl
$docLib = $web.Lists[$listDisplayName]

###############################################################################
##  SET DATE RANGE ##
###############################################################################

# Set the date range
$reviewStartDate = (Get-date).AddDays($startDateRangeDays).ToString($dateToStringFormat)
$reviewEndDate = (Get-date).AddDays($endDateRangeDays).ToString($dateToStringFormat)
# Write out the dates we're looking for
$dateRangeText = "INFO: Searching for documents with review dates between " + $reviewStartDate + " - " + $reviewEndDate
Write-Host $dateRangeText -ForegroundColor Green
AppendLogMessage $dateRangeText

###############################################################################
##  QUERY THE DOCUMENT LIBRARY ##
###############################################################################

# Build the SPQuery
$camlQuery = '' + $reviewStartDate + '' + $reviewEndDate + ''
$spQuery = new-object Microsoft.SharePoint.SPQuery
$spQuery.Query = $camlQuery
$spListItems = $docLib.GetItems($spQuery)
# Write out the number of documents found
$docFoundText = "INFO: Found " + $spListItems.Count + " document(s)."
Write-Host $docFoundText -ForegroundColor Green
AppendLogMessage $docFoundText

###############################################################################
##  PROCESS DOCUMENTS AND SEND EMAILS ##
###############################################################################

# Log start of processing
$beginProcessingText = "INFO: Start processing documents."
Write-Host $beginProcessingText -ForegroundColor Green
AppendLogMessage $beginProcessingText
# Loop through items and write out info
foreach ($item in $spListItems) {

 # Get the SPUser object from the column
 $spFieldUser = [Microsoft.SharePoint.SPFieldUser]$item.Fields.GetField($ownerColumnInternalName);
 $spFieldUserValue = [Microsoft.SharePoint.SPFieldUserValue]$spFieldUser.GetFieldValue($item[$ownerColumnInternalName].ToString());
 $user = $spFieldUserValue.User;

 # Get the user email to test for null/empty
 $userEmail = user.Email
 # If the User's email is present send the email, otherwise log the error
 if ($userEmail) {
  # Build URL for item
  $docLibraryViewUrl = $web.Url + "/" + $docLib.DefaultView.Url
  $ownerDocEmail = "INFO: Document '" + $item.Name + "' has owner " + $userEmail
  AppendLogMessage $ownerDocEmail
  SendEmail $userEmail $docLibraryViewUrl $item.Name
 }
 else {
  $emptyDocEmail = "ERROR: No email is set for '" + $item.Name + "' with document owner: " + $user.DisplayName
  Write-Host $emptyDocEmail -ForegroundColor Red
  AppendLogMessage $emptyDocEmail
 }
}

###############################################################################
##  CLEAN UP ##
###############################################################################
# Log end of processing
$endProcessingText = "INFO: Finished processing documents."
Write-Host $endProcessingText -ForegroundColor Green
AppendLogMessage $endProcessingText
# Dispose of the web object
$web.Dispose()

This script can be downloaded from the Microsoft TechNet Gallery.

Enjoy!

Get an SPUser Object From a List or Library in SharePoint Using PowerShell

At some point in your journeys with PowerShell and SharePoint you'll need to pull a user from a list/library column and get an email address or login name, etc.  If you've never done this before it can be a little frustrating as you have to jump through a couple of hoops.  Let's take a look at simply querying the list.

Let's assume we have a library with a Name column and a user column called "Owner".  Now we can easily query the library and pull out the "Name" with something like this:

# $docLib is a reference to a library and $spQuery is an SPQuery object with a CAML query
$spListItems = $docLib.GetItems($spQuery)

# Loop through items and write out info
foreach ($item in $spListItems) {
    Write-Host $item.Name
}

In this example we simply query the library and loop through the results, writing out the "Name".  But what if we needed to also write out the email address of the user column.  Here is where the hoops come in, fortunately they are small.

You have to perform some casts in order to get the actual SPUser object.  So taking the previous example, we can re-write it like this:

# $docLib is a reference to a library and $spQuery is an SPQuery object with a CAML query
$spListItems = $docLib.GetItems($spQuery)

# Loop through items and write out info
foreach ($item in $spListItems) {
     Write-Host $item.Name

    # Get the SPUser object from the column
   $spFieldUser = [Microsoft.SharePoint.SPFieldUser]$item.Fields.GetField("Owner");

   $spFieldUserValue = [Microsoft.SharePoint.SPFieldUserValue]$spFieldUser.GetFieldValue($item["Owner"].ToString());

   $user = $spFieldUserValue.User;

   # Pull out the user email
   Write-Host = $user.Email}

In this example we first have to create an SPFieldUser object using $item.Fields.GetField("Owner").  Once we have that reference we can use the GetFieldValue method to return an SPFieldUserValue object, which will contain the actual SPUser object.  Now we have access to all the properties on the user like Email, DisplayName, LoginName, etc.

As you can see, its pretty easy to get to the user object in SharePoint via PowerShell.

Enjoy!

SharePoint 2010 Missing Server Side Dependencies and SiteOrphan, Deleting the Orphaned Site

Recently I was working on a server that decided it was ready to start giving us problems.  When trying to create new site collections or sites SharePoint would error out and no site(s) would be created.  Checking the SharePoint Health Analyzer showed the all too familiar "Missing server side dependencies" error.  Opening this revealed all the sites that were never created, but were orphaned.  These errors look like this:

[SiteOrphan] Database [DATABASE_NAME] contains a site (Id = [SITE_GUID], Url = [RELATIVE_URL]) that is not found in the site map. Consider detach and reattach the database. The orphaned sites could cause upgrade failures. Try detach and reattach the database which contains the orphaned sites. Restart upgrade if necessary.

Now like any good SharePoint professional working in 2010, I went looking for the Powershell command that would clear this up.  Alas, there is none.  You'll need to use the good and trusty STSADM to handle this issue.  Luckily its quite simple, the following command will delete the offending site:

stsadm -o deletesite -siteid SITE_GUID -databaseserver DB_SERVERNAME -databasename DB_NAME -force 

The site guid and database name you can get from the Health Analyzer details, as it tells you both of these.  You'll just need to track down the database server name, which is easy.  Just navigate to Central Administration > System Settings > Manage servers in this farm.  That page will show you the database server with its name.

Enjoy!