Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sid8181
Starting Member
2 Posts |
 Posted - 2010-02-09 : 08:20:00
|
| Hi,I have a table named as Territory with the following structure - Table Name:TerritoryColumns:TerritoryIdNameStatus (1/2){My assumption is 1 corresponds to Draft and 2 corresponds to Published]Type (1/2){My assumption is 1 corresponds to Territory and 2 corresponds to Template]I am writing a stored procedure to fetch those territories which have a possible value of Published for a territory and Template as the type of the territory. This is working fine for me on my local machine. When I am trying to do the same using the cross database implementation, I am getting an error Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation"This is happening in the simple select statement which is like this - SELECT territoryid, name, SELECT case when type = 1 then 'template' else 'territory' end) as Type,SELECT case when status= 1 then 'draft' else 'published' end) as Statusfrom territorymanager.I cann't change the collation of the column now due to some constraints. I have to do it with the stored procedure only. Please suggest what needs to be done in this case. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-09 : 08:26:28
|
| use COLLATE DATABASE_DEFAULT on each sides of equal to operation after column names |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-09 : 09:23:56
|
| "use COLLATE DATABASE_DEFAULT on each sides of equal to operation after column names"Only needed on one side, isn't it? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-02-09 : 09:48:50
|
Yes I think on one side is enough - too.But the given example query looks not like it is able to get this kind of error...The error should be about unexpected brackets...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-09 : 09:54:38
|
quote:
Originally posted by Kristen
"use COLLATE DATABASE_DEFAULT on each sides of equal to operation after column names"Only needed on one side, isn't it?
One side is enough. But I dont think it was caused in posted query part. thats why i suggested this keeping in mind that it might be a column column comparison from different tables |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-09 : 10:38:23
|
| Just to clarify I was meaning:WHERE Column1 COLLATE SomeCollationA = Column2 COLLATE SomeCollationBis clearly daft! (it is, isn't it? trying to force difference collations on both sides) and thus the COLLATE is only required on one side:WHERE Column1 COLLATE CollationForBothSides = Column2orWHERE Column1 = Column2 COLLATE CollationForBothSides |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-02-09 : 10:58:25
|
SELECT territoryid, name, case when type = 1 then 'template' else 'territory' end as Type,case when status= 1 then 'draft' else 'published' end as Statusfrom territorymanager
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
|
|
Collation Conflict Issues