Confusing Collation - SQL Server Forums

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 2000 Forums

SQL Server Administration (2000)

Confusing Collation

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-12-08 : 03:33:42

If I remember correctly the collation settings for a server are set during installation. My question is, if you weren't there at the time how do you find out what your collation settings are?

I'm trying to run a compare on two databases on two seperate servers (using SQL compare) and am getting an error which is apparently due to collation. Having seen what it says in BOL about changing the collation setting I'm a bit reluctant to go through that but think I will have to

thanks

steve

To alcohol ! The cause of - and solution to - all of life's problems

Argyle
Yak Posting Veteran

53 Posts

Posted - 2004-12-08 : 03:57:33

You could use any of the following for server collation (system databases):
sp_server_info
sp_helpsort

or the following for database collation (or just right click and check properties)
sp_helpdb

On SQL 7 all user databases have the same collation as the system databases, on SQL 2000 they can be different.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-12-08 : 04:23:45

Many thanks

steve

To alcohol ! The cause of - and solution to - all of life's problems

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-08 : 09:35:08

[code]select serverproperty('collation') as server_collation

select name as db, databasepropertyex(name,'collation') as db_collation
from master.dbo.sysdatabases[/code]

rockmoose

Kristen
Test

22859 Posts

Posted - 2004-12-08 : 16:58:01

[code]
SELECT serverproperty('collation')

-- Change Collation for a database.
-- NOTE: This ONLY effects NEWLY created columns
ALTER DATABASE MyDataBase COLLATE SQL_Latin1_General_CP1_CI_AS

-- Script changing of columns to Standard COLLATing sequence

-- This will NOT work for columns which have dependacies

SELECT
'PRINT ''' + T.name + '.' + C.name + '''',
CHAR(13)+CHAR(10)+'GO',
CHAR(13)+CHAR(10)+'ALTER TABLE '
+ T.name
+ ' ALTER COLUMN '
+ C.name
+ ' '
+ TY.name
+ CASE WHEN TY.name in ('nchar', 'ntext', 'nvarchar', 'sysname', 'text', 'varchar')
THEN '(' + CONVERT(varchar(20), C.length) + ')'
ELSE ''
END
+ ' COLLATE SQL_Latin1_General_CP1_CI_AS '
+ CASE WHEN C.isnullable = 0 THEN 'NOT ' ELSE '' END
+ 'NULL',
'--',
T.name,
C.name,
TY.name,
C.length,
-- TY.*,
C.*,
CHAR(13)+CHAR(10)+'GO',
CHAR(13)+CHAR(10)+'-----'
FROM syscolumns C
JOIN sysobjects T
ON T.ID = C.ID
AND T.type = 'U'
JOIN systypes TY
ON TY.xtype = C.xtype
WHERE TY.name in ('nchar', 'ntext', 'nvarchar', 'sysname', 'text', 'varchar')
AND C.collation <> 'SQL_Latin1_General_CP1_CI_AS'
-- AND T.name like 'MyTableLike%' -- Specific tables only
ORDER BY T.name, C.colid
[/code]
Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources