asked
Incorrect syntax near BEER
Hi, I have a link server from SQL connect to AS400 configured to run OPENQUERY to retrieve data from AS400. I encountered "Incorrect syntax near BEER" syntax error from SQL SSMS. I can't figure out why this simply condition would give me an error. If I remove the WHERE DEPARTMENT = ''BEER'' condition, it retrieve data from AS400 with success. Does anyone know how to correct below syntax error? SELECT * FROM OPENQUERY(AS400,' SELECT DEPARTMENT, CLASS FROM MYLIB.WORKTBL01 WHERE DATE = 20171217 AND DEPARTMENT = ''BEER'' ') Msg 102, Level 15, State 1, Line 7 Incorrect syntax near 'BEER'.
Which driver are you using to setup the linked server connection?
I am using iSeries Access ODBC driver (32-bit) version 13.64.10.00. On the Linked Server, It is using MSDASQL with Dynamic parameter, Nested queries, Level zero only, Allow inprocess, and Supports Like operator checked.
@BrianHo168 Why is the date value not enclosed in quotes? Such script cannot possibly run in SQL Server, maybe it cannot run in AS400 either. If the DATE column is datetime then the script would cause message 8115 (Arithmetic overflow), and if the column is date - message 529 (Explicit conversion from data type int to date is not allowed). Is it at all possible that the actual error is related to the similar problem in AS400, but the error message which comes back from the openquery is somewhat misleading?
Date on the AS400 side is defined as numeric which is fine without quotes. I figured out the issue which is related to the SQL parser side. I put triple single quotes before and after BEER resolved the issue. Thanks for looking into this for me. I am all good now.
18 People are following this question.
