Problems with OPENQUERY

Problems with OPENQUERY

  
We are using linked server and OPENQUERY() for our remote databases, in this case an AS/400.

Many of them are working fine, but for some reason, This one doesn't work:

SELECT * FROM OPENQUERY(AS400, 'SELECT COMPANY,EMPLOYEE,EMP_STATUS FROM LAWAPP9PDB.DBHREMP WHERE COMPANY  = 88 AND EMPLOYEE = 1200392')

The message in the error log reads:
Error in advanced query GetOneEmployee in Employee_GetEmployeeStatus in iPawsDataServices (@SQL): Could not find stored procedure 'SELECT * FROM OPENQUERY(AS400, 'SELECT COMPANY,EMPLOYEE,EMP_STATUS FROM LAWAPP9PDB.DBHREMP WHERE COMPANY = 88 AND EMPLOYEE = 1200392')'.

If I copy and paste the full query text into Microsoft SQL Server Management Studio, it runs fine.
If I copy and paste the SELECT statement into AS/400 SQL GUI (the true location of the table), it runs fine.

The query is not making it to the AS/400. It does not show up in the log.

Anyone have any thoughts?
Hi Alan,

That's strange indeed. Are you properly escaping the quotes in your query? Such as:

  'SELECT * FROM OPENQUERY(AS400, ''SELECT COMPANY,EMPLOYEE,EMP_STATUS FROM LAWAPP9PDB.DBHREMP WHERE COMPANY = 88 AND EMPLOYEE = 1200392'')'

Please note the duplicate inner quotes used above.


Also, when you say the query is not making it to the AS/400, have you inspected the SQL Profiler tool? How is the platform actually sending the query to SQL Server?

Let me know if any of these helped you.

Regards,
Pedro