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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Wrong collation
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/20/2003 :  19:24:54  Show Profile  Visit nr's Homepage  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 06/20/2003 :  19:34:31  Show Profile  Visit nr's Homepage  Reply with Quote
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
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 06/20/2003 :  20:21:07  Show Profile  Reply with Quote
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

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/21/2003 :  02:34:44  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

kelleyb
Yak Posting Veteran

USA
61 Posts

Posted - 06/23/2003 :  17:01:05  Show Profile  Visit kelleyb's Homepage  Reply with Quote
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

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/23/2003 :  17:07:54  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 06/23/2003 :  19:02:36  Show Profile  Visit robvolk's Homepage  Reply with Quote
Change the collation for model, then restart SQL Server. That ought to do it.

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/23/2003 :  19:44:28  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 06/23/2003 :  19:54:39  Show Profile  Visit robvolk's Homepage  Reply with Quote
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".

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/23/2003 :  21:03:49  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000