FMTONLY added incorrectly to a common table expression by JDBC dri

[Piyevsky@discussions.microsoft.com Alex Piyevsky]

Hi All,

I am using sqljdbc 2.0 driver against both SQL Server 2008 and 2005

I'm sending the following request to the SQL Server via the JDBC driver:

WITH X AS (SELECT * FROM dbo.BODY) SELECT * FROM X;

The following error is returned:
: (0) [ ] A processing error Incorrect syntax near the keyword 'with'
: . If this statement is a common table expression or an xmlnamespaces cla
: use, the previous statement must be terminated with a semicolon. has occ
: urred.

When I look at the Profiler trace on the server, I see the following
statement being received:
SQL:BatchStarting set fmtonly on WITH X AS (SELECT * FROM BODY) SELECT *
FROM X set fmtonly off

This statement needs a semicolon before WITH and after X to work, the driver
seems to be adding the settings with incorrect syntax.

Please advise, am I using the wrong syntax or is this perhaps a bug?

Thank you.

[Evan T. Basalik (MSFT)]

Your use of the AS clause implies you are using a TSQL alias.
Unfortunately, the 2.0 driver does not work properly with aliases. At this
time, it does not look like it will be fixed in the near term.

However, if you want this fixed in the near term, please open a case with
our support team and we can work with you to request a hotfix.

Evan