July 6, 2009 at 9:02 am
Hi Everyone,
Currently upgrading SQL 2000 to 2005, the problem is that the SQL 2000 instance has a collation setting which is inconsistent across databases. I am looking to make this consistent across teh board.
I have upgraded collation on the server level, but some tables have columns which have the old collation setting from SQL 2000, this is causing procs to fail.
I have a script which will tell you tables/columns which have such collation setting, but I am thinking whats the fastest way to change this, running alter fails because such columns which have indexes will require the indexes to be dropped.
This leads me to the question, is there a script somewhere which will list all Primary keys etc. or is it better to script tables, drop tables, re-create in SQL 2005 and bulk insert ?
Thanks guys.
July 6, 2009 at 9:21 am
==edit==
DOH i didn't see where you tried and failed due to indexes....sorry.
==edit==
here's a script I use to change collation;
in the example here, I'm grabbing only the top 5...you'd need to remove that.
so it checks each text/ntext/char/nchar/varchar/nvarchar column, and if it doesn't match the "target" collation, it creates the necessary ALTER TABLE ALTER COLUMN statement.
you'd need to run this on each database you want to tweak.
declare @collname varchar(128)
set @collname='SQL_Latin1_General_CP1_CI_AS'
SELECT 'ALTER DATABASE ' + db_name() + ' COLLATE ' + @collname
SELECT TOP 5
'ALTER TABLE ' + SYSOBJECTS.NAME + ' ALTER COLUMN ' + SYSCOLUMNS.NAME + ' '
+ TYPE_NAME(SYSCOLUMNS.XTYPE) + CASE WHEN SYSCOLUMNS.LENGTH = -1 THEN '(max)' ELSE '(' + CONVERT(VARCHAR,SYSCOLUMNS.LENGTH) + ') ' END
+ ' COLLATE ' + @collname
+ CASE ISNULLABLE WHEN 0 THEN ' NOT NULL' WHEN 1 THEN ' NULL' END AS ALTERSTMT,
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE,
SYSCOLUMNS.LENGTH as length
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('char','varchar', 'nchar','nvarchar')
--AND SYSCOLUMNS.COLLATION @collname
ORDER BY TBLNAME,COLNAME
SELECT TOP 5
'ALTER TABLE ' + SYSOBJECTS.NAME + ' ALTER COLUMN ' + SYSCOLUMNS.NAME + ' '
+ TYPE_NAME(SYSCOLUMNS.XTYPE)
+ ' COLLATE ' + @collname
+ CASE ISNULLABLE WHEN 0 THEN ' NOT NULL' WHEN 1 THEN ' NULL' END AS ALTERSTMT,
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE,
SYSCOLUMNS.LENGTH as length
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('text','ntext')
--AND SYSCOLUMNS.COLLATION @collname
ORDER BY TBLNAME,COLNAME
Lowell
July 6, 2009 at 9:41 am
Thanks for the info
below is the script I'm using
declare @fromCollation sysname,
@toCollation sysname
set @fromCollation = 'SQL_Latin1_General_CP1_CS_AS'
set @toCollation = 'SQL_Latin1_General_CP1_CI_AS'
SELECT
'ALTER TABLE ' + TABLE_NAME +
' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE +
CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then '(max)'
WHEN DATA_TYPE in ('text','ntext') then ''
WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ') END
+' COLLATE ' + @toCollation+ ' ' + CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
WHEN 'No' THEN 'NOT NULL'
END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')
and COLLATION_NAME like @fromCollation
Now the problem is that a lot of procs are involved in joins, since tempdb has a different collation compared to that on the table columns, its causing it to fail.
To get around the problem.
I need to drop all statistics
Drop all indexes
Drop all Primary keys as these will also be classed as clustered indexes at times.
So I'm thinking instead of all the trouble, why not simply follow the steps I mentioned in my first post and one will have a clean system free of any differences in collation.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply