IDENTIFIER CASE in SQL 2000 Navision

Greetings We are currently testing Attain 3.6 on SQL server 2000 prior to an upgrade from Nav 2.6 (currently on SQL7). We have various SQL scripts and asp applications which read the Navsion database directly. The move to remove the illegal characters from the column names etc (hoorah, hoorah) means we are reviewing all the code. An unexpected side-effect has been revealed namely that the column identifiers have become case sensitive ie [No_] works but [no_] gives an ‘Invalid column name’ error. This is despite the fact that the IDENTIFIER_CASE setting on the server is set to ‘MIXED’ and other (non-Navision) databases are not case sensitive. Any ideas or workarounds would be welcome. Kennedy

Identifer case-sensitivity follows the collation properties of the database. If your database uses a case-sensitive collation, the identifiers within it will also be, and vice versa. In SQL Server 2000 you can specify the database collation when you create it; each database has its own collation. (In 7.0 there was only one collation - the server collation, that all databases followed.) In Attain, you specify the collation in the New Database window. Here you can choose with your data (and identifiers) should be case/accent sensitive. Case sensitive is the most flexible, data wise, because you can always change a case-sensitive search for a value into an insensitive one, but you cannot change an insensitive search to a sensitive one (with a bad performance hit, which Attain does not allow).

Thanks for this information. Kennedy