Collation and Data Corruption? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Collation and Data Corruption?

Author

Topic

icewizard
Starting Member

2 Posts

Posted - 2008-04-07 : 12:18:56

Hello,
I'm not so new on SQL Server 2005 but i'd rather be sure on this.

I recently dumped an SQL Server 2000 database (French_CI_AS collation) and loaded it into an SQL Server 2005 instance (Latin1_General_CI_AS collation). To make things easier, we'll call this database CLIENTBOOKS. Of course CLIENTBOOKS as well as all the text columns (char, nchar, varchar, nvarchar, text, ntext) kept their original collation French_CI_AS. So I changed the database collation first using:

ALTER DATABASE clientbooks COLLATE Latin1_General_CI_AS
GO

Then after that i dropped all indexes concerned in these tables (there were only 4), i changed all table columns' collations using the following script (except for tables dbo.dtproperties and dbo.sysdiagrams):

DECLARE @@TableName NVARCHAR(100)
DECLARE @@ColumnName NVARCHAR(100)
DECLARE @@ColumnType NVARCHAR(100)
DECLARE @@ColumnLengh FLOAT
DECLARE @@SQL NVARCHAR(1000)
DECLARE @@IsNullAble NVARCHAR(50)

PRINT 'ALTERING TABLE COLUMNS...'

DECLARE my_cursor CURSOR FOR
SELECT sysobjects.name FROM sysobjects WHERE xtype='u' AND sysobjects.name <> 'sysdiagrams' AND sysobjects.name <> 'dtproperties'
OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @@TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE my_column CURSOR FOR
SELECT syscolumns.name,systypes.name AS TYPE, syscolumns.length ,syscolumns.isnullable
FROM syscolumns INNER JOIN sysobjects ON sysobjects.id=syscolumns.id
LEFT OUTER JOIN sys.extended_properties ON sys.extended_properties.minor_id=syscolumns.colid AND sys.extended_properties.major_id = syscolumns.id
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
WHERE sysobjects.xtype='u' AND sysobjects.name=@@TableName AND (systypes.name='TEXT' OR systypes.name='NTEXT' OR systypes.name='NVARCHAR' OR systypes.name='VARCHAR' OR systypes.name='CHAR' OR systypes.name='NCHAR')

OPEN my_column
FETCH NEXT FROM my_column INTO @@ColumnName,@@ColumnType,@@ColumnLengh,@@IsNullAble

WHILE @@FETCH_STATUS = 0
BEGIN
IF (@@IsNullAble=1)
BEGIN
SELECT @@SQL =( 'ALTER TABLE ' + @@TableName + ' ALTER COLUMN ' + @@ColumnName + ' ' + @@ColumnType + '(' + CAST(@@ColumnLengh AS
NVARCHAR) + ') COLLATE ' + ' Latin1_General_CI_AS NULL')
END
ELSE
BEGIN
SELECT @@SQL =( 'ALTER TABLE ' + @@TableName + ' ALTER COLUMN ' + @@ColumnName + ' ' +
@@ColumnType + '(' + CAST(@@ColumnLengh AS NVARCHAR) + ') COLLATE ' + ' Latin1_General_CI_AS NOT NULL')
END

PRINT(@@SQL)
EXEC(@@SQL)

FETCH NEXT FROM my_column INTO @@ColumnName,@@ColumnType,@@ColumnLengh,@@IsNullAble
END
CLOSE my_column
DEALLOCATE my_column

FETCH NEXT FROM my_cursor INTO @@TableName
END
CLOSE my_cursor
DEALLOCATE my_cursor

PRINT 'ALTERATION DONE'
GO

Once i did that, i re-created the 4 indexes again (i generated the create scripts before dropping them).

Can i be sure now that the textual data (char, nchar, varchar, nvarchar, text, ntext) will not be corrupted? Will it be like if i recreated a new database with the correct collation, as well as the necessary tables, and inserted the data into the tables?

Thank you for reading downto here ;)

Take care all :)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-07 : 12:21:06

Try it and see

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-07 : 12:22:16

Run DBCC CheckDB

icewizard
Starting Member

2 Posts

Posted - 2008-04-08 : 04:00:22

Well i ran it and there were no errors. So i should expect it to be exactly the same?

Here's the output: (except for the user tables where i put generic data).

DBCC results for 'clientbooks'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
DBCC results for 'sys.sysrowsetcolumns'.
There are 882 rows in 8 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 100 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 114 rows in 2 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 882 rows in 9 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 100 rows in 1 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 114 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 143 rows in 2 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 334 rows in 2 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 163 rows in 3 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 1699 rows in 21 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 9 rows in 1 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 27 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 228 rows in 8 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 339 rows in 4 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysobjvalues'.
There are 305 rows in 108 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 139 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 269 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 1025 rows in 10 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysqnames'.
There are 92 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 93 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 97 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 17 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 0 rows in 0 pages for object "sys.sysbinsubobjs".
DBCC results for '#####'
There are xxxx rows in yyyy pages for object "#####".
DBCC results for 'sysdiagrams'.
There are 0 rows in 0 pages for object "sysdiagrams".
DBCC results for 'dtproperties'.
There are 0 rows in 0 pages for object "dtproperties".
DBCC results for 'sys.queue_messages_2125302681'.
There are 0 rows in 0 pages for object "sys.queue_messages_2125302681".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'clientbooks'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-08 : 09:21:58

Then you should be good.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources