Code Page for installation ?? Please help

Question

Code Page for installation ?? Please help

Helmut Nielsen

SSC Eights!

Points: 816
More actions
February 23, 2007 at 3:02 am

#194346

Hi,
I have a SQL server standard edition that I just installed. I want the collation after installation to read :
SQL_Latin1_General_CP1_CI_AI , but it shows
SQL_Latin1_General_CI_AI......
My question is how in earth I can set the "CP1" during installation ? Can I change this after installation. NOTE, I want to set this for the complete installation, it will not be enough to change it only on the different database levels

Help extremelly appreciated !!
//Helmut

Viewing 2 posts - 1 through 1 (of 1 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

changing collation can be a big project because it's in so many places:

at the server level, at the database level, and separately for each column of type text/ntext/char/nchar/varchar/nvarchar/

here's a script that sets prints the statements for one server and the current database;

you'd need to repeat the lower portion of the code for each db you want to change. note it is using top 5 right now, as the resultset can be huge, and this is for example purposes.

DECLARE @DESIREDCOLLATION VARCHAR(60)

SET @DESIREDCOLLATION = ' SQL_Latin1_General_CP1_CI_AS'

SELECT 'EXEC sp_serveroption ''' + @@SERVERNAME +''', ''collation name'',' + @DESIREDCOLLATION + ''''

SELECT 'ALTER DATABASE ' + upper(db_name()) + ' COLLATE ' + @DESIREDCOLLATION

SELECT TOP 5

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

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

+ ' COLLATE ' + ' @DESIREDCOLLATION'

+ 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 '+ ' @DESIREDCOLLATION'