Do-It-Yourself SharePoint Tools – SPSiteLister

A while back, I wrote about my philosophy on using SharePoint tools, and how I preferred to create my own tools. A total of 1 reader wrote to me at and asked if I could provide some examples. Don’t say I do not listen to your comments and emails, so I’m going to post some sample SharePoint tools that I have made and use on a very regular basis. The first tool is the SPSiteLister I mentioned.

First, check out the article on how to add SPDisposeCheck to the Tools menu of Visual Studio. As the comments revealed, however, I was too lazy and didn’t bother Googling first, which would have revealed that there were already several articles describing this. And no, I didn’t make SPDisposeCheck. However, the technique of adding tools to Visual Studio is very important.

The other tool I mentioned in the tools philosophy article was a tool to list sites and web applications in a farm. I call this the SPSiteLister, and I keep it as an external tool as well. Since I work on many projects at a time, or at least, need access to multiple sites in my lab, I want to list all the web applications and the root webs so that I know which site I want. Here’s the output from that tool:

Figure 1

This tool is very simple to create, but is a perfect example of the ‘create a tool when you need it’ philosophy. You may not need just this tool, but I do, which is why I made it, and it has saved me tons of time.

To make this tool, you can follow these steps:

  1. Create a new Console Application in Visual Studio
  2. Add references to Microsoft.SharePoint.dll
  3. Add the following two using-statements:
    using Microsoft.SharePoint;
    using Microsoft.SharePoint.Administration;
  4. Add the following code inside your Main method:
    SPFarm farm = SPFarm.Local;
    SPWebService webService = farm.Services.GetValue(“”);
    foreach (SPWebApplication webApp in webService.WebApplications)
        using (SPSite site = webApp.Sites[0])
            using (SPWeb rootWeb = site.RootWeb)
                Console.WriteLine(webApp.DisplayName + ” | ” + site.Url + ” | ” + rootWeb.Title);
  5. Compile and add the resulting .exe as an external tool in Visual Studio.

When making these tools, I add the external tool directly to the build-folder of my Visual Studio project. That way, if I need to change the tool in any way, I can simply make the changes, compile, and the new tool is available immediately. When I move to other development lab environments, I bring the entire Visual Studio project for the tool to the new lab.

I think I’ll post more info on other tools I’m using in later posts, which may or may not benefit you, but at least may give you inspiration to start thinking about creating your own toolset.

In addition, you may want to check out the free SharePoint Troubleshooting issue of Understanding SharePoint Journal by Ayman El-Hattab.


Found this article valuable? Want to show your appreciation? Here are some options:

a) Click on the banners anywhere on the site to visit my blog's sponsors. They are all hand-picked and are selected based on providing great products and services to the SharePoint community.

b) Donate Bitcoins! I love Bitcoins, and you can donate if you'd like by clicking the button below.

c) Spread the word! Below, you should find links to sharing this article on your favorite social media sites. I'm an attention junkie, so sharing is caring in my book!

Pin It

Shared SQL Server for SharePoint? Watch Out!

I just encountered a weird problem. When trying to run the SharePoint Products and Technologies Configuration Wizard and connecting a server to an existing farm, the whole config wizard crashed when we clicked the Retrieve Database Names, sporting a fancy new exception I have never seen before:


System.Data.SqlClient.SqlException: Operand type clash: uniqueidentifier is incompatible with int
Invalid column name ‘Version’.

Of course, Googling this didn’t turn up any relevant result, at least not when combined with SharePoint. That’s about to change 🙂

After a few hours of searching we found out that the problem is that the SharePoint config wizard iterates through all the other databases on the server, and runs a query to try to detect whether the database is a SharePoint config database.

The way the wizard does this is by checking for the existence of a table called Versions, and if found, tries to get the value from the Version column in the row where VersionId is equal to a vertain value, as such:

SELECT @Version=Version FROM [dbo].[Versions] WHERE VersionId=@VersionId

This may seem like a good idea, until you realize that if you already have a database, usually non-SharePoint, with incompatible column types, such as one where VersionId is an int rather than the uniqueidentifier that the config Wizard expects, then you get the above exception.

To recreate the exception, do the following, which is very non-intrusive and won’t hurt your SQL-server:

  1. Create a new database on the same SQL-server as your SharePoint config database. I’ve named mine Test
  2. Add a new table, perhaps as such:
  3. USE [test]
    /****** Object:  Table [dbo].[Versions]    Script Date: 09/04/2009 16:25:48 ******/
    CREATE TABLE [dbo].[Versions](
        [Version] [int] NOT NULL,
        [VersionId] [int] NOT NULL
    ) ON [PRIMARY]

  4. Run the SharePoint Products and Technologies Configuration Wizard on a new server and attempt to connect to the SQL server by hitting the Retrieve Database Names.
  5. Enjoy your brand new exception.

So, how do you work around this issue? Well, delete the offending database, of course! Or, if you plan on keeping your job, try setting explicit deny access on the offending database for your install account. Of course, if you run the config wizard as administrator, that’s out of the question.

This problem only occurs when adding new servers to the farm, so one option may be to simply take the offending database offline while you set up your new server. If you really want to be hardcore, and remember my default disclaimer that doing anything I say in production is just plain stupid, then you can copy the dsn value from the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Secure\ConfigDB registry key from one of the existing servers.

But, as I said, that’s just plain stupid.


Pin It

SPDisposeCheck in Visual Studio

One of the comments I have gotten from the latest USP Journal issue is that running the SPDisposeCheck is awkward because you have to do it via command line.

That’s not necessarily true, there’s an easier way.

Visual Studio supports external tools, allowing you to run command line tools and get the output in the Visual Studio output window. Since SPDisposeCheck is a command line tool, that’s a perfect match.

To set up SPDisposeCheck as an external tool in Visual Studio, go to the Tools->External Tools menu option. Click Add and fill in the form by browsing to the SPDisposeCheck.exe file (usually in C:\Program Files\Microsoft\SharePoint Dispose Check\SPDisposeCheck.exe) and select TargetPath in the Arguments field drop-down, as shown below.

Figure 1

Finally, ensure that the Use Output window checkbox is checked, and hit OK.

Check your Tools menu, and you should have a new option for SPDisposeCheck. Selecting this option will run SPDisposeCheck and output the results from checking your DLL or EXE in the Output window of Visual Studio.

Figure 3

If you want a faster way of accessing SPDisposeCheck, add an & in front of one of the letters in the Title field. That letter now becomes the shortcut key for accessing SPDisposeCheck from the Tools menu. For example, if you set the title to SPDisposeC&heck;, you can run the command using ALT+T (for Tools menu) and then hit H. Sadly, H is the only letter in SPDisposeCheck not already occupied by other menu commands, but you are, of course, free to set the title to anything you like, such as Xyzzy and use X, Y, or Z as the quick-key 🙂

Figure 2


Pin It