Coallation question ?

Question

Post reply

Coallation question ?

Ajay Deshpande

Old Hand

Points: 339
More actions
August 9, 2006 at 11:56 am

#62573

Hi,
I have a ongoing task of refreshing Db with one coallation with the DB backup of someother coallation type.
The backup of DB I have is of coallation type SQL_Latin1_General_CP1_CI_AS.
I have to restore it as SQL_Latin1_General_CP1_CI_AS.
Right now I do this it manually.
By restoring the backup as SQL_Latin1_General_CP1_CI_AS. Scripting all objects and creating it on the destination DB with SQL_Latin1_General_CP1_CI_AS coallation. Transferring the data.
Is there any easier way where as I can automate this process ?

Thanks
Ajay

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Bhushan Default port Points: 1427 More actions · Answer 1

After restoring the backup change the database collation to desired one. No need to change collation for every object.

Ajay Deshpande Old Hand Points: 339 More actions · Answer 2

Ajay Deshpande

Old Hand

Points: 339

August 9, 2006 at 3:16 pm

#654176

whats the exact command to do that ?

Thanks

Ajay

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

ALTER DATABASE MY_DB COLLATE SQL_Latin1_General_CP1_CI_AS

Ajay Deshpande Old Hand Points: 339 More actions · Answer 4

Ajay Deshpande

Old Hand

Points: 339

August 10, 2006 at 5:58 am

#654268

This thing worked Thanks for your help.

Lowell SSC Guru Points: 323518 More actions · Answer 5

you must change the collation for each column in the database that is char,varchar,etc.

simply changing the collation of the database DOES NOT affect the existing schema. to prove it, do an sp_help on any table that has a varchar after you change the database collation.

this script below might help you; i've limited it to TOP 5 for demo purposes, and you'd need to change it to get all your columns; it'd be a long running query.

SELECT TOP 5

'ALTER TABLE ' + SYSOBJECTS.NAME + ' ALTER COLUMN ' + SYSCOLUMNS.NAME + ' '

+ TYPE_NAME(SYSCOLUMNS.XTYPE) + '(' + CONVERT(VARCHAR,SYSCOLUMNS.LENGTH) + ') '

+ ' COLLATE SQL_Latin1_General_CP1_CI_AS'

+ 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')

ORDER BY TBLNAME,COLNAME

SELECT TOP 5

'ALTER TABLE ' + SYSOBJECTS.NAME + ' ALTER COLUMN ' + SYSCOLUMNS.NAME + ' '

+ TYPE_NAME(SYSCOLUMNS.XTYPE)

+ ' COLLATE SQL_Latin1_General_CP1_CI_AS'

+ 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')

ORDER BY TBLNAME,COLNAME<BR

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!