MS SQL Server - case sensitive collation issues

Author

case sensitive collation issues

Jack Vamvas

2006-10-24, 6:38 pm

I have a db - DB1 = Collation=SQL_Latin1
_General_CP1_CI_AS, SQLSortOrder=52
It's sitting on a server Collation = Latin1_General_BIN
The tempdb = Latin1_General_BIN

Note: I created the db from scratch on the server

When I run the following

CREATE PROCEDURE test
AS
DECALRE @test VARCHAR(1)
SELECT @TEST

Error = Server: Msg 137, Level 15, State 2, Procedure test, Line 5
Must declare the variable '@TEST'.

If I change the SELECT @TEST to SELECT @test it works fine.

It looks like it's a case issue , I thought the the db setting overrides the
the server setting and that any new objects created inherited the db
collation levels ?

Hilary Cotter

2006-10-24, 6:38 pm

did you spell declare incorrectly? However when I spell it correctly and run
your code, it works. Is your code sample complete?

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

"Jack Vamvas" < DEL_TO_REPLYtechsupp
ort@ciquery.com> wrote in message
news:rt2dnVE7BcPWIqX
YnZ2dnUVZ8qWdnZ2d@bt
.com...
>I have a db - DB1 = Collation=SQL_Latin1
_General_CP1_CI_AS,
>SQLSortOrder=52
> It's sitting on a server Collation = Latin1_General_BIN
> The tempdb = Latin1_General_BIN
>
> Note: I created the db from scratch on the server
>
> When I run the following
>
> CREATE PROCEDURE test
> AS
> DECALRE @test VARCHAR(1)
> SELECT @TEST
>
> Error = Server: Msg 137, Level 15, State 2, Procedure test, Line 5
> Must declare the variable '@TEST'.
>
>
> If I change the SELECT @TEST to SELECT @test it works fine.
>
>
> It looks like it's a case issue , I thought the the db setting overrides
> the
> the server setting and that any new objects created inherited the db
> collation levels ?
>
>
>
>
>
>
>

Jack Vamvas

2006-10-24, 6:38 pm

Sorry,Yes , in the db the spelling is correct.
I have run the same code on another server where the server collation and
the db collation are the same , it works fine.

It's in the set up outlined below where the problem arises.
Does the tempdb collation create an issue?

"Hilary Cotter" <hilary.cotter@gmail.com> wrote in message
news:O6zX7aE9GHA.1188@TK2MSFTNGP05.phx.gbl...
> did you spell declare incorrectly? However when I spell it correctly and
> run your code, it works. Is your code sample complete?
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
>
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
>
>
> "Jack Vamvas" < DEL_TO_REPLYtechsupp
ort@ciquery.com> wrote in message
> news:rt2dnVE7BcPWIqX
YnZ2dnUVZ8qWdnZ2d@bt
.com...
>
>

John Bell

2006-10-24, 6:38 pm

Hi Jack

With tempdb using a different collation then you may have collation
conflicts when creating or joining to temporary tables. This can be overcome
by using the COLLATE option to force the collation when you either join to
the temporary table or when it is created.

John

"Jack Vamvas" wrote:

> Sorry,Yes , in the db the spelling is correct.
> I have run the same code on another server where the server collation and
> the db collation are the same , it works fine.
>
> It's in the set up outlined below where the problem arises.
> Does the tempdb collation create an issue?
>
>
>
> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message
> news:O6zX7aE9GHA.1188@TK2MSFTNGP05.phx.gbl...
>
>
>

Sponsored Links