Change Collation Settings for an Entire SQL Server 2000 Data

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.

Imports System.Data
Imports System.Data.OleDb

Private Sub MAIN_ROUTINE(ByVal Collation_Name As String)
'DEMO CALL:
' MAIN_ROUTINE("SQL_Latin1_General_CP1_CI_AS")

Dim connString As String
Dim arrTables As ArrayList
Dim sTable, sColumn As String
Dim iCtr, iCount As Integer

'NOTE: CHANGE CONNECTION STRING INFO TO MATCH YOURS. MUST HAVE
'SYS ADMIN PRIVILEGES
Dim cn As New OleDbConnection _
("Provider=SQLOLEDB.1;data source=sqlservername;user id=sa;password=mypassword;Initial Catalog=DBNAME;Connection Timeout=120")

Try

cn.Open()
Catch ex As Exception

Debug.WriteLine(ex.Message)
Exit Sub

End Try
GetDatabaseTables(cn)
arrTables = GetDatabaseTables(cn)
iCount = arrTables.Count - 1
For iCtr = 0 To iCount
ChangeCollation(cn, arrTables(iCtr), Collation_Name)
Next
cn.Close()
End Sub
Function GetDatabaseTables(ByVal cn As OleDbConnection) As ArrayList

'RETURNS: ARRAYLIST OF ALL TABLES IN A DATABASE
Dim objDataTable As DataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})


Dim arrListTables As New ArrayList()
Dim i As Integer
For i = 0 To objDataTable.Rows.Count - 1
arrListTables.Add(objDataTable.Rows(i)(2))

Next
Return arrListTables
End Function


Function ChangeCollation(ByVal cn As OleDbConnection, ByVal strTable As String, _
ByVal Collation_Name As String) As ArrayList


Dim arrListColumns As New ArrayList()

Dim objDt As DataTable
Dim objDr As DataRow
Dim objDS As New DataSet()
Dim objDA As OleDbDataAdapter

Dim objCommand As New OleDbCommand()

Dim sDataType As String
Dim sSQL As String
Dim sLen As String
Dim sColumn As String
Dim sNull As String

objDA = New OleDbDataAdapter _
("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" & strTable & "'", cn)
objDS.Tables.Add(strTable)
objDA.Fill(objDS, strTable)

Dim i As Integer



For i = 0 To objDS.Tables(strTable).Rows.Count - 1


If Not (objDS.Tables(strTable).Rows(i).Item("COLLATION_NAME") Is DBNull.Value) Then
sColumn = objDS.Tables(strTable).Rows(i).Item("COLUMN_NAME")

sDataType = objDS.Tables(strTable).Rows(i).Item("DATA_TYPE").ToString
sLen = objDS.Tables(strTable).Rows(i).Item("CHARACTER_MAXIMUM_LENGTH").ToString
sNull = objDS.Tables(strTable).Rows(i).Item("IS_NULLABLE")
sNull = IIf(sNull = "YES", " NULL", " NOT NULL")

sSQL = "ALTER TABLE " & strTable & " ALTER COLUMN " & sColumn
sSQL &= " " & sDataType
If CInt(sLen) <= 8000 Then sSQL &= "(" & sLen & ")"
sSQL &= " COLLATE " & Collation_Name
sSQL &= sNull
Debug.WriteLine(sSQL)

objCommand = New OleDbCommand(sSQL, cn)
'THIS WILL FAIL SOMETIMES, DOESN'T WORK FOR TEXT FIELDS
'AND IF THERE IS A CONSTRAINT IN SOME CASES
'YOU MAY WANT TO LOG TO A TEXT FILE ON FAILURE SO YOU KNOW
'WHAT YOU NEED TO CHANGE MANUALLY
Try
objCommand.ExecuteNonQuery()
Catch ex As Exception
Debug.WriteLine(ex.Message)
End Try

End If
Next

End Function