SqlException (0x80131904): Line 28: Incorrect syntax near '('.]

Question

Please someone help me! I have an app running on windows 2003 with sql server 2005. When I try to deploy this some app in other server on windows 2003 with sql server 2000, some pages of the app shows the message bellow:

Server Error in '/' Application.
Line 1: Incorrect syntax near '('. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '('.

Source Error: 
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. 


Stack Trace: 

[SqlException (0x80131904): Line 1: Incorrect syntax near '('.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1948826
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844747
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
   System.Data.SqlClient.SqlDataReader.get_MetaData() +83
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
   System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +383

[EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.]
   System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +422
   System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute(ObjectContext context, ObjectParameterCollection parameterValues) +745
   System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) +162
   System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() +45
   System.Linq.Enumerable.FirstOrDefault(IEnumerable`1 source) +203
   System.Data.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__1(IEnumerable`1 sequence) +40
   System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle(IEnumerable`1 query, Expression queryRoot) +60
   System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression) +109
   System.Linq.Queryable.FirstOrDefault(IQueryable`1 source) +269
   SebraeFE.Models.Repositories.InterestsRepository.DeleteInterests(Interests interestsToDelete) +418
   SebraeFE.Models.Managers.InterestsManager.DeleteInterests(Interests interestsToDelete) +41
   SebraeFE.Models.Facades.InterestsFacade.DeleteInterests(Interests interestsToDelete) +41
   SebraeFE.Controllers.OportunidadeController.ApagarInteresse(Int32 Id) +265
   lambda_method(ExecutionScope , ControllerBase , Object[] ) +78
   System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +17
   System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +178
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +24
   System.Web.Mvc.<>c__DisplayClassa.<InvokeActionMethodWithFilters>b__7() +53
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) +258
   System.Web.Mvc.<>c__DisplayClassc.<InvokeActionMethodWithFilters>b__9() +20
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) +258
   System.Web.Mvc.<>c__DisplayClassc.<InvokeActionMethodWithFilters>b__9() +20
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList`1 filters, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +193
   System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +382
   System.Web.Mvc.Controller.ExecuteCore() +123
   System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) +23
   System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext) +7
   System.Web.Mvc.MvcHandler.ProcessRequest(HttpContextBase httpContext) +144
   System.Web.Mvc.MvcHandler.ProcessRequest(HttpContext httpContext) +54
   System.Web.Mvc.MvcHandler.System.Web.IHttpHandler.ProcessRequest(HttpContext httpContext) +7
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +406
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +76


Version Information: Microsoft .NET Framework Version:2.0.50727.3053; ASP.NET Version:2.0.50727.3053

an unsing the tecnologies bellow:

• asp.net mvc 1

• ADO.net and LINQ

please help me!

Answer 1

It sounds quite possible you're trying to use something that SQL 2005 has and SQL 2000 doesn't. Common table expressions?

Answer 2

It could be LINQ. It's not 100% compatible with Sql 2000.

Answer 3

It is quite difficult w/o having an idea of what your query looks like. This particular exception has got me before with SELECT TOP expressions. In SQL 2005+ a simple SELECT TOP could look like this:

SELECT TOP (50) FROM Events

However you will receive the exact syntax error exception you are speaking of in SQL 2000 because SQL 2000 doesn't allow parenthesis in the SELECT TOP expression. In other words it needs to look like this:

SELECT TOP 50 FROM Events

Another tip to try and get to the bottom of this is to get the exact SQL statement generated, then go into SQL 2000 Enterprise Manager and run it. When it gives you the same error in some cases EM will point you a little closer as to where the syntax issue exists (try splitting your query up on multiple lines)

Answer 4

Some SQL Server 2005 queries which contain 2005-specific extensions like ROW_NUMBER(), common table expressions [e.g. WITH x AS (...) SELECT ... FROM x], etc. are not backwards compatible with SQL Server 2000.

You must either ensure that your application runs only on SQL Server 2005 or you must rework your queries that are specific to SQL Server 2005 to support SQL Server 2000, 2005, and probably 2008 too for good measure. There is plenty of documentation available as to what the differences are in T-SQL syntax and semantics among the different versions of SQL Server.

I'm afraid you're not going to get much more specific help from anyone without revealing the actual query text. That would be like me asking you to correct my spelling error on line 12 of my 3rd year English term paper. No, you can't have it, but can you proofread it for me?

Answer 5

A really don't know yet. But i found that all the error are throwed when i call the method FirstOrDefault(). What could i do?

Answer 6

With it help the code that is executed in this page:

public void DeleteInterests(Interests interestsToDelete)
        {
            Interests original = (from m in _db.InterestsSet where m.Id == interestsToDelete.Id select m).FirstOrDefault();
            _db.DeleteObject(original);
            _db.SaveChanges();
        }

I'm doing something wrong?

Answer 7

I did'nt find the real reason. But it seems to be something about the linq and sql2000, exactly as Charles Conway said.

The code below works for me:

replace:

//...
    Interests original = (from m in _db.InterestsSet where m.Id == interestsToDelete.Id select m).FirstOrDefault();
//...

to:

//..
        Interests original = null;

        foreach (var i in from m in _db.InterestsSet where m.Id == interestsToDelete.Id select m){
            original = i;
            break;
        }
    //...

I don't like it, but works...

Answer 8

In the .edmx file you need to change the ProviderManifestToken to 2000 in the Schema tag to ProviderManifestToken="2000" (it will be ProviderManifestToken="2005")

ref: Entity Framework on Sql 2000 vs. Sql 2005 and ProviderManifestToken

Answer 9

open your model .ecmx with xml editor, and modify this

 ProviderManifestToken="2008"

to

 ProviderManifestToken="2000"

it might be the linq .first() that generates the sql script 'Top (1)' which works in sql2005, but should be 'top 1' with out '('and ')' in sql 2000

it works in my solution.