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

ASP.NET MVC – SQL Server Compact (SQL Server CE) first load slow

I had a problem with very long first load of my ASP.NET MVC webpage with SQL Server Compact when I deployed it to IIS. In Visual Studio it was working quite OK, but after deploy to IIS load took about 40 seconds.

I browsed Internet without success, but after few days I found this blog which solved my problem! Thank you ErikEj!

I haven’t configured internet proxy for local system account which was needed for CRL checking during load (netsh winhttp set proxy proxyServer:port “<local>”)