I've got a 2008 SQL Server linked to Oracle 10 server and when writing straight queries with OpenQuery everything works fine. However I need to create proc with parameters as I will have to perform several additional steps after I get this data.
I'm getting an error that makes no sense to me, so I need an extra set of eyes to help. I've had the code print off the dynamic SQL and it runs just fine in Oracle, but when I wrap it in the OpenQuery It fails with an incorrect syntax error at a spot that just can't be the issue. The error is in the WHERE clause. Message is Incorrect syntax near 'INCIDENT'.
Anybody got an idea about what's going on?
SELECT * FROM OPENQUERY(MAXPROD,'
LEFT OUTER JOIN (
MIN(CHANGEDATE) AS CHANGEDATE
TKSTATUS.CLASS = 'INCIDENT'
AND TKSTATUS.STATUS IN ('RESOLVED','CAN','CLOSED')
GROUP BY TICKETID) STATUSCHANGEDT
ON INC.TICKETID = STATUSCHANGEDT.TICKETID
LEFT OUTER JOIN WOCHANGE WC
ON INC.TICKETID = WC.ORGID
INC.VENDOR IS NOT NULL
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'INCIDENT'.