Case insensitivity problem

I have an application that needs to check users' log on credentials from a
web front end.
The web front end passes the user name and password to a stored procedure
and, if the stored procedure finds someone with those credentials then it
returns the user's ID.
Trouble is that SQLServer has been installed to be case insensitive, so
"password" = "PASSWORD"
Is there anything that I can do in the stored procedure that can make the
select statement case sensitive for this particular query?
Thanks
Griff> Is there anything that I can do in the stored procedure that can make the
> select statement case sensitive for this particular query?
Of course.
http://www.aspfaq.com/2152|||http://vyaskn.tripod.com/case_sensi..._sql_server.htm
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Griff" <Howling@.The.Moon> wrote in message
news:OS5efhMqFHA.1556@.TK2MSFTNGP12.phx.gbl...
> I have an application that needs to check users' log on credentials from a
> web front end.
> The web front end passes the user name and password to a stored procedure
> and, if the stored procedure finds someone with those credentials then it
> returns the user's ID.
> Trouble is that SQLServer has been installed to be case insensitive, so
> "password" = "PASSWORD"
> Is there anything that I can do in the stored procedure that can make the
> select statement case sensitive for this particular query?
> Thanks
> Griff
>|||Try to convert them to a binary and then compare...
Marcel van Eijkel
( www.vaneijkel.com )sql

Case Insensitivity is on server wide: tables render case sensative...

Hello:

I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?

Any suggestions?Have a look at "Using SQL Collations" in SQL Server Books Online|||Hello:

I went ahead and took a look inside books online and pretty much found things that I already know. My current collation is: SQL_Latin1_General_CP1_CI_AI.. So the server is actually CaSe insensative... But some of my tables still act like case sensitivity is turned on in most areas where I have not done anything on my part to cause this behavior. So I am guessing that since the tables were moved from a CS_AS to a CI_AI, the tables caused were rendered case sensative although it is not in a CS_AS enviornment... My question is that would I need to rebuild the database manually from scratch or is their an easier way to do this? Thank you.|||If the AS collations appear only on a few tables you can alter those tables only - something like:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_819v.asp|||Hello:

Yes I followed that link it was more helpful... looks like a daunting manual task.. thank you

case insensitivity

here is simple query
SELECT * FROM BFTITLE WHERE RFNAME='HITLER';
but if i want to select hitler LOWER leave case this query is not valid so how can we right query to select all type of hitler(case type)sql server is by default case insensitive. if someone has changed the default collation, you can use the UPPER and LOWER functions.|||well what you can do is change your where clause by changing the search type to either upper or lower....something like

SELECT * FROM BFTITLE WHERE upper(RFNAME)=upper('HITLER');
OR
SELECT * FROM BFTITLE WHERE lower(RFNAME)=lower('HITLER');

Case Insensitivity

I have a SQL 2000 database. I have a ASP.NET web app that I use to
search this database. I need to make my data case insensitive,
espcially my last name column. How do I change this?

Thanks,
BrianI was doing some further reading and I am hearing that you set case
sensitivity when you first install SQL by choosing an ANSI set and the
only way to change this is to re-install SQL. Is this correct? There
has to be another way around this...|||See "Specifying Collations" and "Collation Precedence" in Books
Online. You can change the collation at the database or column level
(see ALTER DATABASE and ALTER TABLE), or in your queries (see COLLATE).

Personally, I would modify the queries (or perhaps create a view)
rather than have one or two columns in a database in a different
collation from the rest.

Simon|||There is another way in SQL2000. Collation is determined at column
level so you can alter the case-sensitivity and other collation
properties at any time. For example:

ALTER TABLE YourTable
ALTER COLUMN last_name VARCHAR(50)
COLLATE Latin1_General_CI_AS

Read the Collations topics in Books Online to understand the collation
syntax and how this affects comparisons between columns of different
collation.

--
David Portas
SQL Server MVP
--|||I used your syntax and everything works like a charm except for one
thing, now when I do a search, such as "W" in the lastname field, it
pulls every records that contains a "W" in the last name, rather than
names that start with "W". How do you correct this? it needs to search
from left to right.

Thanks,
Brian|||What's the SQL statement you are using to SELECT? It sounds like
you're putting a wildcard in front of and behind the character you are
searching on, e.g.:

SELECT ColName
FROM Table
WHERE ColName LIKE '%W%'

when it sounds like you want the wildcard after

SELECT ColName
FROM Table
WHERE ColName LIKE 'W%'

Your collation settings should only affect the case sensity of the
database; not how your LIKE comparisons perform. Am I
misunderstanding?

Stu