Getting error when trying to use OPENQUERY Msg 7321, Level 16, State 2, Line 1

Question

When I run this query:

SELECT *   
FROM OPENQUERY([XXX], 'SELECT * 
                       FROM Database.Table 
                       WHERE (MBCONO=650) AND MBCUNO LIKE a%' )

I get the error:

OLE DB provider "DB2OLEDB" for linked server "XXX" returned message "Token %ŸFOR SKIP WITH FETCH ORDER UNION EXCEPT OPTIMIZE SQLSTATE: 42601, SQLCODE: -104".

Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "SELECT * FROM Database.Table WHERE (MBCONO=650) AND MBCUNO LIKE a%'" for execution against OLE DB provider "DB2OLEDB" for linked server "LAWSON".

I can run the same query without AND MBCUNO LIKE a% successfully.

Answer 1

I would suggest you change your original SQL Statement as follows:

SELECT * 
FROM OPENQUERY([XXX], 'SELECT * FROM database.table where left(MBCUNO, 1) = ''a'' and (MBCONO=650) ' )

This has the two variable swopped around, and the like % exchanged for a left(mbcuno,1).

Ensure that all your table & column references are correct