December 26, 2005 at 7:47 am
I need to convert a database from InterBase 6 to MSDE 2000. The source database makes extensive use of UDTs and character set ISO8859_1 (which is case sensitive). However, if I set the database collation to Latin1_General_CS_AS then the column names and table names are also case sensitive in any SQL statements (which causes issues elsewhere in the project). On the other hand, if I want to set the collation of individual columns instead then there doesn't seem to be a way of setting the collation of a UDT or of a column that is defined by a UDT.
To summarise: I want column and table names to be case insensative, I want character data in those columns to be case sensitive. How do I do that? Any ideas?
Regards PLST
December 27, 2005 at 2:58 pm
You can set the column level collation to case sensitive on the appropriate columns without affecting table and column names, i.e.,
alter table <table_name>
alter column <column_name> [varchar] (50) COLLATE Latin1_General_CS_AS NOT NULL
You could also leave the columns alone and use "COLLATE Latin1_General_CS_AS" every time you evaluate one of the columns that should be case sensitive, i.e.,
join on <column1> = <column2> COLLATE Latin1_General_CI_AS
or this
where <column> = 'value' COLLATE Latin1_General_CI_AS
December 27, 2005 at 6:15 pm
Thanks Ken, but I can't set the collation of a column if that column is a UDT.
Regards PLST
December 29, 2005 at 2:42 pm
since sp_addtype just uses the default collation for the UDT's base datatype, after calling the procedure to create your type, you could manually change the collation for the UDT in the systypes table. You'll need to change the collation before using the UDT in a table or that table may not pick up the new collation.
Turn on allow system table edits and try this demo query (I don't suppose it's the "official" way of doing this but, what the hey!)...
USE tempdb
EXEC sp_addtype tryThis, 'VARCHAR(9)', 'NOT NULL'
GO
create table deleteMe2(
pk int not null primary key,
data tryThis)
insert deleteMe2 (pk, data) values (1, 'gotcha')
select * from deleteMe2 where data = 'gotcha'
select * from deleteMe2 where data = 'Gotcha'
drop table deleteMe2
GO
update systypes
set collationid = 49160
where name = 'tryThis'
GO
create table deleteMe2(
pk int not null primary key,
data tryThis)
insert deleteMe2 (pk, data) values (1, 'gotcha')
select * from deleteMe2 where data = 'gotcha'
select * from deleteMe2 where data = 'Gotcha'
drop table deleteMe2
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply