question

BrianHo168 avatar image
BrianHo168 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'.
syntax
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Which driver are you using to setup the linked server connection?
0 Likes 0 ·
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.
0 Likes 0 ·
@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?
0 Likes 0 ·

1 Answer

·
BrianHo168 avatar image
BrianHo168 answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.