Hi,
I'm experiencing some problems! I’ve just begun querying my data flex data source, which I’ve set up as a linked server on our MS SQL Server (2000). Simple table SELECTs are working fine, but as soon as I try to perform a JOIN the statement fails with the error below. I’ve checked my syntax against the documentation included with a driver, and it seems spot-on. I’m at a loss!

What I send:
select * from openquery( CBA, 'select apmaster.record_number from apmaster INNER JOIN aptran ON apmaster.number = aptran.apm#  order by record_number')

Error Returned:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Flex/ODBC] Unexpected extra token: JOIN]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80040e14].

... can anyone help point out where I’m going wrong?

Thanks in advance,
Casey.
Casey.raats@moffat.co.nz

I can't run your queries as I don't have your data, but the standard format goes like this:

SELECT CUSTOMER.NUMBER, ORDERHEA.ORDER_DATE, ORDERHEA.TOTAL,
CUSTOMER.CUSTOMER, ORDERHEA.ORDER_NUMBER
FROM CUSTOMER
INNER JOIN ORDERHEA ON CUSTOMER.NUMBER=ORDERHEA.CUSTOMER_NUMBER
ORDER BY CUSTOMER.CUSTOMER

Try:

select apmaster.record_number
FROM apmaster
INNER JOIN aptran ON apmaster.number = aptran.apm# 
ORDER BY apmaster.record_number

You can also try turning on FlexODBC detail tracing and check what is actually being sent by SQL Server to FlexODBC.