October 25, 2004 at 9:44 pm
So, here we are upgrading (finally) to SQL 2k, and I found this article - http://www.sqlteam.com/item.asp?ItemID=9066, which was very helpful and got me started in the right direction. However, not being a MSSQL expert (too many hats to master it), I wasn't too aware of collations. Well, it's come back to bite me! Here's the thing - every database I bring over has a different collation than the new server. I can (fairly) easily change the database once it's come over, but that's not enough.
Here's the question: How do I programmatically change all the collations for all the objects within each table within each database? They don't all need it, but I can't do it by hand (it'd take weeks!).
Any thoughts (please!?)?
TIA
October 25, 2004 at 10:07 pm
Check out this thread for additional info:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=139473
On the surface, programmatically changing collation orders doesn't seem too difficult a concept, until you realise that you need to drop associated constraints/indexes etc before changing the collation and then recreate them. I have not seen a script that can do this well.
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 26, 2004 at 2:50 pm
Uugh. Nothing like a SMOOOOTH upgrade...
How about a script that lists the issue-laden columns?
October 27, 2004 at 2:13 am
You could EM to script all the tables for each SQL2K database which will give you column collations which you can then analyze.
Far away is close at hand in the images of elsewhere.
Anon.
October 27, 2004 at 6:02 am
To id the collation issues try the script below. (if this doesn't appears ok post a reply with you email address and I'll email you the script).
Regards,
Stu
--===============================--
use master
go
if exists (select name from sysobjects where name = 'sp_HelpCollation' and type = 'P')
begin
drop proc sp_HelpCollation
end
go
create proc sp_HelpCollation
@mode varchar(20) = null
as
/***********************************************************************************************************************************************
*
*
* Name: sp_HelpCollation
*
* Author: SB
* Date: 2002-07-01
*
* Purpose: Sproc to provide collation info
*
* Revision History
* ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
* | Revision | Date | Changed By | Comments
* ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
* | 0.1 | 2002-07-01 | SB | Initial Release
*
*
*
**************************************************************************************************************************************************/
begin
set nocount on
--help text
if @mode is null
begin
print 'Usage: sp_HelpCollation [''default''|''verbose''|''local''|''local_all'']'
end
--default = default server level collation
if @mode = 'Default'
begin
select 'Default Database Collation : ' +
convert(varchar(100),databasepropertyex('tempdb','collation')) +
char(10) +
'All databases will be created with this collation by default' +
char(10) +
'Tempdb uses this collation for all global and local hash table columns unless specified otherwise.'
end
--local = local database collation
if @mode = 'local'
begin
select 'Database Collation for ' + db_name() + ' : ' + convert(varchar(100),databasepropertyex(db_name(),'collation'))
end
--local_all = local database and column level collation
if @mode = 'local_all'
begin
select 'Database Collation for ' + db_name() + ' : ' + convert(varchar(100),databasepropertyex(db_name(),'collation'))
print 'Column level collations in ' + db_name()
select distinct object_name(sc.id) as TableName,
sc.name as ColumnName,
sc.collation as Collation
from syscolumns sc
inner join sysobjects so
on (so.id = sc.id)
where so.xtype = 'u'
and sc.collation is not null
end
--verbose = collation at all levels
if @mode = 'verbose'
begin
select 'Default Database Collation : ' +
convert(varchar(100),databasepropertyex('tempdb','collation'))+
char(10) +
'All databases will be created with this collation by default.' +
char(10) +
'Tempdb uses this collation for all global and local hash table columns unless specified otherwise.'
select 'Database Collation for ' + db_name() + ' : ' + convert(varchar(100),databasepropertyex(db_name(),'collation'))
print 'Column level collations in ' + db_name()
select distinct object_name(sc.id) as TableName,
sc.name as ColumnName,
sc.collation as Collation
from syscolumns sc
inner join sysobjects so
on (so.id = sc.id)
where so.xtype = 'u'
and sc.collation is not null
end
end
go
grant exec on sp_HelpCollation to public
go
exec sp_Ms_MarkSystemObject sp_HelpCollation
go
November 14, 2004 at 11:27 am
Thanks for the assist, all. The problem in my case is being cause by creation and use of temp tables. Because the newly attached databases have a different collation than the tempdb table, and their stored procs use 'create table #temp' actions, the two won't talk well. The solution (for me) is to simply re-write the relevant temp creation routines. Fortunately, I've found the wonder of UDF's; did you know that you can define variables as "tables"!? It really makes this all a lot easier.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply