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:

04.09

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]
    GO
    /****** Object:  Table [dbo].[Versions]    Script Date: 09/04/2009 16:25:48 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    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.
    04.09-2
  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.

.b

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

Published by

Bjørn Furuknap

I previously did SharePoint. These days, I try new things to see where I can find the passion. If you have great ideas, cool projects, or is in general an awesome person, get in touch and we might find out together.

4 thoughts on “Shared SQL Server for SharePoint? Watch Out!”

  1. If you get bored by the Version method above, here is another way to get this error:
    Place a database with compatibility level 70 (SQL2000) in your SQL instance. You don't need any columns or tables at all. Configuration Wizard will crash with a similar exception.

    /Mattias in Sweden

  2. I had the same Error while i was adding a new WFE in to the existing sharepoint farm. Your Solution is absolutely right it. I solved this issue by not retriving data bases but just enter the resrt of the info and point him to the right DB . It is working fine in my case

Leave a Reply

Your email address will not be published.