CTE in Pass Through Query

[Tom]

Can I use a Common Table Expression (CTE) in a pass through query? If
so, any special syntax?

I have a CTE that works fine in SQLSERVER, but I keep getting an error
message when moving to a pass through that seems to say I need to put
semicolons after each CTE.

Using Access2003 if that matters.

Thanks

[Bob Barrows]

Tom wrote:
> Can I use a Common Table Expression (CTE) in a pass through query?

Yes, if you are using SQL 2005 or later.. Passthrough queries pass sql
statements directly to the destination server with no attempt to parse them.

> If so, any special syntax?

If it works in SSMS, it will work in a passthrough query.

>
> I have a CTE that works fine in SQLSERVER, but I keep getting an error
> message when moving to a pass through that seems to say I need to put
> semicolons after each CTE.
>

So put the semicolons in ...
If multiple statements with CTEs are run in SSMS, you will get the same
error. Prior versions of SS did not require semicolons after each statement.
CTEs have changed that. In order for them to be properly parsed, the
statement prior to each WITH keyword must be ended with a semicolon.
Developers have taken to using
;WITH
to prevent that error, without going through their entire procedures to
delimit every statement. .

Semicolons will be required in a future version of SS, so you might want to
think about preparing for that day by starting to use semicolons after every
statement now.


If I have misinterpreted your situation, you will need to show us the sql
that raises the error.
Be aware that the ODBC driver you are using might be inserting some SET
statements prior to your sql - if they are not delimited with semicolons,
this might be the cause of your error.

PS. I would consider encapsulating your lengthy sql statement in a view or
stored procedure, regardless of whether or not the passthrough works. it is
more efficient to call a stored procedure than to repeatedly send a long sql
statement over the wire.