Hi,
I have two databases
Db1 is in collation "SQL_Latin1_General_CP1_CI_AS" and
Db2 is in collation "Compatibility_52_409_30003".

Now I need to get records based on the values that i have in these two databases. I have used the following query for that
Select db1..table1.field1,db2..table2.field3 from db1..table1 inner join db2..table2 on db1..table1.field4 = db2..table2.field5 COLLATE SQL_Latin1_General_CP1_CI_AS.

When I run this query, it is returning Incorrect syntax near COLLATE.
Can anyone help me on this. I need to solve this issue @ the earliest.

Expecting a positive reply from my dear forum members.

Thanks in advance
Regards
Sudhakara.T.P.

HI,

Use COLLATE at Left side of comparison.

B. Anant

Hi Anant,

Its the same result. I tried using that both on left and right side of the = symbol, but it gives the same result saying "invalid syntax near collate"

Thanks & Regards
Sudhakara.T.P.

HI,

I m in hurry. Here is a query I am using in my produxtion code and its working.

Select ..............From......
Left OUTER join RCHSFIL RCH on pof.po COLLATE SQL_Latin1_General_CP1253_CS_AS = RCH.PO"

I feel u r doing a liitle mistake somewhere, I will be comming back and see. Till then seethis query. Hope u can fix it.

B. Anant

HI again,

Select db1.dbo.table1.field1,db2.dbo.table2.field3 from db1.dbo.table1 inner join db2.dbo.table2 on db2.dbo.table2.field5 COLLATE SQL_Latin1_General_CP1_CI_AS = db1.dbo.table1.field4

Try this.

B. Anant

Hi Ananth,
This is the query that I am using. Here, I have two databases. Intranet..Events is in collation Compatibility_52_409_30003
Pewcentral..staff is in collation SQL_Latin1_General_CP1_CI_AS
So, I need to get the count of records and here is the query that I am using.
Can you make out any difference in the query that you had sent me and the query that is here. When I run this query, i am getting the error invalid syntax near collate.

Select Count(*) as TotRec
FROM Intranet..Events E LEFT OUTER JOIN PewCentral..staff P on E.EditedBy collate SQL_Latin1_General_CP1_CI_AS = P.Initials

Thanks & Regards
Sudhakara.T.P.

HI,

Well seems u r using collation oppositly. Ur Query says to use "SQL_Latin1_General_CP1_CI_AS" with E. But U r saying E is with collation "Compatibility_52_409_30003".


Select Count(*) as TotRec
FROM Intranet.dbo.Events E LEFT OUTER JOIN PewCentral.dbo.staff P on P.INITIALS collate SQL_Latin1_General_CP1_CI_AS = E.EDITEDBY collate
Compatibility_52_409_30003

I am not sure about where u got coolation "Compatibility_52_409_30003"
I can't find this collation in SQL Server.

If still facing problems, I think u can change the fashion of query using WHERE IN etc.... Also u can go with SUM of Union ALLs. Further its the matter of digging the sea. Lets Keep trying.

By the way u can get the exact way to write this using Enterprice manage Query builder. I think u should create a dummy table in same database. Join these table through Enterprice Managers Graphical Query designer and see the generated query in SQL pane.

Gud luck.

B. Anant

Hi Anant,

I will try this.

Thanks for your support.

Regards
Sudhakara.T.P.

Title of this post says it all...

__________________
--Jeff Moden