SQL2005 tools connectivity to 20082r2 after collation rebuild..

Question

we have a number of SQL 2005 clustered server instances using the following collation SQL_AltDiction_CP850_CI_AIS   A new SQL 2008r2 Enterprise cluster was created and after the cluster was put in to 'production' and some databases were added to it, We found out that the collation was set incorrectly to SQL_AltDiction_CP850_CI_AI.   Without wanting to perform a full reinstall I was able to find the following commands to run to rebuild an instance in the cluster to change it's collation.

setup.exe /q /ACTION=REBUILDDATABASE VS=#CLUSTERNAME# /INSTANCENAME=#INSTANCENAME# /SQLSYSADMINACCOUNTS="Domain\User"  /SQLCOLLATION= SQL_AltDiction_CP850_CI_AS

While all user databases were detached and the instance was offlined in the cluster the above command worked in changing the servers default collation.  The problem is now this, Any of the older VB tools that we created, that worked before running the command above no longer work.  Error displayed says: [SQL-DMO] This server object is not connected.

I can load SQL management studio 2005, connect to an instance that was rebuilt, I can see a database that I have attached. it's collation matches that of the server. I can drill down and see the tables, but if I right click on a table and select open table I get the following error: Unspecified error
 (MS Visual Database Tools)

Prior to the rebuilt I was able to perform all the functions above, the user created database was detached before the rebuild and attached after the rebuild and the intance was onlined.

However I can run queries to say, select top 1000 rows from a table from either 2005 and 2008 management tools and they work.  Is there a way to tell what else has been change to not allow the functions we used to use to stop functioning? like say for example SQL-DMO suddenly not working even though the 2005 compatibilty pack has been installed.

 

Answer 1

I suggest you to reinstall the SQL Server 2005, Management Studio. This should fix the issue.

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com