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 ?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 ?
>
>
>
>|||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" <jbellnewsposts@.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 ?
>>
>>
>>|||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
"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" <jbellnewsposts@.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 ?
> >>
> >>
> >>
> >>
> >>
> >>
> >>
>
>sql
Showing posts with label tempdb. Show all posts
Showing posts with label tempdb. Show all posts
Sunday, March 25, 2012
case sensitive collation issues
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 ?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_REPLYtechsupport@.ciquery.com> wrote in message
news:rt2dnVE7BcPWIqXYnZ2dnUVZ8qWdnZ2d@.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 ?
>
>
>
>|||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_REPLYtechsupport@.ciquery.com> wrote in message
> news:rt2dnVE7BcPWIqXYnZ2dnUVZ8qWdnZ2d@.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 ?
>>
>>
>>
>|||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...
> > 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_REPLYtechsupport@.ciquery.com> wrote in message
> > news:rt2dnVE7BcPWIqXYnZ2dnUVZ8qWdnZ2d@.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 ?
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>
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 ?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_REPLYtechsupport@.ciquery.com> wrote in message
news:rt2dnVE7BcPWIqXYnZ2dnUVZ8qWdnZ2d@.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 ?
>
>
>
>|||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_REPLYtechsupport@.ciquery.com> wrote in message
> news:rt2dnVE7BcPWIqXYnZ2dnUVZ8qWdnZ2d@.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 ?
>>
>>
>>
>|||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...
> > 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_REPLYtechsupport@.ciquery.com> wrote in message
> > news:rt2dnVE7BcPWIqXYnZ2dnUVZ8qWdnZ2d@.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 ?
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>
case sensitive collation issues
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 ?
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_REPLYtechsupport@.ciquery.com> wrote in message
news:rt2dnVE7BcPWIqXYnZ2dnUVZ8qWdnZ2d@.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 ?
>
>
>
>
|||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_REPLYtechsupport@.ciquery.com> wrote in message
> news:rt2dnVE7BcPWIqXYnZ2dnUVZ8qWdnZ2d@.bt.com...
>
|||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...
>
>
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 ?
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_REPLYtechsupport@.ciquery.com> wrote in message
news:rt2dnVE7BcPWIqXYnZ2dnUVZ8qWdnZ2d@.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 ?
>
>
>
>
|||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_REPLYtechsupport@.ciquery.com> wrote in message
> news:rt2dnVE7BcPWIqXYnZ2dnUVZ8qWdnZ2d@.bt.com...
>
|||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...
>
>
Thursday, March 22, 2012
case sensitive collation issues
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 ?
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 ?
>
>
>
>
|||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" <jbellnewsposts@.hotmail.com> wrote in message
news:AD1B6D99-C104-4F56-BCF8-4E87193FEF82@.microsoft.com...[vbcol=seagreen]
> 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:
|||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
"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" <jbellnewsposts@.hotmail.com> wrote in message
> news:AD1B6D99-C104-4F56-BCF8-4E87193FEF82@.microsoft.com...
>
>
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 ?
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 ?
>
>
>
>
|||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" <jbellnewsposts@.hotmail.com> wrote in message
news:AD1B6D99-C104-4F56-BCF8-4E87193FEF82@.microsoft.com...[vbcol=seagreen]
> 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:
|||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
"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" <jbellnewsposts@.hotmail.com> wrote in message
> news:AD1B6D99-C104-4F56-BCF8-4E87193FEF82@.microsoft.com...
>
>
case sensitive collation issues
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 ?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_REPLYtechsupport@.ciquery.com> wrote in message
news:rt2dnVE7BcPWIqXYnZ2dnUVZ8qWdnZ2d@.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 ?
>
>
>
>|||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_REPLYtechsupport@.ciquery.com> wrote in message
> news:rt2dnVE7BcPWIqXYnZ2dnUVZ8qWdnZ2d@.bt
.com...
>|||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...
>
>
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 ?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_REPLYtechsupport@.ciquery.com> wrote in message
news:rt2dnVE7BcPWIqXYnZ2dnUVZ8qWdnZ2d@.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 ?
>
>
>
>|||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_REPLYtechsupport@.ciquery.com> wrote in message
> news:rt2dnVE7BcPWIqXYnZ2dnUVZ8qWdnZ2d@.bt
.com...
>|||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...
>
>
Thursday, February 16, 2012
Cant's shrink tempdb in sql2k5
Hi, tried to shrink 190gb tempdb but got following message:
DBCC SHRINKFILE: Page 1:24027896 could not be moved because it is a work table page.
Anyone know why? The tempdb is almost empty, just doesn't release free space.
If the database is still being used by an active transaction and the worktable locates at the end of the file, you can't shrink it. Consider taking at look at the following resources.http://support.microsoft.com/kb/307487
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
|||
Thanks for the info, but they don't address my issue.
The problem I have is that tempdb has lot of free pages in internal object reserved page pool, but sql2k5 doesn't use those free pages when create new internal object. It expands tempdb to get more pages instead, so causes tempdb to keep growing. I got the page number of so called work table page and checked it with dbcc page to get object id it belongs to, but have no way to check if the object is still be used in the tempdb. The empty tempdb keeps grow and eventually run out of disk space, the only way to stop it is restarting sql. I think sql2k5 should have better way to handle this.
|||I personally have not seen this issue.As clearly stated in the whitepaper, internal objects are untouchable by user. If you think there is a bug, you can file one at http://connect.microsoft.com. Be sure to provide a complete repro script.|||I'm having the same issue. Has anyone came up with a solution. The only way it will shrink is to restart the services.|||
hi rmiao,
is your tempdb files set to autogrow?
regards
jag
|||first we need to know why SQL Server is consuming tempdb so alarmingly ... is there any database set as Snapshot issolation level or rowversioning... is there excessive use of Tempdb/tablevariable.. or else just run SQL Profiler and find out the reason behind the growth first
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
Madhu
|||Yes, but don't think it's related.
|||I like to know why as well, but didn't get any answer from Microsoft.|||most probably... excessive tempdb consumption is due to some setting /feature in some user database... that u need to findout yourself... read the link provided and see what feature u are using which consumes tempdb...
Madhu
|||You could also look at :
http://msdn2.microsoft.com/en-gb/library/ms176029.aspx
this gives practical monitoring suggestions and links to other tempdb concept articles.
Cant's shrink tempdb in sql2k5
Hi, tried to shrink 190gb tempdb but got following message:
DBCC SHRINKFILE: Page 1:24027896 could not be moved because it is a work table page.
Anyone know why? The tempdb is almost empty, just doesn't release free space.
If the database is still being used by an active transaction and the worktable locates at the end of the file, you can't shrink it. Consider taking at look at the following resources.http://support.microsoft.com/kb/307487
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
|||
Thanks for the info, but they don't address my issue.
The problem I have is that tempdb has lot of free pages in internal object reserved page pool, but sql2k5 doesn't use those free pages when create new internal object. It expands tempdb to get more pages instead, so causes tempdb to keep growing. I got the page number of so called work table page and checked it with dbcc page to get object id it belongs to, but have no way to check if the object is still be used in the tempdb. The empty tempdb keeps grow and eventually run out of disk space, the only way to stop it is restarting sql. I think sql2k5 should have better way to handle this.
|||I personally have not seen this issue.As clearly stated in the whitepaper, internal objects are untouchable by user. If you think there is a bug, you can file one at http://connect.microsoft.com. Be sure to provide a complete repro script.|||I'm having the same issue. Has anyone came up with a solution. The only way it will shrink is to restart the services.|||
hi rmiao,
is your tempdb files set to autogrow?
regards
jag
|||first we need to know why SQL Server is consuming tempdb so alarmingly ... is there any database set as Snapshot issolation level or rowversioning... is there excessive use of Tempdb/tablevariable.. or else just run SQL Profiler and find out the reason behind the growth first
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
Madhu
|||Yes, but don't think it's related.
|||I like to know why as well, but didn't get any answer from Microsoft.|||most probably... excessive tempdb consumption is due to some setting /feature in some user database... that u need to findout yourself... read the link provided and see what feature u are using which consumes tempdb...
Madhu
|||You could also look at :
http://msdn2.microsoft.com/en-gb/library/ms176029.aspx
this gives practical monitoring suggestions and links to other tempdb concept articles.
Cant's shrink tempdb in sql2k5
Hi, tried to shrink 190gb tempdb but got following message:
DBCC SHRINKFILE: Page 1:24027896 could not be moved because it is a work table page.
Anyone know why? The tempdb is almost empty, just doesn't release free space.
If the database is still being used by an active transaction and the worktable locates at the end of the file, you can't shrink it. Consider taking at look at the following resources.http://support.microsoft.com/kb/307487
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
|||
Thanks for the info, but they don't address my issue.
The problem I have is that tempdb has lot of free pages in internal object reserved page pool, but sql2k5 doesn't use those free pages when create new internal object. It expands tempdb to get more pages instead, so causes tempdb to keep growing. I got the page number of so called work table page and checked it with dbcc page to get object id it belongs to, but have no way to check if the object is still be used in the tempdb. The empty tempdb keeps grow and eventually run out of disk space, the only way to stop it is restarting sql. I think sql2k5 should have better way to handle this.
|||I personally have not seen this issue.As clearly stated in the whitepaper, internal objects are untouchable by user. If you think there is a bug, you can file one at http://connect.microsoft.com. Be sure to provide a complete repro script.|||I'm having the same issue. Has anyone came up with a solution. The only way it will shrink is to restart the services.|||
hi rmiao,
is your tempdb files set to autogrow?
regards
jag
|||first we need to know why SQL Server is consuming tempdb so alarmingly ... is there any database set as Snapshot issolation level or rowversioning... is there excessive use of Tempdb/tablevariable.. or else just run SQL Profiler and find out the reason behind the growth first
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
Madhu
|||Yes, but don't think it's related.
|||I like to know why as well, but didn't get any answer from Microsoft.|||most probably... excessive tempdb consumption is due to some setting /feature in some user database... that u need to findout yourself... read the link provided and see what feature u are using which consumes tempdb...
Madhu
|||You could also look at :
http://msdn2.microsoft.com/en-gb/library/ms176029.aspx
this gives practical monitoring suggestions and links to other tempdb concept articles.
Tuesday, February 14, 2012
Cant's shrink tempdb in sql2k5
Hi, tried to shrink 190gb tempdb but got following message:
DBCC SHRINKFILE: Page 1:24027896 could not be moved because it is a work table page.
Anyone know why? The tempdb is almost empty, just doesn't release free space.
If the database is still being used by an active transaction and the worktable locates at the end of the file, you can't shrink it. Consider taking at look at the following resources.http://support.microsoft.com/kb/307487
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
|||
Thanks for the info, but they don't address my issue.
The problem I have is that tempdb has lot of free pages in internal object reserved page pool, but sql2k5 doesn't use those free pages when create new internal object. It expands tempdb to get more pages instead, so causes tempdb to keep growing. I got the page number of so called work table page and checked it with dbcc page to get object id it belongs to, but have no way to check if the object is still be used in the tempdb. The empty tempdb keeps grow and eventually run out of disk space, the only way to stop it is restarting sql. I think sql2k5 should have better way to handle this.
|||I personally have not seen this issue.As clearly stated in the whitepaper, internal objects are untouchable by user. If you think there is a bug, you can file one at http://connect.microsoft.com. Be sure to provide a complete repro script.|||I'm having the same issue. Has anyone came up with a solution. The only way it will shrink is to restart the services.|||
hi rmiao,
is your tempdb files set to autogrow?
regards
jag
|||first we need to know why SQL Server is consuming tempdb so alarmingly ... is there any database set as Snapshot issolation level or rowversioning... is there excessive use of Tempdb/tablevariable.. or else just run SQL Profiler and find out the reason behind the growth first
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
Madhu
|||Yes, but don't think it's related.
|||I like to know why as well, but didn't get any answer from Microsoft.|||most probably... excessive tempdb consumption is due to some setting /feature in some user database... that u need to findout yourself... read the link provided and see what feature u are using which consumes tempdb...
Madhu
|||You could also look at :
http://msdn2.microsoft.com/en-gb/library/ms176029.aspx
this gives practical monitoring suggestions and links to other tempdb concept articles.
Subscribe to:
Posts (Atom)