asked on

MS sql not sensitive??

my following query

> select * from users where userid="SAnjay"

fetches user "sanjay" also where as its wrong. any set up is there to set case sensitivity off?

miron

did you try

select * from users where userid="SAnjay" COLLATE SQL_Latin1_General_CP1_CS_AS

sanjaykattimani

ASKER

select * from users where userid="SAnjay" COLLATE SQL_Latin1_General_CP1_CS_AS

generates following error

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'COLLATE'.

ASKER CERTIFIED SOLUTION

miron

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sanjaykattimani

ASKER

Thanks. it works. Is there any way to set it on so that it always compares all text with case sensitivity?

sanjaykattimani

ASKER

Thanks. it works. Is there any way to set it on so that it always compares all text with case sensitivity?

miron

Just install SQL Server 7.0 using "Custom Installation" method, pick collation SQL_Latin1_General_CP1_CS_AS as a default collation during installation process. Textual data will be always compared using case sensitive comparison on this server.
A caveat, import existing data from server with conflicting collation settings will require two steps.
-- export existing data into a flat file(s)
-- import it into new server using Transact - SQL statement BULK INSERT or bcp.exe command line utility.

Cheers