'aa'='AA' but 'aA' != 'aa' - why?

I have setup a case insensitive database that seems to work in all cases
except when dealing with the letter a. I suspect this has something to do
with the collation. The SQL code at the bottom of this message can
demonstrate the problem.
The select * from cameraassignment fails when the a in assignment is lower
case.
use master
go
CREATE DATABASE [atest]
COLLATE Danish_Norwegian_CI_AS
go
use atest
go
CREATE TABLE [dbo].[CameraAssignment] (
[CameraId] [int] IDENTITY (1, 1) NOT NULL ,
[CameraName] [nvarchar] (30) COLLATE Danish_Norwegian_CI_AS NOT NULL
)
Go
-- This will work
select * from cameraAssignment
go
-- This fails, why'
select * from cameraassignment
goIt is because the difference between the Danish alphabet and English. AA is
sometimes considered as a equivalent of an alphbet like A ( sorry I cannot
type it out, let me take 'X' instead. So, when there is AA and you use LIKE
'A%', system could take AA as X but not AA, However, if there is AAA, like
'NESAAAS', it will take AA as X and the left A will fit the creteria of the
LIKE clause. It is not a bug but some collation difference. So when run a
query, just take it into consideration, such as for a (lower case)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Raymond Glassmeyer" wrote:

> I have setup a case insensitive database that seems to work in all cases
> except when dealing with the letter a. I suspect this has something to do
> with the collation. The SQL code at the bottom of this message can
> demonstrate the problem.
> The select * from cameraassignment fails when the a in assignment is lower
> case.
> --
> use master
> go
> CREATE DATABASE [atest]
> COLLATE Danish_Norwegian_CI_AS
> go
> use atest
> go
> CREATE TABLE [dbo].[CameraAssignment] (
> [CameraId] [int] IDENTITY (1, 1) NOT NULL ,
> [CameraName] [nvarchar] (30) COLLATE Danish_Norwegian_CI_AS NOT NULL
> )
> Go
> -- This will work
> select * from cameraAssignment
> go
> -- This fails, why'
> select * from cameraassignment
> go
>|||Thanks for the reply.
Are there any other character combinations that could be an issue? In order
to get my code to work I am going to go through all the stored procedures an
d
change the SQL that uses the table CameraAssignment so that the A in
assignment is upper case. This also affects any client software calling a
stored procedure that contains a mixed case aa in the name (aA or Aa).