case sensitive collation issues

20 Oct 2006 12:20 PM

Jack Vamvas

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 ?

20 Oct 2006 1:26 PM

John Bell

Hi Jack

In the Collate topic in BOL it says "The identifiers for variables, GOTO
labels, temporary stored procedures, and temporary tables are in the default
collation of the instance." Therefore the behaviour you see is as expected.

John

Show quoteHide quote

"Jack Vamvas" wrote:

> 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 ?
>
>
>
>
>
>
>

20 Oct 2006 2:53 PM

Jack Vamvas

Thanks

I've tried all sorts of things , as outlined below, so potentially it look
like the only solution is to either change the COLLATION level of the
instance or make the variables uniform in the sp.

Show quoteHide quote

"John Bell" <jbellnewspo***@hotmail.com> wrote in message
news:AD1B6D99-C104-4F56-BCF8-4E87193FEF82@microsoft.com...
> Hi Jack
>
> In the Collate topic in BOL it says "The identifiers for variables, GOTO
> labels, temporary stored procedures, and temporary tables are in the
> default
> collation of the instance." Therefore the behaviour you see is as
> expected.
>
> John
>
> "Jack Vamvas" wrote:
>
>> 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 ?
>>
>>
>>
>>
>>
>>
>>

20 Oct 2006 3:13 PM

John Bell

Hi

Yes, as your instance is case sensitive then the variables will be as well.
As this is a compile error it is not so bad, and it is something you will
need to do if you don't have control of the live environment (e.g. if you
sell a product). Make sure that your coding standards cover this. If you use
source code control (at object level) it will help you track down when issues
due to collation problems are intorduced, and an automated build process
would also be a way of quickly catching this type of error (before you are
rushing to ship a release!)

HTH

John

Show quoteHide quote

"Jack Vamvas" wrote:

> Thanks
>
> I've tried all sorts of things , as outlined below, so potentially it look
> like the only solution is to either change the COLLATION level of the
> instance or make the variables uniform in the sp.
>
>
>
> "John Bell" <jbellnewspo***@hotmail.com> wrote in message
> news:AD1B6D99-C104-4F56-BCF8-4E87193FEF82@microsoft.com...
> > Hi Jack
> >
> > In the Collate topic in BOL it says "The identifiers for variables, GOTO
> > labels, temporary stored procedures, and temporary tables are in the
> > default
> > collation of the instance." Therefore the behaviour you see is as
> > expected.
> >
> > John
> >
> > "Jack Vamvas" wrote:
> >
> >> 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 ?
> >>
> >>
> >>
> >>
> >>
> >>
> >>
>
>
>

Thread options