How to resolve sql collation error.

Posted by Karthik2010_Mca under Sql Server on 9/6/2013 | Points: 10 | Views : 6713 | Status : [Member] | Replies : 15
Hi All,
How to resolve sql collation error.

When I execute my sp. I have facing this error "Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict".

Karthik


Responses

Posted by: Bandi on: 9/6/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Check the collation of those two columns and change one of them to other collation... Post us the code which causes conflict
To get the collation of column name/Database

SELECT name, collation_name

FROM sys.databases
WHERE name = N'DBName';


SELECT name, collation_name
FROM sys.columns
WHERE name = N'ColumnName';


--Detailed list of column name and collation name
USE DBname 

GO
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'TableNAme')
AND name = 'ColumnName'


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/6/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
for example,
SELECT ID

FROM ItemsTable
INNER JOIN AccountsTable
WHERE ItemsTable.Collation1Col = AccountsTable.Collation2Col


ERROR:
If columns ItemsTable.Collation1Col and AccountsTable.Collation2Col have different collation, it will generate the error “Cannot resolve collation conflict for equal to operation“.

To resolve the collation conflict add following keywords around “=” operator.

SELECT ID

FROM ItemsTable
INNER JOIN AccountsTable
WHERE ItemsTable.Collation1Col COLLATE DATABASE_DEFAULT
= AccountsTable.Collation2Col COLLATE DATABASE_DEFAULT


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Allemahesh on: 9/6/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Dear Karthik,

Can you post some query so that we can help you.

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/6/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
By using 1st reply list the collation names of all columns that are in procedure
And then use 2nd reply to tweet the procedure in right direction...
If you face any problem post us back..
SELECT ID

FROM ItemsTable
INNER JOIN AccountsTable
WHERE ItemsTable.Collation1Col COLLATE SameCollationNameForBothColumns
= AccountsTable.Collation2Col COLLATE SameCollationNameForBothColumns


Refer this link:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/0d0a2659-edb5-4f22-b602-5db29e9b1c4d/implicit-conversion-of-char-value-to-varchar-cannot-be-performed-because-the-collation-of-the-value

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Karthik2010_Mca on: 9/10/2013 [Member] Starter | Points: 25

Up
0
Down
Hi All,

Here I posted my updated code.

S_Session_Name varchar no 500 yes no yes Latin1_General_CI_AI --T Table Collation
S_Session_Code varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS --B Table Collation

INSERT INTO #temp1
SELECT
H.[I_Batch_Content_Details_ID] AS I_Batch_Content_Details_ID,
T.I_Term_ID,
T.S_Term_Name,
T.I_Module_ID,
T.S_Module_Name,
T.I_Session_ID,
T.S_Session_Name,
T.S_Session_Name+' ('+B.S_Session_Code+')' as S_Session_Code, -- I think, I got error this line.
T.S_Session_Topic,
H.S_Session_Alias,
H.S_Session_Chapter,
H.S_Session_Description,
H.S_Content_URL,
@iBatchID,
H.B_IsActive ,
H.I_Folder_Id
FROM
T
INNER JOIN dbo.T_Session_Master B
ON T.I_Session_ID = B.I_Session_ID
LEFT OUTER JOIN dbo.T_Batch_Content_Details H
ON T.I_Term_ID = H.I_Term_ID
AND T.I_Module_ID = H.I_Module_ID
AND T.I_Session_ID = H.I_Session_ID
AND H.I_Batch_ID = @iBatchID
AND H.I_Session_ID = ISNULL(@iSessionID,H.I_Session_ID)
AND H.I_Module_ID = ISNULL(@iModuleID,H.I_Module_ID)
AND H.I_Term_ID = ISNULL(@iTermID,H.I_Term_ID)

INSERT INTO #temp1
SELECT A.I_Batch_Content_Details_ID ,
B.I_Term_ID ,
C.S_Term_Name,
B.I_Module_ID ,
D.S_Module_Name,
B.I_Session_ID ,
B.S_Session_Name,
NULL,
B.S_Session_Topic ,
A.S_Session_Alias ,
A.S_Session_Chapter ,
A.S_Session_Description ,
A.S_Content_URL ,
B.I_Batch_ID,
A.B_IsActive,
A.I_Folder_Id
FROM T B
INNER JOIN dbo.T_Term_Master C
ON B.I_Term_ID = C.I_Term_ID
INNER JOIN dbo.T_Module_Master D
ON B.I_Module_ID = D.I_Module_ID
LEFT OUTER JOIN dbo.T_Batch_Content_Details A
ON B.S_Session_Name = A.S_Session_Name
AND B.S_Session_Topic = A.S_Session_Topic
AND A.I_Batch_ID = @iBatchID
WHERE B.I_Session_ID IS NULL
AND C.I_Term_ID = ISNULL(@iTermID,C.I_Term_ID)
AND D.I_Module_ID=ISNULL(@iModuleID,D.I_Module_ID)


Karthik

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/10/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Convert S_Session_Code collation to S_Session_Name collation....
Check the highlighted part for the conversion..........

INSERT INTO #temp1 

SELECT
H.[I_Batch_Content_Details_ID] AS I_Batch_Content_Details_ID,
T.I_Term_ID,
T.S_Term_Name,
T.I_Module_ID,
T.S_Module_Name,
T.I_Session_ID,
T.S_Session_Name,
T.S_Session_Name+' ('+B.S_Session_Code COLLATE Latin1_General_CI_AI +')' as S_Session_Code, -- I think, I got error this line.
T.S_Session_Topic,
H.S_Session_Alias,
H.S_Session_Chapter,
H.S_Session_Description,
H.S_Content_URL,
@iBatchID,
H.B_IsActive ,
H.I_Folder_Id
FROM
T
INNER JOIN dbo.T_Session_Master B
ON T.I_Session_ID = B.I_Session_ID
LEFT OUTER JOIN dbo.T_Batch_Content_Details H
ON T.I_Term_ID = H.I_Term_ID
AND T.I_Module_ID = H.I_Module_ID
AND T.I_Session_ID = H.I_Session_ID
AND H.I_Batch_ID = @iBatchID
AND H.I_Session_ID = ISNULL(@iSessionID,H.I_Session_ID)
AND H.I_Module_ID = ISNULL(@iModuleID,H.I_Module_ID)
AND H.I_Term_ID = ISNULL(@iTermID,H.I_Term_ID)

INSERT INTO #temp1
SELECT A.I_Batch_Content_Details_ID ,
B.I_Term_ID ,
C.S_Term_Name,
B.I_Module_ID ,
D.S_Module_Name,
B.I_Session_ID ,
B.S_Session_Name,
NULL,
B.S_Session_Topic ,
A.S_Session_Alias ,
A.S_Session_Chapter ,
A.S_Session_Description ,
A.S_Content_URL ,
B.I_Batch_ID,
A.B_IsActive,
A.I_Folder_Id
FROM T B
INNER JOIN dbo.T_Term_Master C
ON B.I_Term_ID = C.I_Term_ID
INNER JOIN dbo.T_Module_Master D
ON B.I_Module_ID = D.I_Module_ID
LEFT OUTER JOIN dbo.T_Batch_Content_Details A
ON B.S_Session_Name = A.S_Session_Name
AND B.S_Session_Topic = A.S_Session_Topic
AND A.I_Batch_ID = @iBatchID
WHERE B.I_Session_ID IS NULL
AND C.I_Term_ID = ISNULL(@iTermID,C.I_Term_ID)
AND D.I_Module_ID=ISNULL(@iModuleID,D.I_Module_ID)


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Karthik2010_Mca on: 9/10/2013 [Member] Starter | Points: 25

Up
0
Down

Hi,

After I change the updated code, I got below mentioned error.

Msg 468, Level 16, State 9, Procedure uspGetBatchContentFromBatchID, Line 168
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.


INSERT INTO #temp1
SELECT
H.[I_Batch_Content_Details_ID] AS I_Batch_Content_Details_ID,
T.I_Term_ID,
T.S_Term_Name,
T.I_Module_ID,
T.S_Module_Name,
T.I_Session_ID,
T.S_Session_Name,
T.S_Session_Name+' ('+B.S_Session_Code COLLATE Latin1_General_CI_AI +')' as S_Session_Code,
T.S_Session_Topic,
H.S_Session_Alias,
H.S_Session_Chapter,
H.S_Session_Description,
H.S_Content_URL,
@iBatchID,
H.B_IsActive ,
H.I_Folder_Id
FROM
T
INNER JOIN dbo.T_Session_Master B
ON T.I_Session_ID = B.I_Session_ID
LEFT OUTER JOIN dbo.T_Batch_Content_Details H
ON T.I_Term_ID = H.I_Term_ID
AND T.I_Module_ID = H.I_Module_ID
AND T.I_Session_ID = H.I_Session_ID
AND H.I_Batch_ID = @iBatchID
AND H.I_Session_ID = ISNULL(@iSessionID,H.I_Session_ID)
AND H.I_Module_ID = ISNULL(@iModuleID,H.I_Module_ID) -- Got error this line.
AND H.I_Term_ID = ISNULL(@iTermID,H.I_Term_ID)

Thanks


Karthik

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/10/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
>>AND H.I_Module_ID = ISNULL(@iModuleID,H.I_Module_ID) -- Got error this line.

Hi Karthik,
Same thing as above...
Change one of the column name to other collation type...
AND H.I_Module_ID = ISNULL(@iModuleID COLLATE CollationOfModule_IDColumn   ,H.I_Module_ID) 



Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Karthik2010_Mca on: 9/10/2013 [Member] Starter | Points: 25

Up
0
Down

Hi Chandu,

After change, I got this error.

Msg 447, Level 16, State 0, Procedure uspGetBatchContentFromBatchID, Line 136
Expression type int is invalid for COLLATE clause.

CREATE TABLE #temp1
(
I_Batch_Content_Details_ID INT,
I_Term_ID INT,
S_Term_Name VARCHAR(500),
I_Module_ID INT,
S_Module_Name VARCHAR(500),
I_Session_ID INT,
S_Session_Name VARCHAR(500),
S_Session_Code VARCHAR(550),
S_Session_Topic VARCHAR(1000),
S_Session_Alias VARCHAR(250),
S_Session_Chapter VARCHAR(250),
S_Session_Description VARCHAR(500),
S_Content_URL VARCHAR(500),
I_Batch_ID INT,
B_IsActive BIT , --error Point out here
I_Folder_Id INT
)


Karthik

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/10/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
You supposed to convert only strings collation, but not the int, smallint,bit

what are the datatypes of the fields @iModuleID and H.I_Module_ID ?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/10/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi Karthik,
Once post me the collations of tempdb and your current database where the tables exist by using following command
SELECT name, collation_name

FROM sys.databases
where name in ('tempdb', 'Db1')

I doubt that the tempdb & your current database collations are different.. In that case its better to define collation at the time of temp table creation

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Karthik2010_Mca on: 9/10/2013 [Member] Starter | Points: 25

Up
0
Down

Hi Chandu,

I have posted my sp.

Thanks
 Download source file

Karthik

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/10/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
I'm sorry.. I doesn't have access to download file in my office..
Can you post in "Description of Reply" only?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/12/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Have you fixed the issue? If not yet post us back with the clear explanation; otherwise mark it as answer

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/18/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Make sure that you have same collation for both tempdb and current database where the stored procedure exists....
This should resolve the above errors..
If you sorted out the issues Mark it as answer

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response