Error in the PROCEDURE....

Hi,
i m trying to run a procedure...but everytime i run it...it gives me some error as:
-
Msg 468, Level 16, State 9, Procedure FIN_INFO__Get_Cash_Payments_Info, Line 46

Cannot resolve the collation conflict between "Cyrillic_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
--

Whats the meaning of "Cyrillic_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS"
How can i change it?
if someone has some idea then please let me know...that will b great help to me...
thnks,
regards.

"Cyrillic_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" are the collations of the columns being compared. Here is an example:

create table col (col1 varchar(10) collate Cyrillic_General_CI_AS,

col2 varchar(10) collate SQL_Latin1_General_CP1_CI_AS)

select * from col

where col1 = col2

Results:

Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Cyrillic_General_CI_AS" in the equal to operation.
But if you explicitly convert the collation like in the following, the query will work:

select * from col

where col1 = col2 collate SQL_Latin1_General_CP1_CI_AS

More information can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_7ory.asp

collate SQL_Latin1_General_CP1_CI_AS

|||Thanks for the reply...so that means that in the whole procedure i have to make this COLLATE command u suggested....for all the columns?ok..i will try it....n i hope it will work...thnks a lot once again
regards...|||You only need to use the COLLATE command it the collation of the columns can not be implicitly compared. If you need to do this all over, you might evaulate if you can change the column type to a more compatible collation.

Peter|||Hi,
i tried all the ways to use COLLATE...but still the same error...can you please go through the code and tell me...where exactly i have to use COLLATE...i will really appreciate any help...

ALTER procedure [dbo].[Get_Payments]

(

@.p_Personal_Account numeric(15,0), -- Personal_Account_Attributes.Personal_Account%type,

@.p_Quantity int, -- number := NULL,

@.p_Date_From datetime, -- date := NULL,

@.p_Date_To datetime -- date := SYSDATE,

)

as

begin

begin try

select @.p_Quantity = null

select @.p_Date_From = null

select @.p_Date_To = getdate()

IF (@.p_Quantity IS NULL)

select PD.Payment_Document_Id ,

PD.Document_Number collate SQL_Latin1_General_CP1_CI_AS,

(select isnull(sum(FT.Amount),0)

from Financial_Transaction FT

where FT.Personal_Account = @.p_Personal_Account

and FT.Payment_Document_id = PD.Payment_Document_Id collate SQL_Latin1_General_CP1_CI_AS

and FT.Operation_Type not in(select OL.Operation_Type

from Operation_Type_Group OG, Operation_Type_Group_Link OL

where OG.Group_Code = dbo.fnGetConstant('Collection_Constants', 'g_Sum_Differences_Group')

and OL.Operation_Type_Group_id = OG.Operation_Type_Group_id collate SQL_Latin1_General_CP1_CI_AS) ) Document_Amount,

round((select isnull(sum(FT.Amount),0)

from Financial_Transaction FT

where FT.Personal_Account = @.p_Personal_Account

and FT.Payment_Document_id = PD.Payment_Document_Id

and FT.Operation_Type not in(select OL.Operation_Type

from Operation_Type_Group OG, Operation_Type_Group_Link OL

where OG.Group_Code = dbo.fnGetConstant('Collection_Constants', 'g_Sum_Differences_Group')

and OL.Operation_Type_Group_id = OG.Operation_Type_Group_id)) * ER.Rate_Value / ER.Rate_Amount, convert(int, dbo.fnGetConstant('Collection_Constants', 'g_Round_Size'))) Internal_Amount,

PD.Currency_Code collate SQL_Latin1_General_CP1_CI_AS,

(select CR.Currency_Symbol

from Currency CR

where CR.Currency_Code = PD.Currency_Code collate SQL_Latin1_General_CP1_CI_AS) Currency_Symbol,

case

when PD.Spec_Detail_Table_Code = dbo.fnGetConstant('Collection_Constants', 'g_Spec_Detail_Table_EP')

then (select min(F.Date_Of_Transaction)

from Financial_Transaction F

where F.Personal_Account = @.p_Personal_Account

and F.Payment_Document_id = PD.Payment_Document_Id)

else PD.Date_of_Payment

end Date_of_Payment,

case

when PD.Spec_Detail_Table_Code = dbo.fnGetConstant('Collection_Constants', 'g_Spec_Detail_Table_EP')

then PD.Date_Of_Payment

else PD.Date_of_Collection

end Date_of_Collection,

(select min(FT.Date_of_Transaction)

from Financial_Transaction FT

where FT.Payment_Document_Id = PD.Payment_Document_Id collate SQL_Latin1_General_CP1_CI_AS

and FT.Personal_Account = @.p_Personal_Account) Date_Of_Transaction,

PD.Pay_Form,

(select PF.Pay_Form_Name

from Pay_Form PF

where PF.Pay_Form = PD.Pay_Form collate SQL_Latin1_General_CP1_CI_AS) Pay_Form_Name,

case

when PD.Spec_Detail_Table_Code = dbo.fnGetConstant('Collection_Constants', 'g_Spec_Detail_Table_EP')

then (select EPC_Serial_Number

from EPC_Payment EP

where EP.Payment_Document_Id = PD.Payment_Document_id collate SQL_Latin1_General_CP1_CI_AS)

else PD.Pay_Form_Number

end Pay_Form_Number,

PD.Customer_Data,

PD.Commentary,

PD.Spec_Detail_Table_Code,

(select PP.Payment_Place_Type

from Payment_Packet PP

where PD.Pay_Packet_id = PP.Pay_Packet_id) Payment_Place_Type,

(select PPT.Payment_Place_Type_Name

from Payment_Place_Type PPT

where PPT.Payment_Place_Type = (select PP.Payment_Place_Type

from Payment_Packet PP

where PD.Pay_Packet_id = PP.Pay_Packet_id)) Payment_Place_Type_Name,

PD.Date_of_Change,

PD.User_id_of_Change ,

(select ER1.Rate_Value/ER1.Rate_Amount

from Exchange_Rate_List ER1

where PD.Date_Of_Collection between ER1.Start_Date and isnull(ER1.End_Date, dbo.fnGetConstant('Collection_Constants', 'g_Max_Date'))

and ER1.From_Currency_Code = dbo.fnGetConstant('Collection_Constants', 'g_Currency_Code_Internal')

and ER1.To_Currency_Code = dbo.fnGetConstant('Collection_Constants', 'g_Currency_Code_National')

and ER1.Exchange_List_Type = dbo.fnGetConstant('Collection_Constants', 'g_Exchange_List_Type')) Course

from Payment_Document PD, Exchange_Rate_List ER

where PD.Payment_Document_id in (select F.Payment_Document_id

from Financial_Transaction F

where F.Personal_Account = @.p_Personal_Account

and F.Date_Of_Transaction between isnull(@.p_Date_From, dbo.fnGetConstant('Collection_Constants', 'g_Min_Date'))

and isnull(@.p_Date_To, dbo.fnGetConstant('Collection_Constants', 'g_Max_Date')))

and (select min(F.Date_of_Transaction)

from Financial_Transaction F

where F.Personal_Account = @.p_Personal_Account

and F.Payment_Document_id = PD.Payment_Document_id) between isnull(@.p_Date_From, dbo.fnGetConstant('Collection_Constants', 'g_Min_Date'))

and isnull(@.p_Date_To, dbo.fnGetConstant('Collection_Constants', 'g_Max_Date'))

and ER.From_Currency_Code = PD.Currency_Code

and ER.To_Currency_Code = dbo.fnGetConstant('Collection_Constants', 'g_Currency_Code_Internal')

and ER.Exchange_List_Type = dbo.fnGetConstant('Collection_Constants', 'g_Exchange_List_Type')

and PD.Date_Of_Collection between ER.Start_Date and isnull(ER.End_Date, dbo.fnGetConstant('Collection_Constants', 'g_Max_Date'))

order by Date_of_Payment desc;

THANKS IN ADVANCE....PLEASEEEEEEEEEE CHANGE THIS CODE N TEL ME WHERE I HAVE TO WRITE THIS collate....coz the changes i made..they show the same error as b4
Regards,
Shuchi.

|||Without access to database it would be hard to tell exactly where to add "collate". As a general rule, any place we you are comparing columns that have different collations many need this keyword. I see in your code you have collate in the selected columns, this is not necessary. When you get an error compiling, just click on the error and it should take you to the offending statement.

Peter