SQL Server Performance Forum – Threads Archive

Case Sensitive Fields

Hi All, Facing a problem. Hopes someone can help me out. I am a VB6 developer. In my development machine, when I am using the SQL table fields, it runs smoothly wihthout cheking the case-sentiveness of the fields name.
For Eg :- " select PriceList from SupplierPriceList "
AND
" select pricelist from SupplierPriceList "
makes no difference. But when I am copying the program to the client machine it gives an error messages. So I prefer to adjust the development machine case sensitive settings accordingly so that, I can eliminate such errors from the beggining itself. Pls advise what should I do for the same ? Thanks in advance
Raj

---

don’t seem to have problem with my VB6 application. what error messages you got?

---

"Invalid column name" is the error message Tx
Raj

---

Make sure you have spelled it correctly Madhivanan Failing to plan is Planning to fail

---

Hm, in VB you’re probably interfacing through ADO, right? Ever checked the immense amount of information you can retrieve going through the object? Information such as column names, column properties, etc. etc.?

---

Its not the problem with spelling. But my doubt is why it is working in one machine and not in the other ? As i wrote "select Pricelist " Or "select PriceList" is working in one machine.
But in the other machine I forced to type " select PriceList" which is exactly the column name. Tx
Raj

---

As far as I know, database object names are case-insensitive in all SQL standards that I’m aware of. Perhaps you have different objects with the same name, but for different owners? In that case, and if you’re not telling SQL which object you mean, it may pick a table where perhaps the column name is different from the one you think you’re referring to.

---

AFAIK, database object names are NOT case-sensitive. Can you execute this query in Query Analyser and let us know? —————————————-
http://spaces.msn.com/members/dineshasanka

---

Hi dineshasanka, I tried it in Query Analyiser. The error comes and the error message is :- " Server : MSG 207, Level 16, State 3, Line 1
‘Invalild Column Name’ " And I tried the same query through SQL Enterprise Manager :- Here the column name was automatically changed by the server
( I mean I entered "Pricelist", but it was changed as "PriceList" by the server itself) and the result came without any error. Also as per the experts below comments, I tried all other possible tests like double checking the spelling, adding the owner name and table name before the column name etc. But still the problem exists. I hope there is a way to attach the screen dump here so that everyone can see also the problems. Tx.
Raj

---

You can try this in order to change the case sensitiveness to case insensitiveness. ALTER database <database name>
collate SQL_Latin1_General_CP1_CI_AS If you are interested you can execute the below function to look at various collate options for case insensitiveness. SELECT *
FROM ::fn_helpcollations()

---

AFAIK, collation affects case-sensitivity only of data, not of object names. See what happens if you run this script: CREATE TABLE dbo.test (Test INT)
DROP TABLE dbo.TEST If the object name is truly case-sensitive in your installation, then the second instruction will fail with an error message saying that the object doesn’t exist. If the object name is not case-sensitive, then the table will be dropped without an error.

---

You are aware that this changes only the default collation for the database? This means that this collation will be used when you create a new column in a table in the database, but you do not set a collation for the column. It also means that all existing objects in the database still have the previous collation! Also make sure that you specify the collation for columns on temporary tables in stored procedures (and loose scripts). This is because the default collation in tempdb is the default collation of the server instance, and this is the default for all temporary tables. You will get error messages only when you try to compare columns that have different collations, but I’m not entirely sure if it can really mess up your data if you pass it from one collation to the next. There is a slightly obscure option when setting collation: you can use the phrase collate database_default for temporary tables (if appropriate).

---

maybe you can try select pricelist from dbo.SupplierPriceList if dbo is the owner of the table..anyway just try to put in the owner’s name in the query and see how it execute..
This is a rare error I ever seen..might be due to permission restriction that caused the query not to execute properly…

---

Thanks Adriaan, for bring up the point that it could cause the hindrance while using the temp db. And as you said collate database_default for temporary tables can handle the situation to some extent. At the same time I would like to bring up that your comments above quoted might not be absolutely correct. It depends on what type of collation you set.
“It ALSO mean that all existing objects in the database still have the previous collation!”
Try to execute the below code step by step, which would give you the contradictory results of your own statement. –Step1:
CREATE DATABASE Test
COLLATE Lithuanian_CS_AS –Step 2:
USE Test CREATE TABLE dbo.Test
(TID int identity(1,1),
TDesc char(3)) INSERT Test (TDesc) VALUES(‘A1’) SELECT tID from Test –STep 3:
ALTER DATABASE Test
COLLATE SQL_Latin1_General_CP1_CI_AS SELECT tID from Test — Remember Test is an already existing table — USE Master
— DROP DATABASE Test

---

Uma, Thanks for supplying a script for this. This is the first time I see there is a problem with the case in object names when the db collation is case sensitive, and I sincerely hope it will be the last time too.[xx(] Still, the collation of the existing column doesn’t change, and neither does the case you have to use in literal criteria against a CS column – when you add this to the two SELECT queries … WHERE TDesc LIKE ‘a1’ … they both return 0 rows because the column is case sensitive even if the db isn’t.

---

Tx. for everyones help. I tried with all suggestions, but couldnt succeeded.
May be the collasion discussion gonve over my head.
Adding owners name before the column name made no impact. Also there is no permission restriction.
So what it is ????????
I dont know. But it is true that still the problem exists with me. Tx once again
Raj

---

Check the collation setting of the database where you’re having the problem. A collation name usually ends with a couple of two-character codes, like Lithuanian<b>_CS_AS</b>.<br /><br />The CS bit indicates that the collation is Case Sensitive. In most situations, you would see CI for Case Insensitive.<br /><br />With CS, the string "PriceList" is considered different from the string "pricelist", although in CI they would be considered identical. So with CS, if you have a primary key then these two strings do NOT violate the unique constraint, and with CI they would.<br /><br />On the database level, if the database collation is CS then it also means that you can have two different objects, named <b>a</b> and <b>A</b>. If the database collation is CI, then you can only have one object named <b>a</b> or <b>A</b>, but not both.<br /><br />So the problem with a CS database collation is that you must be extremely careful in your scripts, because the use of lower case and upper case must be 100% correct. A database with a CI collation is both more forgiving, and less headache-inducing.[<img src=’/community/emoticons/emotion-5.gif’ alt=’‘ />]

---

Tx alot Mr Adirran, Such a detailed reply cleared all my doubts And I got the answer my problem too. with regards
Raj

---

]]>