Inner join for three tables from two different servers

Question

Inner join for three tables from two different servers

Feebie

Right there with Babe

Points: 785
More actions
January 8, 2010 at 11:13 am

#99983

hi,
i have to bring data from three diffrent tables which are in two different servers.
and i had my query like this...
SELECT * FROM [SCS Navision Test].[dbo].[110309 Test SCS$Purchase Header] a ,
[SCS Navision Test].[dbo].[110309 Test SCS$Vendor] b , [SCS_Portal].[dbo].[tblpartitem] c
WHERE a.[Document Type] = 1 AND
a.[No_] IN (SELECT ponmber FROM tblpartitem WHERE tblpartitem.POStatus = 'Complete') AND
a.[Buy-from Vendor No_] IN (Select No_ from [SCS Navision Test].[dbo].[110309 Test SCS$Vendor] WHERE [SCS Vendor Portal] = 1)
in the above query i need to get rows from purchase header table with the where conditions i have.. it is giving me error like
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CS_AS" in the equal to operation.
please see the attached images for table values and help me how to write a query.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Feebie Right there with Babe Points: 785 More actions · Answer 1

Sorry, correction...

i needed to get data from a single tbale by checkinf with 2 different tables (in whcih one of the table is on other server)...

Garadin One Orange Chip Points: 29613 More actions · Answer 2

Check out the COLLATE keyword in BOL.

Also, you don't have any criteria linking these tables, so you're performaing a multi server cross join. Sounds dangerous.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

S3ssion SSChasing Mays Points: 602 More actions · Answer 3

Read this on how to resolve some collation conflicts:

http://www.sqlusa.com/bestpractices2005/collatedatabasedefault/

| If in Doubt...don't do it!! |

Azzu Ten Centuries Points: 1363 More actions · Answer 4

Hi

Whenever joining two columns of character type ex varchar/nvarchar the collation issue may happen.

Use Where ColA = ColB Collate <Collation Name>

Hope this helps. If you need any more information refer to BOL.

Thanks.

Chuck Hottle SSCarpal Tunnel Points: 4777 More actions · Answer 5

I noticed that this seems to involve Dynamics NAV, so I thought I'd throw in my two cents about collation and NAV. We are implementing NAV 2009 and have a had a number of issues with collation. You can change the database collation in NAV. We still use SQL_Latin1_General_CP1_CI_AS for our database servers. In NAV, with either create database or alter database if you choose "Western-European dictionary sort order, code page 1252, case-insensitive, accent-sensitive (52)" from the list of SQL collations it generates the “SQL_Latin1_General_CP1_CI_AS” collation for the database and associated objects. Using the COLLATE keyword for queries can get a little old.