OK - I’ve just had one of those arcane issues with an esoteric combination of technologies that threw up nothing on Google - hence this post!

The Scenario

I have a WCF Data Services service with a database back end that is running SQL 2000 in production, but I had taken a backup and was running on my local SQL 2008 R2 Express instance.

I also have an ASP.NET MVC 2 web app that is a client of the data service.

All was well testing on both on my local and staging (also SQL 2000) databases when using the standard http / web request stuff.

I then re-factored the MVC project to use the Data Services Client Library from within the model, so that some text boxes were populated as soon as the page has loaded, rather than populating them from the client-side using JSON as I had before. All was going well until I pushed it out to the staging server which talks to a SQL 2000 instance and all of a sudden I started getting a “Incorrect syntax near the keyword ‘AS’” error message from the data service.

I dusted off SQL Server Profiler and ran it against the staging DB to see the actual SQL query that was being generated by the Data Service and noticed the first line was SELECT TOP (1) FROM… I copied and pasted the query into query analyzer and sure enough, got the same error message that I was receiving from the data services client.

By just removing the brackets from TOP (1) I could get the query to run.

Fortunately for me, I was explictly calling the .FirstOrDefault() method on my linq query from the Data Services client library as I was only expecting one record to come back from the query, so by just removing the call in my code I was able to get the client working again.

It seems SQL 2000 is not compatible with the syntax that is generated by the entity framework for SELECT TOP (X)?