Had the same issue, try this script for fixing collation in your tables:
------------------------------------------------------------
-- LM_ChangeCollation - Change collation in all tables
-- made by Luis Monteiro - ljmonteiro@eurociber.pt
-- modified by wilfred van dijk - wvand@wilfredvandijk.nl
------------------------------------------------------------
DECLARE @new_collation varchar(100)
DECLARE @debug bit
DECLARE
@tablesysname,
@previoussysname,
@column varchar(60),
@typevarchar(20),
@legthvarchar(4),
@nullablevarchar(8),
@sqlvarchar(8000),
@msgvarchar(8000),
@servercollationvarchar(120)
/*
uncomment one of the following lines:
*/
set @new_collation = convert(sysname, databasepropertyex(DB_NAME(), 'collation'))
-- set @new_collation = convert(sysname, serverproperty('collation'))
/*
@debug = 0 to execute
*/
set @debug = 1
if @new_collation is null
begin
print 'which collation?'
goto einde
end
DECLARE C1 CURSOR FOR
select'Table'= b.name,
'Column'= a.name,
'Type'= type_name(a.system_type_id),
'Length'= a.max_length,
'Nullable'= case when a.is_nullable = 0 then 'NOT NULL' else ' ' end
fromsys.columns a
joinsysobjects b
ona.object_id = b.id
whereb.xtype = 'U'
andb.name not like 'dt%'
andtype_name(a.system_type_id) in ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext')
anda.[collation_name] <> @new_collation
orderby b.name,a.column_id
OPENC1
FETCHNEXT
FROMC1
INTO @table,@column,@type,@legth,@nullable
set@previous = @table
WHILE@@FETCH_STATUS = 0
BEGIN
if @table <> @previous print ''
set@sql = 'ALTER TABLE ' + QUOTENAME(@table) + ' ALTER COLUMN ' + QUOTENAME(@column) + ' '
set@sql = @sql + @type + '(' + @legth + ')' + ' COLLATE ' + @new_collation + ' ' + @nullable
print @SQL
if @debug = 0
begin
begin try
EXEC (@sql)
end try
begin catch
print 'ERROR:' + ERROR_MESSAGE()
print ''
end catch
end
set@previous = @table
FETCHNEXT
FROMC1
INTO @table,@column,@type,@legth,@nullable
END
CLOSE C1
DEALLOCATE C1
einde:
Note: You'll manually have to fix columns which are part of a key/constraint (by using management studio)
Wilfred
The best things in life are the simple things