How do I link tables from two separate data files?

(I am very new to SQL)

I have an inventory management system (Radio Beacon) that works with my SAP Business One system.  I need to create a list of information from SAP with the bin location from RB.

the barcodes table contains 54 entries. all I need are 54 bin locations.

I am using MS SQL Server management Studio.
This is my query:

select partno, ItemCode, product, itemname,U_nsn, U_altno1, U_altno2, U_altno3, U_NINN, U_amgno, binlabel
from rbeacon..BarCodes, rbeacon..binlocat, kascarllc..oitm
where barcodes.partno = binlocat.product and barcodes.partno = oitm.itemcode

It produces this error:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP850_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.

I can't find a reference to this error with Google.


Michael, that looks like a very specific Microsoft SQL error message rather than a MySQL one (differnet product, confusingly similar name).  Alas - I know a bit about MySQL and you're asking about MS SQL.

However as an "educated guess" I would suggest that the one of the two comparisons you're making in the where clause is trying to compare two colums which don't have the same data structure - that's what it looks like to me.    To give you a parallel - you're trying to compare a rabbit with the number 14, and the answer is "it's not logical to even try to compare such different things".

Column type for SAP (Kascarllc.OITM.ItemCode) is PK,NVARCHAR(20)

Column type for RBeacon.binlocat.product and RBeacon.barcode.partno is VARCHAR(20)

So other than the fact that the SAP column will handle extra characters, why should these not work?  If this is indeed the problem, what is the function for stripping out all but the characters needed to make the match?

Michael, that looks like a very specific Microsoft SQL question rather than a MySQL one (differnet product, confusingly similar name).  I don't know the functions you need to convert the types.

The answer... In case you are interested...
Has to do with the language type set up in the data file, called collate.  Aparently when language types don't match you can tell it what kind to use.

In this case, placing the line "collate Latin1_General_CI_AI" (the language type used in one of the files) after the "on" statement corrects the problem.

This query works perfectly.

Select product AS 'item No', binlabel as 'Location',quantity as 'Qty',itemname as 'Description',U_NSN as 'NSN', U_altno1 as 'Alternate 1', U_altno2 as 'Alternate 2', U_altno3 as 'Alternate 3',U_NINN as 'NINN', U_amgno as 'AMG No.', partno as 'part no'
from rbeacon..binlocat A
inner join
KASCARLLC..OITM B
on ltrim(rtrim(a.product)) = ltrim(rtrim(b.itemcode))
collate Latin1_General_CI_AI
inner join rbeacon..barcode C
on ltrim(rtrim(b.itemcode)) = ltrim(rtrim(c.partno))
collate Latin1_General_CI_AI


Many thanks for following up and completing the thread, Michael; sorry I wasn't much help in this case - glad to add "collate" to my repatory if any future questions on Mssql stray this way.

---