Changing the Collation on a table

A while ago, we came across this error in one of our main databases:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

So as  a workaround we created a view on the table to prevent it erroring:

select t.transaction_num, t.department_id, d.description 
from transaction_header t inner join department d
on t.department_id = d.department_id COLLATE Latin1_General_CI_AS
where t.department_id = 'SALES'

BUT none of this meant that the original problem was fixed. At the time it wasn’t a big issue and still doesn’t cause any problems but for completeness I wanted to sort this out.

Investigation

First, it was important to find out if any other columns in the database had the "SQL_Latin1_General_CP1_CI_AS" collation. For Info, the default collation on this database is "Latin1_General_CI_AS".

How to Find DB collation via T-SQL:

SELECT DATABASEPROPERTYEX('CMS3000Train', 'Collation') SQLCollation;

To find out which objects in the database have columns which have different collation to database default:

SELECT * FROM sysobjects 
  WHERE xtype = 'U' 
    and id IN 
    ( SELECT id FROM syscolumns WHERE collation <> 
    (SELECT DATABASEPROPERTYEX('MyDB', 'Collation')))

To get more detail on which columns are affected:

select name, id, colid, collation  from syscolumns
where id in (select id from sysobjects where name = 'department')

Solution

To resolve the collation and set it back to the same as the database default (after consideration of why it was set to be different in the first place!), the following can be run (only on non primary key columns)

ALTER TABLE department
Alter column description varchar (50) COLLATE Latin1_General_CI_AS

Changing Primary Key columns

The table needs to be rebuilt by pushing the data out into a temporary table and recreating the table.

BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Department
    (
    department_id varchar(5) NOT NULL,
    description varchar(50) NULL,
    date_created datetime NOT NULL,
    date_deleted datetime NULL,
    username_created varchar(20) NOT NULL,
    username_deleted varchar(20) NULL
    )  ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Department)
     EXEC(
    'INSERT INTO dbo.Tmp_Department 
    (department_id, description, date_created, date_deleted, username_created, username_deleted)
    SELECT department_id, description, date_created, date_deleted, username_created, username_deleted 
    FROM dbo.Department WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Department
GO
EXECUTE sp_rename N'dbo.Tmp_Department', N'Department', 'OBJECT' 
GO
ALTER TABLE dbo.Department ADD CONSTRAINT
    PK_Department PRIMARY KEY CLUSTERED 
    (
    department_id
    ) WITH
    ( 
    PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
    ) ON [PRIMARY]

GO
COMMIT

The original SQL can now be qeuried without the use of the COLLATE.

select t.transaction_num, t.department_id, d.description 
from transaction_header t inner join department d
on t.department_id = d.department_id 
where t.department_id = 'SALES'

Embed Flash into WordPress.com…

Useful link that i found which lets you embed flash into a free wordpress blog

Embed Flash

Raising money for the British Heart Foundation….

Hi Everyone,

After months of exercising and jogging on a casual basis, I’ve finally plucked up the courage to do it for a cause! I am going to be running the Manchester Great 10k Run for the British Heart Foundation on the 15th of May. I’ve decided to choose the BHF as i know several people who have been affected by Heart problems and if i can help the BHF with a contribution towards helping them continue the great work that they do, then the pain of the run will be satisfying!

People are at the heart of what the BHF does, but this isn’t possible without the support and charitable donations of you guys So…………

Please, Please, Please donate generously so that together we can beat heart disease.

Vodpod videos no longer available.