Error with default database collation

Hello
I am using SQL Server 2000 SP4
In my stored procedure I create table variable that contains one varchar
field and join that table variable with one of the database tables. I keep
getting error "Cannot resolve collation conflict for equal to operation.".
Both fields should have same collation. Table's field due to the fact that
it's collation is set to <database default> and <database default> is
Latin1_General_CI_AS. And according to Books online table variable also has
a
collation taken from <database default> if not explicitly set to other. So
what is the problem? If table variable field explicitly assign collation
Latin1_General_CI_AS - everything works fine. Why? My server's default
collation is Cyrillic_General_CI_AS. But how that can affect? I am not
allowed to use explicit collation in my SP.
Probably it is a known bug in SQL Server... Are then any fixes for that?
Thanks in advance.Seems like SQL2K doesn't pick up the current database for the connection whe
n you create a table
variable:
CREATE DATABASE x COLLATE Cyrillic_General_CI_AS
GO
USE x
CREATE TABLE t(c1 varchar(10))
INSERT INTO t VALUES('asd')
GO
--Error on 2000 sp3, fine on 2005
DECLARE @.t table(c1 varchar(10))
INSERT INTO @.t VALUES('asd')
SELECT * FROM t INNER JOIN @.t AS t2 ON t.c1 = t2.c1
GO
--Fine on both 2000 sp3 and 2005, as expected
DECLARE @.t table(c1 varchar(10) COLLATE database_Default)
INSERT INTO @.t VALUES('asd')
SELECT * FROM t INNER JOIN @.t AS t2 ON t.c1 = t2.c1
GO
--Error as expected on both 2000 sp3 and 2005
CREATE TABLE #t (c1 varchar(10))
INSERT INTO #t VALUES('asd')
SELECT * FROM t INNER JOIN #t AS t2 ON t.c1 = t2.c1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in messag
e
news:C5DD7198-4122-483E-BFD6-E83AF28002F7@.microsoft.com...
> Hello
> I am using SQL Server 2000 SP4
> In my stored procedure I create table variable that contains one varchar
> field and join that table variable with one of the database tables. I keep
> getting error "Cannot resolve collation conflict for equal to operation.".
> Both fields should have same collation. Table's field due to the fact that
> it's collation is set to <database default> and <database default> is
> Latin1_General_CI_AS. And according to Books online table variable also ha
s a
> collation taken from <database default> if not explicitly set to other. So
> what is the problem? If table variable field explicitly assign collation
> Latin1_General_CI_AS - everything works fine. Why? My server's default
> collation is Cyrillic_General_CI_AS. But how that can affect? I am not
> allowed to use explicit collation in my SP.
> Probably it is a known bug in SQL Server... Are then any fixes for that?
> Thanks in advance.