March 16, 2009 at 4:15 am
Hi
if i execute the following querry
iam getting error can anybody please help
SELECT distinct
UM.userName as TseNames,
SSA.description as AreaNames,
SSPA.TSEId as TSEID
FROM
SABM_SALES_PLAN_HEADERS SSPH,userMaster UM, SABM_Territory ST,SABM_SALES_AREAS SSA,SABM_SALES_PLAN_ACCESSES SSPA
WHERE
Um.locationMasterId = ST.locationId
AND SSPH.locationid=ST.locationid
AND SSPH.StateId=ST.branchid
AND SSPH.AreaId=SSA.AreaId
AND UM.ID=SSPA.TSEID
AND SSPH.ID=SSPA.PlanId
AND SSA.branchid=ST.branchid
AND ST.BranchId = '15'
AND SSPH.ID=350
AND SSPH.locationid='8'
UNION
SELECT
TT.tsename as TseNames,
TT.areaname as AreaNames,
TT.tseid as TSEID
from
Tsetemp TT
error ----Cannot resolve collation conflict for UNION operation.
March 16, 2009 at 4:58 am
There's an excellent article by Steve Jones here[/url] which describes the cause and the solution to your problem. If you're still unsure after reading the article, then repost for additional help.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 16, 2009 at 5:49 am
Hi iam not able to get can u explain please
March 16, 2009 at 6:04 am
karan457 (3/16/2009)
Hi iam not able to get can u explain please
A published article such as this will explain your problem far better than a single post in the forums section. Are you unable to access the article, or unable to understand it?
If you are unable to access it via the link in my earlier post, then try pasting the following into your browser:
http://www.sqlservercentral.com/articles/T-SQL/61288/
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 16, 2009 at 6:07 am
The issue is that you have either table or column collation settings on the first set of data are different than on the second set. You'll need to look at the structures carefully to figure out where the differences are.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 16, 2009 at 7:57 am
Once you've noted the server & column collations, you can take care of your collation conflict by following this example:
DROP TABLE CollationTest1
CREATE TABLE [dbo].[CollationTest1](
[CT1ID] [int] IDENTITY(1,1) NOT NULL,
[CT1] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS
) ON [PRIMARY]
INSERT INTO CollationTest1 (CT1) VALUES ('varchar string 1 in CT1')
INSERT INTO CollationTest1 (CT1) VALUES ('varchar string 2 in CT1')
INSERT INTO CollationTest1 (CT1) VALUES ('varchar string 3 in CT1')
------------------------------------------------------------------------------
DROP TABLE CollationTest2
CREATE TABLE [dbo].[CollationTest2](
[CT2ID] [int] IDENTITY(1,1) NOT NULL,
[CT2] [varchar](30) COLLATE SQL_Latin1_General_Cp437_BIN
) ON [PRIMARY]
INSERT INTO CollationTest2 (CT2) VALUES ('varchar string 1 in CT2')
INSERT INTO CollationTest2 (CT2) VALUES ('varchar string 2 in CT2')
INSERT INTO CollationTest2 (CT2) VALUES ('varchar string 3 in CT2')
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- unmatched collation
SELECT CT1ID, CT1
FROM CollationTest1
UNION
SELECT CT2ID, CT2
FROM CollationTest2
/*
Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for UNION operation.
*/
-- unmatched collation
SELECT CT1ID, CT1, CT2
FROM CollationTest1
INNER JOIN CollationTest2 ON CT1ID = CT2ID
WHERE LEFT(CT1, 1) = LEFT(CT2, 1)
/*
Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
*/
------------------------------------------------------------------------------
-- Force column CollationTest1.CT1 to match collation of CollationTest2.CT2
SELECT CT1ID, CT1 COLLATE SQL_Latin1_General_Cp437_BIN
FROM CollationTest1
UNION
SELECT CT2ID, CT2
FROM CollationTest2
/*
Results:
CT1IDCT1
1varchar string 1 in CT1
2varchar string 2 in CT1
3varchar string 3 in CT1
1varchar string 1 in CT2
2varchar string 2 in CT2
3varchar string 3 in CT2
*/
-- Force column CollationTest2.CT2 to match collation of CollationTest1.CT1
SELECT CT1ID, CT1
FROM CollationTest1
UNION ALL
SELECT CT2ID, CT2 COLLATE SQL_Latin1_General_CP1_CI_AS
FROM CollationTest2
/*
Results:
CT1IDCT1
1varchar string 1 in CT1
2varchar string 2 in CT1
3varchar string 3 in CT1
1varchar string 1 in CT2
2varchar string 2 in CT2
3varchar string 3 in CT2
*/
-- matched collation
SELECT CT1ID, CT1, CT2
FROM CollationTest1
INNER JOIN CollationTest2 ON CT1ID = CT2ID
WHERE LEFT(CT1 COLLATE SQL_Latin1_General_Cp437_BIN, 1) = LEFT(CT2, 1)
/*
Results:
CT1IDCT1CT2
1varchar string 1 in CT1varchar string 1 in CT2
2varchar string 2 in CT1varchar string 2 in CT2
3varchar string 3 in CT1varchar string 3 in CT2
*/
-- matched collation
SELECT CT1ID, CT1, CT2
FROM CollationTest1
INNER JOIN CollationTest2 ON CT1ID = CT2ID
WHERE LEFT(CT1, 1) = LEFT(CT2 COLLATE SQL_Latin1_General_CP1_CI_AS, 1)
/*
Results:
CT1IDCT1CT2
1varchar string 1 in CT1varchar string 1 in CT2
2varchar string 2 in CT1varchar string 2 in CT2
3varchar string 3 in CT1varchar string 3 in CT2
*/
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 4, 2011 at 9:55 am
One small note - when aliasing a column, the collate hint goes after the column name but before the alias.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply