Tag: SharePoint 2007

SharePoint Upgrade Failure – Orphaned Documents not Orphaned Lists

Published August 31, 2011

Upgrading to SharePoint 2010 from 2007 is well worthwhile, and is significantly easier than the upgrade from 2003 to 2007 was. With that said, there are a number of things to look out for, and a number of bumps along the way. Running the stsadm –o preupgradecheck on your SharePoint 2007 farm identifies a number of the potential pitfalls and alerts you to their dangers.

Recently, I was at a client site doing a database attach upgrade, and we ran into a warning about orphaned objects. Orphaned objects will cause the upgrade to fail, so it needs to be remedied prior to the upgrade.

Orphaned objects are items that exist in the database, but aren’t properly connected to anything in the site collection. Orphaned objects are typically sites or lists. Joel Oleson has a good article on deleting orphaned sites, Orphaned lists are trickier with much of the guidance pointing to removing and then re-adding the content database. Also there is an stsadm command that should repair orphaned objects. The syntax is:

stsadm –o databaserepair –url yoururl –databasename contentDB –deletecorruption

Using the –deletecorruption directive goes ahead and fixes the problem, without it, it just tells you where the problem lies.

Unfortunately, nothing worked in my case. Running databaserepair simply resulted in a return that looked like the following:

<OrphanedObjects Count="1">
  <Orphan Type="SPList" Id="{787A6375-ABA3-4475-AE64-230853EB4448}" SiteId="{13AB0F9E-386B-4128-916C-E70BFC6A45F3}" />
</OrphanedObjects>

This discussion (in which the response marked as an answer isn’t the answer) got me pointed in the right direction. I am loathe to do anything directly in the content database, but if I had an orphan, I could at least find it there. I therefore opened up the AllLists table in the content database and did a SQL search for my list, like:

select * from AllLists Where tp_ID=’787A6375-ABA3-4475-AE64-230853EB4448’

Unfortunately, I got no results. This was baffling.

I had the GUID of the offending list, but it didn’t exist in the list table. I started rooting around elsewhere. It wasn’t too long before I found a reference to it in the AllDocs table, in the ListId column. To me, that meant that I didn’t have an orphaned list, but an orphaned document that was referring to a non-existent list..

After determining that the document was in fact disposable, I deleted its record with some simple SQL

DELETE FROM AllDocs Where ListID = ‘787A6375-ABA3-4475-AE64-230853EB4448’

Once that was done, I ran the preupgrade check, and all was clear – no more orphaned objects. We could proceed to the next roadblock… (more on that later).

This solution worked in my case, but as it involves monkeying with the content database, use at your own risk, and whatever you do – have a backup of the database available!

2 Comments

SharePoint Loses Access to Central Admin Content Database

Published February 1, 2011

I had a nasty problem with a client today that was running SharePoint 2007. They were doing some SQL database maintenance and inadvertently wound up with some problems with their msdb system database. They were able to get these rebuilt, and SharePoint appeared to work just fine. However, when I attempted to access the Central Administration application, I received the highly instructive “Unknown Error”.

The event log of course had a string of errors in it, most of which were Event 3760, with details below:

Cannot open database “SharePoint_AdminContent_ae0bba59-2a40-4110-8e9b-de1dcbbefc1f” requested by the login. The login failed. Login failed for user ‘xxxxxxxxx’.

SQL Server logs were also showing that access was denied, but the service account in question not only had all of the correct SQL roles, it was also the DB owner. This was obviously not actually a permissioning problem. The user could connect through SQL Management Studio, etc.

My next attempt at a solution was to remove, and re-add the Central administration application from the server using the Products and Technologies configuration wizard. That didn’t help at all, as it was simply referring to the same database, The problem obviously existed within SQL Server itself.

I could have removed the server from the farm,but that wasn’t something that I wanted to deal with,and the client wanted to prevent any more down time than was absolutely necessary. What finally worked was to create a new SQL database from a backup of the old one, and then configure Central Administration to use it. This also had the side benefit of removing the only remaining database in the farm with a GUID in its name.

The exact steps required to do this were:

Take a full SQL backup of the Administration Application content database.
Run stsadm –o deletecontentdatabase –url http://myurl:myport –databasename olddatabasename
Run SQL Management Studio and Detach the old content database. Either delete it, or move it to a new location. Don’t forget to do the same with the transaction logs
Restore the content database backed up in 1 to a new name (preferably without GUIDS). Watch the filenames (under the options tab) and use the overwrite option
Run stsadm –o addcontentdatabase –url http://myurl:myport –databasename newdatabasename

You should be good to go after this. This procedure also works if you just want to rename your Central Admin content database.

Using XSL to get the URL of the Current SharePoint Page for the Content Query Web Part

Published January 19, 2011

The Content Query Web Part (CQWP) is the Swiss army knife of SharePoint. I use it in all sorts of situations. Recently, I had a situation where I was displaying content from a central list on a series of decentralized sites. The content in the web part was being filtered by the site it was hosted on. When the users navigated to the site, all they saw were the items from the central list that pertained to the site they were on.

When the users clicked on the item in the result set, it opened the item. The problem was that when they closed the item, they would be returned to the source list, which was not what they expected.

The solution was to add the source URL parameter to the XSL that the CQWP used, but how would it know the site that should be returned? Thankfully, Spyral Out had already come across this requirement, and sorted it out. I repeat it here so that there is another source available. Here’s how to do it:

Add the NameSpace xmlns:ddwrt=”http://schemas.microsoft.com/WebParts/v2/DataView/runtime” at to the top of the xsl file (Usually ItemStyle.xsl)
Add a parameter “PageUrl “ <xsl:param name=”PageUrl” /> right below your namespace definitions

3. Assign this parameter along with the SafeLink URL to a variable, and then use the variable as the link target

<xsl:variable name=”DetailPageLink” select=”concat($SafeLinkUrl,’&Source=’,$PageUrl)” />
<a href=”{$DetailPageLink}” title=”{@LinkToolTip}”>

Once done,users can click through to an item,edit it, and when saved or closed, they will be returned to the page hosting the web part.

1 Comment

UNC Path Naming for files stored on SharePoint

Published December 2, 2010

If you didn’t already know, you can access any file stored in SharePoint (2007 or 2010) as though it was a folder on your system. The secret is to make it a network drive. You can do this a couple of ways. The easiest way is to open the library in Explorer. In SharePoint 2010, you’ll find this option in the list ribbon, on the library tab. Depending on the width of your screen, this may only appear as an icon.

Once you click on it, you’ll see your files in a regular Explorer Window. Depending on your Explorer settings, you may also see a folder named forms. Do not touch that folder, and do not attempt to add a new folder named forms…. you will break your library. That folder contains the files necessary to properly render the library in a browser. Just pretend that it isn’t there.

Once you do this, your system will “remember” this library and you will be able to to navigate directly to it through the Network node of your Explorer window.

You can also add this link directly, by right clicking on your computer in the explorer view, selecting Add Network Location, and entering the URL of your library (or server) there.

This is all possible due to the magic of WebDAV (Web Distributed Authoring and Versioning). It’s a protocol for transferring binary files over http and is what is behind the concept of web folders. It’s an interesting acronym because the none of the implementations of the protocol that I’ve ever seen (it’s been around for about 12 years now) have anything at all to do with versioning,but I digress.

For any of this to work,the WebClient service must be running on the client machine, and if you’re using a server operating system, the Desktop Experience must be installed.

This works great for most cases. If you’re opening the files in Word, Excel etc, those applications understand that they’re opening content in SharePoint and they’ll happily write directly back to it……in most cases. The problem is that what is actually happening is that the file is being brought down locally, and synchronized back to the server, or written back directly by the application.

We recently came across a case where we were using an Excel spreadsheet that was stored in SharePoint as a data source in an SSIS (SQL Server Integration Services) process. Everything worked just fine, but the package was not picking up any changes. As it turns out, when we told SSIS the source path of the Excel file, we used the WebDAV address, which was http://server/site/library/filename.xlsx. What then happened was that SSIS pulled down a copy of the file to a temp folder, and then used that file from then on. Not so good.

The fix for this was to use a UNC path (the standard machinenamesharedfolder style). What is the UNC path for a WebDAV folder? It’s not well advertised, but it’s

\ServerURLDavWWWRootSite1NameSite2NameLibraryNameFolderName

Server URL can be a FQDN or a machine name, depending on your configuration, and the site structure begins after DavWWWRoot. The DavWWWRoot is a constant that tells Explorer that it is dealing with WebDav, and must always be present.

It’s probably a good idea to use the UNC path whenever you need to programmatically access files stored on SharePoint.

24 Comments

Integrating SharePoint On Premises With BPOS and Exchange Online: Part 1–Outbound

Published September 21, 2010

If you’ve ever set up a SharePoint Farm, you’ll know that one of the first things that you need to configure is Outgoing Email. The way that you typically do this is to specify the name of an Exchange (or any other SMTP server) in your organization, a from and reply to address, and you’re done. Occasionally there are problems because the Exchange (or other) server does not allow relaying, and that’s easily remedied with a quick server configuration.

If you’re using BPOS, you not only have Exchange online, but you have SharePoint online. Outgoing email is already automatically set up for you. Just set an email alert for yourself and your will receive an email when anything changes. However, what about organizations that have elected to use hosted Exchange or BPOS, but also still maintain an on-premises SharePoint farm?

The good news is that it can be done. The bad news is that it’s not as simple as before when everything was on premises. This article will attempt to walk through the required steps.

This example uses SharePoint 2010 but the same is true (I think) for SharePoint 2007.

1. Setup SharePoint Outgoing Mail

As before, go to Central Administration, navigate to system settings, and click the “Outgoing E-Mail Settings” link.

There are two major things to note here. Firstly,the Outbound SMTP server is NOT one of the Online Services servers. What is it? Well,it doesn’t exist yet – we’ll get to that in step 3. The reason for this is that to deliver mail to any of the Online Services servers, you need to authenticate, use SSL, and use a non standard SMTP port. Unless I’m missing something, I don’t see any of those options here. (You can find complete instructions on relaying messages to BPOS and Exchange Online here). What we therefore need to do is to set up our very own SMTP server that can relay these messages for us.

The other thing to take note of here is the “From address”. When working internally, this doesn’t normally matter, you can give it any old fake name and off it goes. This is not true here, if this email address is not valid online, mail will not be delivered. We will add this address later.

2. Update DNS

If you haven’t already done so, you’ll need to add DNS entries for the SMTP server that will use the same IP address as this SharePoint server. In this example I’m using an internal domain, but you’ll also want to add another one externally if you’ll be configuring incoming email. all that will matter is that it resolve to the same machine.

3. Add the SMTP Service

Complete instructions on how to do this (as well as configuring mail for SharePoint generally) here. I will therefore not go into any detail except to point out that if you’re running on Windows versions prior to 2008, the installation procedure is slightly different. The end result is pretty well the same. In fact, I don’t think that this feature has been updated in about 10 years, it still requires the IIS6 admin interface.

4. Add Proxy User to Online Services

Unless you want all of your automated emails appearing as if they originated with an actual user, you’ll want to use a proxy user. The down side is – you’re going to pay a license for this user. Of course, given the cost of hosted Exchange, that’s not a big deal, but it would be nice if this wasn’t required.

If you’re reading this because you have BPOS or Hosted Exchange, you already know how to do this, so I won’t spell it out here. Just remember that this user needs to be the same as that specified in the first step.

5. Configure the SMTP Service

Open up the “Internet Information Services (IIS) 6.0 Manager” from the administrative tools group on the server. You should see your server as a node – open the node, and you’ll see the SMTP server. If it’s not already started, start it. Then right click on the server and select properties. When the properties box comes up, select the delivery tab. It should appear like the following:

If you haven’t already guessed it, we will be using all 3 numbered buttons.

1 – Outbound Security:

This is where we enter the credentials for the proxy user. Exchange Online checks to see if the “From” user and the authenticated user are one and the same. If they’re not, it rejects the message, which is why we need to have the proxy user, and to be careful about the “From” field in step 1.

TLS encryption is basically SMTP’s way of saying SSL, so this screen covers that off as well.

2 – Outbound Connections

This is the screen where we get to specify the non standard SMTP port used by Exchange Online. Use 587.

3 – Advanced

I have no idea why this is any more advanced than either of the other two screens, but no matter. This is where you enter the Online Services SMTP server as a Smart Host. You can also enter a masquerade domain (the “real” mail domain), but it’s optional.

When you’re done, click OK. Then, click the access tab. Click the Authentication button to ensure that Anonymous access is selected (it is by default). Then, click the relay button.

13 Comments