Collation Error while joining the tables. can somebody help.

Ashutosh Gupta
Ashutosh Gupta used Ask the Experts™
on
Collation Error while joining the tables. can somebody help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Error is coming because the collation of the columns are different.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
Mayank is correctly said that this is because the collation of the columns are different. Please try this sample-

Table Creation and data generation-

CREATE TABLE A1
(
	Vals VARCHAR(20) COLLATE SQL_Latin1_General_CP850_CI_AS
)
GO

INSERT INTO A1 VALUES ( 'Pawan' )
GO

CREATE TABLE A2
(
	Vals VARCHAR(20)
)
GO

INSERT INTO A2 VALUES ( 'Pawan' )
GO

Open in new window


Error - Trial
/*------------------------
SELECT * FROM A1 INNER JOIN A2 ON A1.Vals = A2.Vals
------------------------*/
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP850_CI_AS" in the equal to operation.

Open in new window


SOLUTION 1 : Provide the Collation on the fly.

SELECT * FROM A1 INNER JOIN A2 ON A1.Vals COLLATE SQL_Latin1_General_CP1_CI_AS = A2.Vals COLLATE SQL_Latin1_General_CP1_CI_AS

Open in new window


SOLUTION 2  : Move to the default collation of the database.

SELECT * FROM A1 INNER JOIN A2 ON A1.Vals COLLATE DATABASE_DEFAULT = A2.Vals COLLATE DATABASE_DEFAULT

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/
Vals                 Vals
-------------------- --------------------
Pawan                Pawan

(1 row(s) affected)

Open in new window

Author

Commented:
Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial