SqlException: Incorrect syntax near ‘OFFSET’.

If you are developing MVC application with Entity Framework using localDB database on your developer machine and then you put your product to the production server with older version of SQL Sever (2008R2 in my case) you can get exception below:

SqlException (0x80131904): Incorrect syntax near 'OFFSET'. 
Invalid usage of the option NEXT in the FETCH statement. 
Incorrect syntax near 'OFFSET'.

The issue is caused by the fact that SQL Server 2008 R2 doesn’t support SQL command OFFSET which can be called by Entity Framework.

To fix this behavior, you need to open your model .EDMX file in some XML editor and edit ProviderManifestToken from version 2012 to version 2008. Compile and it will be working again 🙂

<Schema Namespace="ComplianceRiskModel.Store" ProviderManifestToken="2012" Provider="System.Data.SqlClient" ...>
Advertisements

One thought on “SqlException: Incorrect syntax near ‘OFFSET’.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s