SQL query field names in SQL Mgt Studio are case-sensitive

rsaritzky

Hi:

In my Nav SQL databases running under SQL 2005 (3.60 database running under NAV 5.1 client), my queries require field names and table names to be case-correct, e.g. the following query will NOT work:

select * from [CRONUS USA, Inc_$G_L entry]

I receive an error: "Invalid object name 'CRONUS USA, Inc_$G_L entry'."

Notice that the table name is spelled with a lower-case "e" in the word 'entry'

However, if I use the correct-case spelling:

select * from [CRONUS USA, Inc_$G_L Entry]

.. the query works OK.

Similarly for field names, e.g.

select * from [CRONUS USA, Inc_$G_L Entry] where [G_L account No_] = '18100'
does not work ("account" is spelled with lower-case 'a'), but
select * from [CRONUS USA, Inc_$G_L Entry] where [G_L Account No_] = '18100'
works.

Is this related to the database collation sequence? The current collation sequence on the database is SQL_Latin1_General_CP1_CI_AS - case insensitive, Accent Sensitive as I read it (I didn't set up the databases).

Has anyone else had this experience?

Thx

Ron

rsaritzky

I must correct myself. The NAV database's collation sequence is Latin1_General_CS_AS - case-sensitive, accent-sensitive.

Same question - does this control query field/table name case-sensitivity?

Ron

kine

I think yes, it depends on the collation, but I am not sure if on DB collation or server collation (collation of master database).

DenSter

I've always thought that collation is about the data, not about the objects. I've never known otherwise than that field names have to be spelled correctly, which is weird because it does accept keywords in lower case.

rsaritzky

DenSter wrote:

I've always thought that collation is about the data, not about the objects. I've never known otherwise than that field names have to be spelled correctly, which is weird because it does accept keywords in lower case.

I've always thought the same thing. And, for the non-NAV databases on the same server, I don't have the same issue - I can type the query in aNy cAsE tHaT I wAnT aNd iT sTiLL wOrKs <grin>.

I thought it might be my original 3.60 database, but I installed new NAV demo databases (both 5.1 and 2009) from CD and they behave the exact same way.

I also have 2 SQL 2005 server installs on separate machines - same behavior on both.

bbrown

Collation is not about the data, but rather how the database engine reads and processes the data. Having a "case sensitive" colloation , or not, does not determine what data can be stored but rather how a particular database engine will read that data.

rsaritzky

Well, I just found this tidbit of information in Books Online:

"The collation of an identifier depends on the level at which it is defined. Identifiers of instance-level objects, such as logins and database names, are assigned the default collation of the instance. Identifiers of objects in a database, such as tables, views, and column names, are assigned the default collation of the database. For example, two tables with names that differ only in case can be created in a database that has case-sensitive collation, but cannot be created in a database that has case-insensitive collation."

So, in SQL 2005, table and field names are case-sensitive if the database collation is case-sensitive.

Thanks to everyone who responded anyway.

Ron

DenSter

bbrown wrote:

Collation is not about the data

What I mean with "about the data" as opposed to "about the objects" is that I've always thought that collation determines how data is read/stored/processed/looked at/sniffed/eyeballed/whatever other term you want to use, not how table/field names are evaluated in SQL queries (whether on SQL Server or any other DBMS that uses SQL). I've taught myself eons ago to always be very precise when it comes to names of objects/attributes/properties/etc. I don't even try to use anything other than the exact name, including the case of the characters.

I've always thought that the collation ONLY determines anything related to the data, and that the collation does not have an effect on how cases are evaluated when it comes to table/field names. So I've always assumed that SQL Server would consider [NAME] and [name] to be different fields, regardless of the collation. Because I am not a very accurate typist, I've taught myself years ago to always use object browsers, or to drag table/field/stored procedure names into the query window. For the past 8 years I don't think I've actually typed more than 5 fields names, so I've never had a problem with that particular issue.

In other words: if you always use the exact names, you will never have any problem with the case of the characters.

rsaritzky

DenSter wrote:

bbrown wrote:

Collation is not about the data

What I mean with "about the data" as opposed to "about the objects" is that I've always thought that collation determines how data is read/stored/processed/looked at/sniffed/eyeballed/whatever other term you want to use, not how table/field names are evaluated in SQL queries (whether on SQL Server or any other DBMS that uses SQL). I've taught myself eons ago to always be very precise when it comes to names of objects/attributes/properties/etc. I don't even try to use anything other than the exact name, including the case of the characters.

I've always thought that the collation ONLY determines anything related to the data, and that the collation does not have an effect on how cases are evaluated when it comes to table/field names. So I've always assumed that SQL Server would consider [NAME] and [name] to be different fields, regardless of the collation. Because I am not a very accurate typist, I've taught myself years ago to always use object browsers, or to drag table/field/stored procedure names into the query window. For the past 8 years I don't think I've actually typed more than 5 fields names, so I've never had a problem with that particular issue.

My reading of the Books Online and actual trying leads me to the conclusion that as far as table/field names, [NAME] and [name] CAN relate to the same object IF the collation sequence is case-insensitive. I'm a fast typist and a very slow mouser, so I type most queries. It wasn't an issue in SQL2000 - you could type queries in any case and it would work.

Ron

kine

The problem is that the engine is sometime searching for the identifiers in the system tables in the database, it means using the collation for the data of this system table (something like searching for the field in Field system table in NAV ;-)) it means it is really data which are affected by the collation, even when it is "just" identifier of table or field...

DenSter

rsaritzky wrote:

I'm a fast typist and a very slow mouser

Me too, fast typer, moderately slow mouser. BUT fast typing a query and then dealing with all the error messages takes more time for me than slowly dragging them into the query window

In NAV development I use F5 to get fieldnames into my code, and I'm pretty quick at it. There must be shortcut keys in SQL Server, I just don't use it enough to figure them out.

bbrown

There's always the option of using the "Query Designer"

DenSter

Like I said:

DenSter wrote:

I just don't use it enough to figure them out.

rsaritzky

DenSter wrote:

rsaritzky wrote:

I'm a fast typist and a very slow mouser

Me too, fast typer, moderately slow mouser. BUT fast typing a query and then dealing with all the error messages takes more time for me than slowly dragging them into the query window

In NAV development I use F5 to get fieldnames into my code, and I'm pretty quick at it. There must be shortcut keys in SQL Server, I just don't use it enough to figure them out.

A pretty cool tool for SQL is SQLPrompt from RedGate Software. They have a suite of tools for SQL called the SQL Toolbelt, but SQLPrompt is my favorite - it adds auto-complete to sql mgt studio, and has ways to automatically insert snippets. I use it to insert the company name into my query. So I can type "G_L" and it will auto-suggest "Select * from MyCompanyName$G_L Entry"

A free 14-day trial is available at http://www.red-gate.com

ara3n

SQL 2008 has auto-complete. It's one of the reasons I upgraded to sqk 2k8 on my laptop.