Here's a little snippet of SQL to help resolve collation
conflicts. You could use it to analyze all the tables on your SQL
server and it will attempt to change the collation of any text fields
to the default collation.
In the current state it doesn't
actually change anything, it simply returns a table of sql command. I
wrote a simple app which will do the sql commands as a batchfile - you
can copy and paste the data from the Query into this SqlBatchRunner.
Here's the SQL:
set nocount on
declare @defaultCollation varchar(256)
set @defaultCollation =cast(serverproperty('collation') as varchar(256))
declare @dbName varchar(256)
declare @fieldName varchar(256)
declare @tableName varchar(256)
declare @fieldType varchar(256)
declare @fieldSize varchar(10)
declare @sql nvarchar(1000)
declare c cursor for
select name
from master..sysdatabases
where name in ('tennis', 'prods', 'media')
open c
create table #fields (tableName varchar(256),
fieldname varchar(256),
databaseName varchar(256),
collation varchar(256),
fieldSize varchar(10),
fieldTypeName varchar(256))
fetch next from c into @dbName
while @@Fetch_status =0
begin
set @sql = 'insert into #fields select so.Name as tableName, sc.name as fieldname, ''' + @dbName + ''' as DatabaseName, sc.collation, sc.length, st.name as fieldName
from ' + @dbName + '..syscolumns sc inner join ' + @dbName + '..sysobjects so
on so.id = sc.id
inner join ' + @dbName + '..systypes st on sc.xtype = st.xtype
where sc.xtype in (175, 239, 99, 231, 35, 167)
and so.xtype like ''U''
and sc.collation not like ''' + @defaultCollation + '''
and (select count(*) from ' + @dbName + '..sysindexkeys where colid = sc.colid) =0
'
exec sp_ExecuteSql @sql
fetch next from c into @dbName
end
close c
deallocate c
create table #SqlCommands (sql nvarchar(1000), result int)
declare d cursor for
select tableName, fieldName, databaseName, fieldTypeName, str(fieldSize) from #fields
open d
fetch next from d into @tableName, @fieldName, @dbName, @fieldType, @fieldSize
while @@fetch_status = 0
begin
insert into #SqlCommands values ('alter table ' + @dbName + '..' + @tableName + ' alter column [' + @fieldName + ']' + @fieldType + '(' + @fieldSize + ') collate ' + cast(serverproperty('collation') as varchar(256)), '')
fetch next from d into @tableName, @fieldName, @dbName, @fieldType, @fieldSize
end
close d
deallocate d
declare @errVal int
declare e cursor for select sql, result from #SqlCommands for Update of result
open e
fetch next from e into @sql, @errVal
while @@fetch_status =0
begin
print @sql
--exec @errVal = sp_executesql @sql
update #SqlCommands set result = @@Error where current of e
fetch next from e into @sql, @errVal
end
close e
deallocate e
select * from #SqlCommands
drop table #SqlCommands
drop table #fields
And you can download the source code for the SqlBatchRunner.