Hi!
I have a table that keeps track of the
system numbers that my system needs to issue.

This is a Warehouse Control System. So the type of numbers are like
* pallet numbers
* assignment numbers
and so on.

This is (successfully) done by emulating the behavior of
other db's concept of sequences, ie a thingy where
I can pull out a number OUTSIDE my current transaction.

However, since my coworkers would become upset if the
db differed on cAsE of entity names, I set the db to
be insensitive to casing, when it comes to
table names and column names.
This then leads to having the CONTENT of string fields to
be insensitive to casing TOO

Ie i can write

select A from B where C = 'ABC'
as well as
select a from b where c = 'abc'

and still get the same result-set. This is not OK in the application.
So, I append COLLATE SQL_Latin1_General_CP1_CS_AS on all string
fields
in my tables like:
create table BSYSNO (
BSYTYP integer default 1 not null , -- Primary Key
BSYFIRST integer default 1 ,
BSYLAST integer default 1 ,
BSYSEQU varchar(40) COLLATE SQL_Latin1_General_CP1_CS_AS default '
' , -- unique index 2
IXXLUPD varchar(12) COLLATE SQL_Latin1_General_CP1_CS_AS default ' '
not null,
IXXLUTS datetime not null
)
go

alter table BSYSNO add constraint BSYSNOP1 primary key (
bsytyp
)
go

create unique index BSYSNOI2 on BSYSNO (
bsysequ
)
go



Now things are looking real good.
Except for this one:

The table described above holds the name of the tables
emulating sequences. So when developing/setting up a new system
we have script that creates the missing tables.
There, I look into INFORMATION_SCHEMA.TABLES as well as
into BSYSNO.

I then run this:

select
'create table ' + rtrim(BSYSEQU) + '(' +
' NUM bigint IDENTITY(' + rtrim(cast(BSYFIRST as CHAR)) + ',1),' +
' TS datetime default current_timestamp' +
')'
from BSYSNO
where not exists (select 'x' from INFORMATION_SCHEMA.TABLES where
TABLE_NAME=rtrim(BSYSEQU))
go

exit
go

which creates the needed CREATE TABLE statements.
(They are redirected to another file, which is fed into sqlcmd)

But I now get this

Msg 468, Level 16, State 9, Server SELNDRDW03ARON, Line 18
Cannot resolve the collation conflict between
"SQL_Latin1_General_CP1_CS_AS"
and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

And I see the reason. The view's string fields are not case sensitive,
but
BSYSNO's string fields are.

Anyone have a suggestion to how I get around this?

/Björn

björn lundin (b.f.lundin (AT) gmail (DOT) com) writes:
Personally, I have never seen the point with calling a table Orders in one
minute, orders in the next and then ORDERS on the day after. I would smack
on a case-insenstive collation and go with that.

Why that collation? Why not Finnish_Swedish_CS_AS, which would be the
natural choice? Or at least Latin1_General_CS_AS? Avoid the SQL collations,
there are some nasty traps and funny thing with these. They exist for
legacy only.

You need to use the COLLATE clause to force the collation on either
side. Which probably should be the INFORMATION_SCHEMA side, since else
you will kill the index on the column in BSYSNO:

where not exists (select 'x' from INFORMATION_SCHEMA.TABLES where
TABLE_NAME COLLATE Finnish_Swedish_CS_AS =BSYSEQU)

I removed the rtrim since it's not needed, and that's another index killer.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Hmm, yes, if it was not for the code itself is a bit inconsistent when
it comes to
casing. And recently, we switched gui, using an internal .net
framework
used within other parts of the company. And that framework
uses the case of TABLE_NAME.column_name. Which the background programs
does not.
And the co-worker use sql*plus for Oracle all the time,
and now I suspect sqlcmd will be popular.
(which inspires me to create another thread about describe tables)

Also, some parts of the system changes a lot, some parts are very
static.
Given that the old parts stems from the late 80'/early 90's,
the way of coding has changed.
The old code uses VARIABLE_NAMES is capital case,
while the new stuff uses Variable_Names in Camel_Case instead.
This sometimes also applies to the sql-statements in the code as well.

Another reason is that the language all is written in (but for the
gui)
is Ada, which is case-ignorant, both for reserved words, and for
variables.
(Ada83, then Ada95, and now Ada05)


Giving my name, yes that would be natural. But the application runs
in Denmark/Norway/Finland/The UK/BeNeLux and Germany as well.
I'd like to keep the same collation everywhere.
Order by clauses on strings having language-specific characters
are very rare, if they ever occur.
Case equivalence is something else.

The reason is simply ignorance. I did not have a clue, found the one I
use,
and moved on. This concept is a bit new to me, even if I see the point
of
having the different collations.
I'll switch to Latin1_General_CS_AS.
Thanks for bringing it up.


Perfect. I'll test the coming week.
The index thing is a non-existing problem,
the table only holds max 10-15 records or so,
And its only run at at system startup.
But the tip may come in handy in other situations.

Again, thanks Erland
--
Björn

björn lundin (b.f.lundin (AT) gmail (DOT) com) writes:
Ada? I was into that for a while; I was very marginally involved in the
work with Ada95. But since I started to work exclusively with Microsoft SQL
Server and Windows, I've lost all contact with it.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On 12 Juni, 20:40, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Oh? In what area?
The only foorprint I have, is making ACT change the wordings in a
warning in gcc/gnat.
I did not understand it, and they changed it...

Well, as you probably know, it runs well on windows too...

--
Björn

björn lundin (b.f.lundin (AT) gmail (DOT) com) writes:
There was some kind of Swedish working group, and I got in mainly for
my interest in localisation/internationalisation. I think my final
contribution was minimal. But I did participate in a conference that
was held here in Sweden which included the top guys of the Ada 95
Committee.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx