ABP Suite Create Project - Incorrect syntax near the keyword 'WITH' error #6484 | Support

huseyin.cakirli created 9 months ago

ABP Framework version: v8.0.1
UI Type: MVC
Database System: EF Core (SQL Server)
Tiered (for MVC) or Auth Server Separated (for Angular): yes
Exception message and full stack trace: 'InvoiceSample.EntityFrameworkCore.InvoiceSampleDbContext'. Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'WITH'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Steps to reproduce the issue:

Hi,

i create a simple sample project with abp suite. Give "An error has occurred! Error detail not sent by server."

ERROR: 'InvoiceSample.EntityFrameworkCore.InvoiceSampleDbContext'. Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'WITH'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

I explain the step by step process below.

When I run the dbmigration application, I still get the following error.

I created a new project several times, both angullarjs and mvc projects, and I received the following error in db migration. I didn't create any table, I just created a new project.

2024-01-10 16:43:42.282 +03:00 [INF] Started database migrations... 2024-01-10 16:43:42.284 +03:00 [INF] Migrating schema for host database... 2024-01-10 16:43:44.421 +03:00 [INF] Executing host database seed... 2024-01-10 16:43:45.921 +03:00 [ERR] Failed executing DbCommand (5ms) [Parameters=[@__ef_filter__p_2='?' (DbType = Boolean), @__ef_filter__p_0='?' (DbType = Boolean), @__organizationUnitIds_0='?' (Size = 4000)], CommandType='"Text"', CommandTimeout='30'] SELECT [a].[RoleId] FROM [AbpOrganizationUnitRoles] AS [a] INNER JOIN ( SELECT [a0].[Id], [a0].[Code], [a0].[ConcurrencyStamp], [a0].[CreationTime], [a0].[CreatorId], [a0].[DeleterId], [a0].[DeletionTime], [a0].[DisplayName], [a0].[EntityVersion], [a0].[ExtraProperties], [a0].[IsDeleted], [a0].[LastModificationTime], [a0].[LastModifierId], [a0].[ParentId], [a0].[TenantId] FROM [AbpOrganizationUnits] AS [a0] WHERE (@__ef_filter__p_2 = CAST(1 AS bit) OR [a0].[IsDeleted] = CAST(0 AS bit)) AND (@__ef_filter__p_0 = CAST(1 AS bit) OR [a0].[TenantId] IS NULL) ) AS [t] ON [a].[OrganizationUnitId] = [t].[Id] WHERE (@__ef_filter__p_0 = CAST(1 AS bit) OR [a].[TenantId] IS NULL) AND [a].[OrganizationUnitId] IN ( SELECT [o].[value] FROM OPENJSON(@__organizationUnitIds_0) WITH ([value] uniqueidentifier '$') AS [o] ) 2024-01-10 16:43:45.936 +03:00 [ERR] An exception occurred while iterating over the results of a query for context type 'InvoiceSample.EntityFrameworkCore.InvoiceSampleDbContext'. Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'WITH'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__209_0(Task1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke() at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) --- End of stack trace from previous location --- at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread) --- End of stack trace from previous location --- at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable1.AsyncEnumerator.MoveNextAsync() ClientConnectionId:238fe4f5-cdfc-453f-9840-72c9b5e49f06 Error Number:156,State:1,Class:15 Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'WITH'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__209_0(Task1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke() at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) --- End of stack trace from previous location --- at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread) --- End of stack trace from previous location --- at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable1.AsyncEnumerator.MoveNextAsync() ClientConnectionId:238fe4f5-cdfc-453f-9840-72c9b5e49f06 Error Number:156,State:1,Class:15

Best regards

9 Answer(s)

0

EngincanV created 10 months ago
Support Team .NET Developer

Hi, as far as I understand this problem occurs either because you are using a local SQL server or a lower SQL server version. You can try to set the CompatibilityLevel as 120 and try again: https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/breaking-changes#mitigations
0

huseyin.cakirli created 10 months ago

Hi, This problem did not exist before upgrading to abp suite 8 version. ABP suite already creates CompatibilityLevel 120

I checked, all the db's I created with abp suite before have the same features.

Microsoft SQL Server 2014 - 12.0.2269.0 (X64) Express Edition (64-bit)

Best regards
0

huseyin.cakirli created 10 months ago

Hi again,

I installed Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Express Edition (64-bit) and it worked in this version.

CompatibilityLevel 160

I understand that you have to use SQL Server CompatibilityLevel (160) in ABP Framework 8 version (abp suite and cli) , is this correct?

Changing the SQL server version at the customer location is not an easy thing, most customers will want to continue using their existing licenses. Can there be another solution to this?

Best regards
0

EngincanV created 10 months ago
Support Team .NET Developer

Hi again,

I installed Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Express Edition (64-bit) and it worked in this version.

CompatibilityLevel 160

I understand that you have to use SQL Server CompatibilityLevel (160) in ABP Framework 8 version (abp suite and cli) , is this correct?

Changing the SQL server version at the customer location is not an easy thing, most customers will want to continue using their existing licenses. Can there be another solution to this?

Best regards

Hi, actually you would not need to set the compatibility level manually to 160 if the server would not be a local SQL server (it works as expected for other server types), I'm not sure why this is happening but I'll investigate it and take the related action (it seems there might be a bug in EF Core 8, but I'll check it deeply). And also I'll let you know about the problem and the solution.

Best regards.
1

maliming created 10 months ago
Support Team Fullstack Developer

hi

We have not changed any settings of the sql server, it is completely EF Core control.

EF Core will create a new database with some database settings. We haven't made any changes.

See https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/breaking-changes#high-impact-changes https://www.youtube.com/watch?v=g-6Fs9c5Ryo
0

huseyin.cakirli created 10 months ago

hi

We have not changed any settings of the sql server, it is completely EF Core control.

EF Core will create a new database with some database settings. We haven't made any changes.

See
https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/breaking-changes#high-impact-changes https://www.youtube.com/watch?v=g-6Fs9c5Ryo

Hi,

Let me show you step by step below what happens on 2 different SQL servers.

I experienced this problem in SQL Server 2014. I delete all the tests from the catalog and run them from scratch.

SQL Server 2014 (OLD VERSION SQL SERVER) PROBLEM IS HERE

localhost\sqlexpress This instance is "Microsoft SQL Server 2014 - 12.0.2269.0 (X64) Express Edition (64-bit)"

"ConnectionStrings": { "Default": "Server=.\sqlexpress;Database=InvoiceSample_SQL_Server_2014;Trusted_Connection=True;TrustServerCertificate=True" },

Run DBMigrator Application give bellow error message;

ERROR: Microsoft.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'WITH'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.'

But Create Catalog on SQL Server 2014 (OLD VERSION SQL SERVER)

But i cannot login web interface with the username and password because the user is not created. I don't do any manual action .

SQL Server 2022 (NEW VERSION SQL SERVER) There is no problem here.

localhost*sqlexpress01* This instance is "Microsoft SQL Server 2022 Express Edition (64-bit)"

"ConnectionStrings": { "Default": "Server=.\sqlexpress01;Database=InvoiceSample_SQL_Server_2022;Trusted_Connection=True;TrustServerCertificate=True" },

The only thing I did was to create a new project from scratch with abp suite, I did not add any entities.

I briefly summarize the problem again. When I create a new project with abp suite, I get the following error in SQL Server 2014.

ERROR: Microsoft.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'WITH'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.'

Best regards
0

EngincanV created 10 months ago
Support Team .NET Developer

Thanks for the comprehensive explanation but as you would understand there is nothing we can do at that point. This is not related to ABP Suite itself, but the database provider and the SQL server itself.
1

huseyin.cakirli created 10 months ago

Thanks for the comprehensive explanation but as you would understand there is nothing we can do at that point. This is not related to ABP Suite itself, but the database provider and the SQL server itself.

Hi, Thanks for quick response and help.

It is already clearly written in the document your team "@maliming" shared with me, EF Core 8 version no longer supports SQL Server 2014 and below.

https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/breaking-changes#high-impact-changes

New behavior Starting with EF Core 8.0, EF now generates SQL that is more efficient, but is unsupported on SQL Server 2014 and below.

It's clear that you have nothing to do about this. For now, we will continue to develop and work on the application with the old version abp (below version 8).

Thank you for your support

Best regards
0

EngincanV created 10 months ago
Support Team .NET Developer

Thanks for the comprehensive explanation but as you would understand there is nothing we can do at that point. This is not related to ABP Suite itself, but the database provider and the SQL server itself.

Hi, Thanks for quick response and help.

It is already clearly written in the document your team "@maliming" shared with me, EF Core 8 version no longer supports SQL Server 2014 and below.

https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/breaking-changes#high-impact-changes

New behavior Starting with EF Core 8.0, EF now generates SQL that is more efficient, but is unsupported on SQL Server 2014 and below.

It's clear that you have nothing to do about this. For now, we will continue to develop and work on the application with the old version abp (below version 8).

Thank you for your support

Best regards

Thanks for your understanding. I'll add this to our documentation. Best regards.

Have an answer to this question? Log in and write your answer.