Wrong 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 Development (2000)

Wrong collation

Author

Topic

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-20 : 19:24:54

Background.
There is a remote Oracle system with an application on it - lots of data going back several years updated daily.
There is a sql server system which gets (pulls) daily updates from the Oracle system to keep copies of thge tables up to date. Also gets some data from other places.
The sql server is mainly a reporting system but allows users to do some updates.

This is the system I inherited (actually I implemented the daily updates rather than dragging accross the data every time a report is run).

The problem is that the Oracle server is case sensitive buit the sql server is case insensitive (I pointed this out on my first day and am now feeling damn smug). This usually doesn't matter but there are a few places where it causes problems.

Everyone has now agreed I am right (and insufferable) and we have to change it. Of course lots of things have been written case insensitive - and we can't change the server.

I'll put my thoughts in the next post.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-20 : 19:34:31

I'm thinking of creating a new database - case sensitive.
Migrating the data into it and creating views in the old database to access the data.
The views will be in a CI database so the table names and field names won't cause a problem. The data will be in a CS database so that the keys and joins will still work.

The good thing is that I can move the data across at my leisure (probably this weekend!).

Only problem I see is that temp tables created from the CI database will be CI and can't join to the CS tables without a collate statement (default_database in the CS one named in the CI one).

I have tried this stuff and it all seems ok apart from the temp table
CS
create table a (s varchar(20))
insert a select 'aaaa'
insert a select 'AAAA'
go
create table b (s varchar(20))
insert b select 'aaaa'
insert b select 'AAAA'

CI
create view A
as
select * from TestCollation..a
go
create view b
as
select * from TestCollation..b
go
select * from A
where S = 'aaaa'
select * from a
where S = 'aaaA'

create table #a (s varchar(20))
insert #a select * from a
select * from a, #a where a.s = #a.s --********* fails
select * from a, #a where a.s = #a.s collate SQL_Latin1_General_CP1_CS_AS

select s
from A
group by s

select distinct s
from A
group by s

select *
from a,b
where a.s = b.s

select * from a, (select * from b) b where a.s = b.s

Any thoughts anyone?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 06/20/2003 19:45:14

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-20 : 20:21:07

My thoughts: I'd always wondered if Nigel would actually post a question on this forum. I thought you only had answers.

Sounds like you don't want to mess with the existing SQL database (too much usage)? I'm wondering why you don't scrap it and write it anew with case sensitive compares (VARBINARY)?

You mention the new database will be case-sensitive but leave me assuming that it will be oracle?

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-21 : 02:34:44

Thanks Sam.
Nope - sticking with sql server (although I am an Oracle expert now

). The two databases will be on sql server one CS with all the data and one CI with all the views. Eventually the CI database will be dropped and the system databases rebuilt - but the environment isn't controlled enough (I'm retired and mellow so I allow it) to convert everything at once - embedded sql in apps, dts packages - all things I hate - roll up, see the cursors.

Realised next weekend is the period end run and we're getting a backup server soon (that's to take backups - don't go there) so I'll delay things a while.

p.s. can use collate for compares so don't need to convert to binary any more.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kelleyb
Yak Posting Veteran

61 Posts

Posted - 2003-06-23 : 17:01:05

Did I just witness history? A pseudo-question from Nigel. :) I hope your proposed solution works, but I'm curious if many in this group could have answered anyway.

-Brian

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-23 : 17:07:54

I noticed a question today on the ms forum (p.s. don't try it or bother looking for the "official method").

Is there any way to change the collation of the tempdb database without
rebuilding the master database?
My problem is that I use temporary tables in a database that does not have
the same collation os tempdb.
If I try this:
ALTER DATABASE tempdb
COLLATE Danish_norwegian_CI_AS
SQL Server 2000 return the error:
"Cannot alter the database 'tempdb' because it is a system database."
Off course I might hack the master database system tables, but I would
really prefer an "official" method

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-06-23 : 19:02:36

Change the collation for model, then restart SQL Server. That ought to do it.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-23 : 19:44:28

And have master and msdb (and model) with a different collation to tempdb?

Had a quick look for something that might cause the agent trouble - can't find it but I'll let someone else test I think.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-06-23 : 19:54:39

I'm not sure why there'd be a problem in changing the collation in model, but if you really didn't want to do it:

quote:
Creating or altering a table column.

You can specify collations for each character string column using the COLLATE clause of the CREATE TABLE or ALTER TABLE statement. You can also specify a collation when you create a table using SQL Server Enterprise Manager. If you do not specify a collation, the column is assigned the default collation of the database.

You can also use the database_default option in the COLLATE clause to specify that a column in a temporary table use the collation default of the current user database for the connection instead of tempdb.

From BOL, under "COLLATE".

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-23 : 21:03:49

I'm planning to use database collation for temp tables created from the new CS database and a named collation for those from the CI database.

Not sure that all the system stored procs are built to allow for tempdb being a different collation to the system DBs - will probably be OK for user SPs as these can be called from any datbase context - but those which are only expected to be used by the system may have problems.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources