Case-sensitivity

Hello gurus!
I have to migrate my DB from Oracle to SQL Server 2000. There are some
unique indexes on some String columns of my DB-Schema. In Oracle the unique
index constraint concerns case-sensitivity but SQL Server does not.
So when I have to migrate the two values 'A' and 'a' into a column with a
unique index I got an SQL error concerning the uniqueness of the index.
I already successfully changed the collation mode from case-insensitive to
case-sensitive. Now the collation mode is set to case-sensitive, but the
above example still doesn't work...
Does somebody have an idea how to reach real case-sensitivity in SQL Server?
By the way the database in SQL Server is restored from a file which was
backupped from my SQL Server, when the collation mode was set to
case-insensitive.
Thanks in advance
RobertHow did you change the collation? In SQL Server, the collation of a
database is the default for objects created in that database -- but only a
default. Every object that can have a collation can have a different
collation. Every column, for instance, will maintain its own. Whether
that's the default or something else is up to you... It sounds as though you
may need to change the collation for the column you're indexing. Although
it may be better for you to start over and re-create the database with a
case-sensitive collation to begin with, so that every column has the correct
collation.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Robert Möstl" <Robert Mstl@.discussions.microsoft.com> wrote in message
news:F266E63B-97A9-4E92-8D05-3B9951BD94EB@.microsoft.com...
> Hello gurus!
> I have to migrate my DB from Oracle to SQL Server 2000. There are some
> unique indexes on some String columns of my DB-Schema. In Oracle the
unique
> index constraint concerns case-sensitivity but SQL Server does not.
> So when I have to migrate the two values 'A' and 'a' into a column with a
> unique index I got an SQL error concerning the uniqueness of the index.
> I already successfully changed the collation mode from case-insensitive to
> case-sensitive. Now the collation mode is set to case-sensitive, but the
> above example still doesn't work...
> Does somebody have an idea how to reach real case-sensitivity in SQL
Server?
> By the way the database in SQL Server is restored from a file which was
> backupped from my SQL Server, when the collation mode was set to
> case-insensitive.
> Thanks in advance
> Robert|||I changed collation with rbuildm.exe. I tried a few SQL collations which are
case-sensitive. Then I created a new database and created a new table. Now
the collation is case-sensitive. That's good.
But it's bad that now also the tablename in the FROM clause of an SQL
Statement have to be written exactly like it was created.
Example:
CREATE TABLE OBJ_STATUS (
obj_status_id int NOT NULL,
name varchar(50) NOT NULL,
note varchar(250) NULL,
)
SELECT * FROM obj_status -> this does not work
SELECT * FROM OBJ_STATUS -> this works
My problem is that I have to migrate an application which was based on
Oracle till now. In Oracle for example the tablename in the FROM clause could
be upper or lower case, it doesn't matter, and when I query a string column
it is case-sensitive.
How can I reach such an 'case-sensitivity status'?
If this is not possible it is not practicable to migrate to SQL Server.
Regards Robert
"Adam Machanic" wrote:
> How did you change the collation? In SQL Server, the collation of a
> database is the default for objects created in that database -- but only a
> default. Every object that can have a collation can have a different
> collation. Every column, for instance, will maintain its own. Whether
> that's the default or something else is up to you... It sounds as though you
> may need to change the collation for the column you're indexing. Although
> it may be better for you to start over and re-create the database with a
> case-sensitive collation to begin with, so that every column has the correct
> collation.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Robert Möstl" <Robert Mstl@.discussions.microsoft.com> wrote in message
> news:F266E63B-97A9-4E92-8D05-3B9951BD94EB@.microsoft.com...
> > Hello gurus!
> >
> > I have to migrate my DB from Oracle to SQL Server 2000. There are some
> > unique indexes on some String columns of my DB-Schema. In Oracle the
> unique
> > index constraint concerns case-sensitivity but SQL Server does not.
> >
> > So when I have to migrate the two values 'A' and 'a' into a column with a
> > unique index I got an SQL error concerning the uniqueness of the index.
> >
> > I already successfully changed the collation mode from case-insensitive to
> > case-sensitive. Now the collation mode is set to case-sensitive, but the
> > above example still doesn't work...
> >
> > Does somebody have an idea how to reach real case-sensitivity in SQL
> Server?
> > By the way the database in SQL Server is restored from a file which was
> > backupped from my SQL Server, when the collation mode was set to
> > case-insensitive.
> >
> > Thanks in advance
> >
> > Robert
>
>|||You've made your entire server case-sensitive. You can control default
collation at various levels:
server
database
column
statement
Look up the COLLATE keyword in Books Online.
You should put your server back to case-insensitive collation and control
the collation at the column level instead.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Robert Möstl" <RobertMstl@.discussions.microsoft.com> wrote in message
news:7DB932A6-846D-41CE-8F9E-44ED68A91681@.microsoft.com...
> I changed collation with rbuildm.exe. I tried a few SQL collations which
are
> case-sensitive. Then I created a new database and created a new table. Now
> the collation is case-sensitive. That's good.
> But it's bad that now also the tablename in the FROM clause of an SQL
> Statement have to be written exactly like it was created.
> Example:
> CREATE TABLE OBJ_STATUS (
> obj_status_id int NOT NULL,
> name varchar(50) NOT NULL,
> note varchar(250) NULL,
> )
> SELECT * FROM obj_status -> this does not work
> SELECT * FROM OBJ_STATUS -> this works
> My problem is that I have to migrate an application which was based on
> Oracle till now. In Oracle for example the tablename in the FROM clause
could
> be upper or lower case, it doesn't matter, and when I query a string
column
> it is case-sensitive.
> How can I reach such an 'case-sensitivity status'?
> If this is not possible it is not practicable to migrate to SQL Server.
> Regards Robert
>
> "Adam Machanic" wrote:
> > How did you change the collation? In SQL Server, the collation of a
> > database is the default for objects created in that database -- but only
a
> > default. Every object that can have a collation can have a different
> > collation. Every column, for instance, will maintain its own. Whether
> > that's the default or something else is up to you... It sounds as though
you
> > may need to change the collation for the column you're indexing.
Although
> > it may be better for you to start over and re-create the database with a
> > case-sensitive collation to begin with, so that every column has the
correct
> > collation.
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.datamanipulation.net
> > --
> >
> >
> > "Robert Möstl" <Robert Mstl@.discussions.microsoft.com> wrote in message
> > news:F266E63B-97A9-4E92-8D05-3B9951BD94EB@.microsoft.com...
> > > Hello gurus!
> > >
> > > I have to migrate my DB from Oracle to SQL Server 2000. There are some
> > > unique indexes on some String columns of my DB-Schema. In Oracle the
> > unique
> > > index constraint concerns case-sensitivity but SQL Server does not.
> > >
> > > So when I have to migrate the two values 'A' and 'a' into a column
with a
> > > unique index I got an SQL error concerning the uniqueness of the
index.
> > >
> > > I already successfully changed the collation mode from
case-insensitive to
> > > case-sensitive. Now the collation mode is set to case-sensitive, but
the
> > > above example still doesn't work...
> > >
> > > Does somebody have an idea how to reach real case-sensitivity in SQL
> > Server?
> > > By the way the database in SQL Server is restored from a file which
was
> > > backupped from my SQL Server, when the collation mode was set to
> > > case-insensitive.
> > >
> > > Thanks in advance
> > >
> > > Robert
> >
> >
> >|||After I changed collation mode to case-sensitive with rebuildm.exe I
generated a new database. Then I created a new table. Now when I want to
query this table the tablename in the FROM clause have to be written just
like it was in the CREATE TABLE statement. Queries on a string column are no
case-sensitive.
My problem is that I have to migrate an existing application based on
Oracle. There the tablenames in the SQL statements are written in upper case
or lower case. In Oracle it doesn't matter.
How can I reach in SQL Server that I can write tablenames in upper or lower
case but although the queries of string columns are case-sensitive?
If this is not possible, I have to cancel this migration project.
Regards,
Robert
"Adam Machanic" wrote:
> How did you change the collation? In SQL Server, the collation of a
> database is the default for objects created in that database -- but only a
> default. Every object that can have a collation can have a different
> collation. Every column, for instance, will maintain its own. Whether
> that's the default or something else is up to you... It sounds as though you
> may need to change the collation for the column you're indexing. Although
> it may be better for you to start over and re-create the database with a
> case-sensitive collation to begin with, so that every column has the correct
> collation.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Robert Möstl" <Robert Mstl@.discussions.microsoft.com> wrote in message
> news:F266E63B-97A9-4E92-8D05-3B9951BD94EB@.microsoft.com...
> > Hello gurus!
> >
> > I have to migrate my DB from Oracle to SQL Server 2000. There are some
> > unique indexes on some String columns of my DB-Schema. In Oracle the
> unique
> > index constraint concerns case-sensitivity but SQL Server does not.
> >
> > So when I have to migrate the two values 'A' and 'a' into a column with a
> > unique index I got an SQL error concerning the uniqueness of the index.
> >
> > I already successfully changed the collation mode from case-insensitive to
> > case-sensitive. Now the collation mode is set to case-sensitive, but the
> > above example still doesn't work...
> >
> > Does somebody have an idea how to reach real case-sensitivity in SQL
> Server?
> > By the way the database in SQL Server is restored from a file which was
> > backupped from my SQL Server, when the collation mode was set to
> > case-insensitive.
> >
> > Thanks in advance
> >
> > Robert
>
>|||Robert Möstl wrote:
> After I changed collation mode to case-sensitive with rebuildm.exe I
> generated a new database. Then I created a new table. Now when I want
> to query this table the tablename in the FROM clause have to be
> written just like it was in the CREATE TABLE statement. Queries on a
> string column are no case-sensitive.
> My problem is that I have to migrate an existing application based on
> Oracle. There the tablenames in the SQL statements are written in
> upper case or lower case. In Oracle it doesn't matter.
> How can I reach in SQL Server that I can write tablenames in upper or
> lower case but although the queries of string columns are
> case-sensitive?
> If this is not possible, I have to cancel this migration project.
> Regards,
> Robert
> "Adam Machanic" wrote:
>> How did you change the collation? In SQL Server, the collation of a
>> database is the default for objects created in that database -- but
>> only a default. Every object that can have a collation can have a
>> different collation. Every column, for instance, will maintain its
>> own. Whether that's the default or something else is up to you...
>> It sounds as though you may need to change the collation for the
>> column you're indexing. Although it may be better for you to start
>> over and re-create the database with a case-sensitive collation to
>> begin with, so that every column has the correct collation.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Robert Möstl" <Robert Mstl@.discussions.microsoft.com> wrote in
>> message news:F266E63B-97A9-4E92-8D05-3B9951BD94EB@.microsoft.com...
>> Hello gurus!
>> I have to migrate my DB from Oracle to SQL Server 2000. There are
>> some unique indexes on some String columns of my DB-Schema. In
>> Oracle the unique index constraint concerns case-sensitivity but
>> SQL Server does not.
>> So when I have to migrate the two values 'A' and 'a' into a column
>> with a unique index I got an SQL error concerning the uniqueness of
>> the index.
>> I already successfully changed the collation mode from
>> case-insensitive to case-sensitive. Now the collation mode is set
>> to case-sensitive, but the above example still doesn't work...
>> Does somebody have an idea how to reach real case-sensitivity in
>> SQL Server? By the way the database in SQL Server is restored from
>> a file which was backupped from my SQL Server, when the collation
>> mode was set to case-insensitive.
>> Thanks in advance
>> Robert
Not sure what collation you used, but when I select
Collation=SQL_Latin1_General_CP1_CS_AS both my table names and column
names are case-sensitive. If you're talking about string comparisons
then they will only match when there's an exact match:
For example:
create table testing (col1 nvarchar(50))
go
insert into testing values (N'ABC123')
insert into testing values (N'aBC123')
insert into testing values (N'ABC')
insert into testing values (N'abc')
select * from testing
go
select * from testing where col1 = N'ABC' -- matches one row
select * from testing where col1 = N'abc' -- matches one row
select * from testing where col1 LIKE N'A%' -- matches two rows
Drop table testing
BTW, you didn't need to rebuild the master database. You could have just
created a new database using the collation you want. Which one are you
using?
David Gugick
Quest Software
www.imceda.com
www.quest.com