This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
I have upgraded our project to use LS5, and changed the database type to SQL Server 2012. At the same time I upgraded my local SQL Express from 2008 R2 to Express 2012. The changed the compatibility level of the actual database to 11 (2012). When I run my app, using the LINQ FirstorDefault() I receive the following error. System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'OFFSET'.
Invalid usage of the option NEXT in the FETCH statement.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at ...?()
at Mindscape.LightSpeed.Profiling.Interceptor.ExecuteCommand(Func The actual command at that line is:
I am guessing the app is using 2012 syntax, against what appears to be a 2008 R2 database. But the server is a 2012 Express server, and the database is 2012 compatible. If I change the connection database type back to 2008, it works fine, even though the database is a 2012. I have obviously missed a step, but I am unable to see where. Hopefully someone can help. Thanks |
|
|
I would enable logging and capture the SQL being generated. From the error message it actually sounds more like the syntax of the statement might be invalid so I would check that first just to confirm its not something invalid.
|
|
|
Hi Jeremy I switched on the logging, the actual SQL generated is below.
|
|
|
Ok thanks, that looks fine. If I create a 2008 compatible database on a 2012 server I can still use that syntax so that would seem to indicate your server may still believe its running 2008 - what does a SELECT @@version return you?
|
|
|
Hi Jeremy, I tried what you suggested, see below for the result. Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (Intel X86)
Dec 28 2012 19:06:41
Copyright (c) Microsoft Corporation
Express Edition on Windows NT 6.1 As you can see it is running SQL 2012. Thanks |
|
|
And if you run that query you capture above manually via Management Studio does it give the same error?
|
|
|
If I run the above in Management Studio it returns the correct rows. |
|
|
That doesn't seem to reconcile then as there should be no difference between what is being send via LightSpeed and what you are running in Management Studio for that particular query - can you recheck that the app is actually talking to the server you think it is? e.g. Double check the connection string and if that seems ok then attach a quick profiler session and check that the statement is being executed (and then that its the same statement that we think it is).
|
|
|
I owe you guys a big apology. It turns out the whilst at the start of the program when we were checking the connection we were connected to the 2012 database, however, after a user logs in, it was switching the connection string to a different database, and the one it switched to was an old 2008 R2 version. I am very sorry for wasting your time. Many thanks |
|
|
No problem! Good to hear its all sorted out now - was starting to turn into a bit of a mystery there :)
|
|