Concatenating fields

[sdh]

I get the following error from the query analyzer when
I try to run this query

Server: Msg 457, Level 16, State 1, Procedure usp_keywordSearchLineageAllAncestrytable, Line 10
Implicit conversion of char value to char cannot be performed because the collation of the value is unresolved due to a collation conflict.


All the fields I am trying to concatenate are char (50)


Can anyone suggest a reason why.



Select (tbLin_id) ancestryID,(tbHrse_id) horse_id,(tbLin_PGS+tbLin_PGD+tbLin_MGS+tbLIn_MGD+tbLin_PGGS+tbLin_PGGD+tblin_MGGS+tbLin_MGGD+tblin_PDGGS+tblin_PDGGD+tblin_MSGGS+tblin_MSGGD) ancestryText

into #ancestry
From
[dbTophorse].[dbo].[tbLineage]

 

[dreameR78]

Hi there,

I am not sure what version of SQL Server you are using but if you are using SQL Server 2000 then carry on reading!

It sounds like some of your columns in your table have different collations so you need to ensure that they are the same.

First of all, get the curret collation of your database using the DataBasePropertyEX fucntion. Something like this :

select DatabasePropertyEX ('@DataBase', 'collation') -- replace @Database with the name of your database of course!

Copy the name of the collation and use it in your query. Something like this :

Select (tbLin_id) ancestryID,
(tbHrse_id) horse_id,
(tbLin_PGS COLLATE SQL_Latin1_General_CP1_CI_AS+
tbLin_PGD COLLATE SQL_Latin1_General_CP1_CI_AS+
tbLin_MGS COLLATE SQL_Latin1_General_CP1_CI_AS+
tbLIn_MGD COLLATE SQL_Latin1_General_CP1_CI_AS+
tbLin_PGGS COLLATE SQL_Latin1_General_CP1_CI_AS+
tbLin_PGGD COLLATE SQL_Latin1_General_CP1_CI_AS+
tblin_MGGS COLLATE SQL_Latin1_General_CP1_CI_AS+
tbLin_MGGD COLLATE SQL_Latin1_General_CP1_CI_AS+
tblin_PDGGS COLLATE SQL_Latin1_General_CP1_CI_AS+
tblin_PDGGD COLLATE SQL_Latin1_General_CP1_CI_AS+
tblin_MSGGS COLLATE SQL_Latin1_General_CP1_CI_AS+
tblin_MSGGDCOLLATE SQL_Latin1_General_CP1_CI_AS) ancestryText
into #ancestry
From
[dbTophorse].[dbo].[tbLineage]

Hope this helps.

 

[sdh]

Thanks dreameR78 thats been a great help.