Strange issue with cross apply.

Question

Post reply

Strange issue with cross apply.

graham.smith

SSC Enthusiast

Points: 143
More actions
December 1, 2015 at 6:40 am

#322940

We are running 2008 R2, no service packs applied, and when trying to use cross apply
SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script] FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC
I am seeing this result
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.
However, and this is the bit I can't work out, I created a new database this morning and when I run the code against that database it executes with no problem at all. Any pre-existing database will not execute the cross apply, any new database is fine. I'm relatively new in this company and unsure if any changes have been made to the server properties recently. Does anyone know why this is happening and what we can do to modify the other db's so cross apply can be used?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

That makes me suspect that your existing databases have a low compatibility level - SQL Server 2000 (80), maybe?

John

graham.smith SSC Enthusiast Points: 143 More actions · Answer 2

Thanks John, that is possible, certainly some of our customers are still on 2005 and that wasn't something I'd thought of checking to be honest. I'll go check and see.

I've checked and you are in fact quite right. Thanks again and now to decide whether we can modify them for the rare occasions we may want to use cross apply.

Graham

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Is there any reason to leave them in the old compatibility mode? If there isn't, you ought to bring them all up to date to take advantage of the latest functionality. It won't be just that DMV (or rather DMF).

John

graham.smith SSC Enthusiast Points: 143 More actions · Answer 4

I agree. I was slightly shocked to find one customer still on SQL Server 2000, not even 2005, in fact. But then we use Delphi 5 to develop the applications themselves so we're not exactly out there at the cutting edge of technology 😀 I will speak to them and try to get them to agree that they need to start to move slightly further into the 21st century :w00t:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Most new features will work fine in a lower compat. It's just places where what is allowed has changed, like with APPLY where things will break on a lower compat mode.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

graham.smith SSC Enthusiast Points: 143 More actions · Answer 6

I've now found out why we can't go up yet. In the application there is code which is using the good old non-ansi joins. If I change the compatibility then the app itself breaks quite spectacularly. So short of rewriting the app to use ansi join syntax it's not going to happen for the foreseeable.

It is planned to rewrite at some stage, but when that gets done is not up to me sadly.

But cheers anyway.