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:
- Create a new database on the same SQL-server as your SharePoint config database. I’ve named mine Test
- Add a new table, perhaps as such:
- 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.
- Enjoy your brand new exception.
/****** Object: Table [dbo].[Versions] Script Date: 09/04/2009 16:25:48 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Versions](
[Version] [int] NOT NULL,
[VersionId] [int] NOT NULL
) ON [PRIMARY]
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.
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!