Change Collation Settings for an Entire SQL Server 2000 Database (VB.NET)

Category:
C#, VB.NET, ASP.NET
Type:
Modules
Difficulty:
Intermediate
Author:
Intelligent Solutions Inc.

Version Compatibility: Visual Basic.NET, ASP.NET

More information:
If you use SQL Server 2000, you may have come across a situation where you import data from another sql, then find queries that reference new and old data together fail because the default collation settings for two servers were different. To address this, you normally have to either change the collation settings for each character field one by one or rebuild the database in question. This VB.NET code offers another way, relying on system tables and views that are not that well documented. To use it, call the function MAIN_ROUTINE as demonstrated by the example. It should work in VB.NET or ASP.NET.

Please refer to the notes for a few issues. In particular, note that you will need sysadmin privileges on the SQL Server in order to do this, and that the change won't work on every single column, so you may want to log the columns where it doesn't work (not done here) in order to change those columns manually later.

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:

Code: