HELP! Case insensitive database on case sensitive server

[aki999]

We need to install CI database on CS server, and there are some issues
with stored procedures.

Database works and have CI collation (Polish_CI_AS). Server has
coresponding CS collation (Polish_CS_AS). Most queries and procedures
works but some does not :-(

We have table Customer which contains field CustomerID.

Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless of
character case (we have table Customer not CUSTOMER)

Following TSQL generate error message that must declare variable @id
(in lowercase)

DECLARE @ID INT (here @ID in uppercase)
SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)

I know @ID is not equal to @id in CS, but database is CI and table
names Customer and CUSTOMER both works. This does not work for
variables.

I suppose it is tempdb collation problem (CS like a server collation
is). I tried a property "Identifier Case Sensitivity" for my
connection, but it is read only and have value 8 (Mixed) by default -
this is OK I think.

DO I MISS SOMETHING ????

[Erland Sommarskog]

Re: HELP! Case insensitive database on case sensitive server

aki999 (aki999@wp.pl) writes:[color=blue]
> We need to install CI database on CS server, and there are some issues
> DECLARE @ID INT (here @ID in uppercase)
> SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)
>
> I know @ID is not equal to @id in CS, but database is CI and table
> names Customer and CUSTOMER both works. This does not work for
> variables.
>
> I suppose it is tempdb collation problem (CS like a server collation
> is). I tried a property "Identifier Case Sensitivity" for my
> connection, but it is read only and have value 8 (Mixed) by default -
> this is OK I think.
>
> DO I MISS SOMETHING ????[/color]

I will have to admit that I was under the impression that it was the
database collation that controlled the sensitivity of variables,
but apparently it is the system collation, so it appears that you
will have to install a case-insensitive instance, assuming that the
code you have problems with is in stored procedures.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[aki999]

Re: HELP! Case insensitive database on case sensitive server

Database collation do not controll variables name case sensitivity.
Server collation do. Any other object (tables, fields, etc.) names are
case insensitive in described configuration.

Unfortunatelly installing a CI instance is not a good solution, because
that is licensing problem on MS SQL.
It is OK on MSDE, but You have to pay for license for full MS SQL.

I cannot change code of database (stored procedures, functions, etc.)
but I can modify code in client application. I can chenge connection
parameters and database access methods.

May be there are some database or connection parameters that I can
change to force this to work?

[Erland Sommarskog]

Re: HELP! Case insensitive database on case sensitive server

aki999 (aki999@wp.pl) writes:[color=blue]
> Database collation do not controll variables name case sensitivity.
> Server collation do. Any other object (tables, fields, etc.) names are
> case insensitive in described configuration.
>
> Unfortunatelly installing a CI instance is not a good solution, because
> that is licensing problem on MS SQL.
> It is OK on MSDE, but You have to pay for license for full MS SQL.[/color]

Is that really so? But licensing is definitely nothing I know well, so
I assume that you know what you are talking about.
[color=blue]
> May be there are some database or connection parameters that I can
> change to force this to work?[/color]

Apparently not. (And a connetion parameter would be bad a bad idea,
since that could permit you stored a procedure that another process
would not be able to compile.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[aki999]

Re: HELP! Case insensitive database on case sensitive server

So second instance (CI collation) is a one and only solution for now.
I will install named instance of MSDE next to SQL Server and will keep
trying to find other way or modify database application...