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
Robert
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 Mstl" <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...
> unique
> Server?
>
>
|||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 Mstl" <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[vbcol=seagreen]
> 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:
a[vbcol=seagreen]
you[vbcol=seagreen]
Although[vbcol=seagreen]
correct[vbcol=seagreen]
with a[vbcol=seagreen]
index.[vbcol=seagreen]
case-insensitive to[vbcol=seagreen]
the[vbcol=seagreen]
was[vbcol=seagreen]
|||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...
> unique
> Server?
>
>
|||Robert M?stl wrote:[vbcol=seagreen]
> 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:
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

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 Mstl" <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 coul
d
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 y
ou
> 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 corre
ct
> 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...
> unique
> Server?
>
>|||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 Mstl" <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[vbcol=seagreen]
> 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:
>
a[vbcol=seagreen]
you[vbcol=seagreen]
Although[vbcol=seagreen]
correct[vbcol=seagreen]
with a[vbcol=seagreen]
index.[vbcol=seagreen]
case-insensitive to[vbcol=seagreen]
the[vbcol=seagreen]
was[vbcol=seagreen]|||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 y
ou
> 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 corre
ct
> 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...
> unique
> Server?
>
>|||Robert M?stl wrote:[vbcol=seagreen]
> 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:
>
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