Collation Conflict

I am trying to move my system from SQL Server 7.0 to SQL Server 2000 and I a
m
getting a "Cannot resolve collation conflict" on query which joins a table
and a view.
The relevant field in the table is defined thus:
[ST_ACCOUNT] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
and the linking field in the View is defined as
CONVERT(Char(8), regno) AS Account_No
Do I have to specifically declare a Collation type in the view. If so, how?
PeterYes you need to specify COLLATION
CREATE TABLE #t (col VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS )
INSERT INTO #t VALUES ('a')
--different collation
CREATE TABLE #t1 (col VARCHAR(10) COLLATE SQL_Latin1_General_CP1255_CI_AS)
INSERT INTO #t1 VALUES ('b')
select * from #t1 join #t on
#t.col=#t1.col
--Server: Msg 446, Level 16, State 9, Line 1
--Cannot resolve collation conflict for equal to operation
select * from #t1 join #t on
#t.col=#t1.col COLLATE SQL_Latin1_General_CP1_CI_AS
--No error
"Petet Tickler" <PetetTickler@.discussions.microsoft.com> wrote in message
news:D1019634-175A-45F2-B012-3B3787D001B7@.microsoft.com...
>I am trying to move my system from SQL Server 7.0 to SQL Server 2000 and I
>am
> getting a "Cannot resolve collation conflict" on query which joins a table
> and a view.
> The relevant field in the table is defined thus:
> [ST_ACCOUNT] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> and the linking field in the View is defined as
> CONVERT(Char(8), regno) AS Account_No
> Do I have to specifically declare a Collation type in the view. If so,
> how?
> Peter
>