System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'mySchema'

[KingR1]

Description

After some version I started receiving error Incorrect syntax near 'mySchema'. I have DB structure that for each client has its own DB schema. So I'm passing schema name and some items to save in the table. As result of BulkInsert I got an error.

Exception

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'mySchema'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at .(DbCommand , BulkOperation , Int32 )
   at .( )
   at .Execute(List`1 actions)
   at Z.BulkOperations.BulkOperation.Execute()
   at Z.EntityFramework.Extensions.EntityBulkOperation`1.BulkInsert()
   at .BulkInsert[T](BulkOperation`1 this, DbContext context, List`1 list, Boolean isManager, List`1 entitiesToUpdate, Type type, String typeName)
   at .`1.(SchemaEntityType )
   at System.Collections.Generic.List`1.ForEach(Action`1 action)
   at .BulkInsert[T](DbContext this, BulkOperation`1 bulkOperation, IEnumerable`1 entities2, List`1 entitiesToUpdate)
   at DbContextExtensions.BulkInsert[T](DbContext this, IEnumerable`1 entities, Action`1 bulkOperationFactory)
   at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at DbContextExtensions.`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MyApp.DataAccess.Sql.EntityFramework.DbSession.<>c__DisplayClass29_0`1.<<BulkInsertAsync>b__1>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Polly.RetrySyntaxAsync.<>c__DisplayClass33_0.<<WaitAndRetryAsync>b__1>d.MoveNext() in C:\projects\polly\src\Polly.Shared\Retry\RetrySyntaxAsync.cs:line 807
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Polly.Retry.RetryEngine.<ImplementationAsync>d__1`1.MoveNext() in C:\projects\polly\src\Polly.Shared\Retry\RetryEngineAsync.cs:line 33
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Polly.Retry.RetryEngine.<ImplementationAsync>d__1`1.MoveNext() in C:\projects\polly\src\Polly.Shared\Retry\RetryEngineAsync.cs:line 29
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Polly.Policy.<ExecuteAsync>d__135.MoveNext() in C:\projects\polly\src\Polly.Shared\PolicyAsync.cs:line 269
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MyApp.DataAccess.Sql.Common.DbExecutionPolicy.<ExecuteAsync>d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MyApp.DataAccess.Sql.EntityFramework.DbSession.<BulkInsertAsync>d__29`1.MoveNext()

Further technical details

• EF version: 6.3
• EF Extensions version: 4.0.96
• Database Provider: MS SQL Server

[JonathanMagnan]

Hello @KingR1 ,

Is it possible for you to log the query (https://entityframework-extensions.net/logging) that causes the error?

Any additional information could be very useful for us as we are not sure where no start.

Are you confirming that some older version is working but the newest version throws this error? If yes, was is the most recent working version you know which is working?

Best Regards,

Jon

[KingR1]

Hi @JonathanMagnan

About logging - will try to add, but it can be not too simple to do it for me. About version - for sure it was fine with 4.0.79... next version that was tried was 4.0.91, but cannot remember if that version already had this issue. And now 4.0.96...

[JonathanMagnan]

Thank you,

I'm pretty sure the issue appears starting from the v3.0.88 (which now add a clustered index to the ZZZ_Index column) but it could be also something else.

Having the current executed SQL statement in error will sure help us since we will know exactly where the error appears. You can also retrieve it via SQL Profiler or directly use EF6 logger as well.

[KingR1]

@JonathanMagnan Here is example of query:

[mySchema]: -- Executing Command:
CREATE TABLE [mySchema].#ZZZProjects_a2be727a_601e_470f_8fa3_c21fd1d33b53 ( [Id] [sys].[uniqueidentifier] NULL, [CompanyProfileId] [sys].[uniqueidentifier] NULL, [SetId] [sys].[uniqueidentifier] NULL, [BuildType] [sys].[int] NULL, [RequestSource] [sys].[int] NULL, [DataDiscoveryState] [sys].[int] NULL, [DateCreated] datetime NULL, ZZZ_Index [INT] NOT NULL )

CREATE CLUSTERED INDEX INDEX_[mySchema]#ZZZProjects_a2be727a_601e_470f_8fa3_c21fd1d33b53 ON [mySchema].#ZZZProjects_a2be727a_601e_470f_8fa3_c21fd1d33b53 (ZZZ_Index ASC);
-- CommandTimeout:120
-- Executing at 10/3/2020 4:00:13 AM

So temp table cannot be created in my custom schema.

[KingR1]

@JonathanMagnan do you think we can expect a fix for this issue in near future or do you need some more details? Thanks.

[JonathanMagnan]

Hello @KingR1 ,

Sorry for the delay, my developer will look at it as a priority today.

So I should be able to give you an update tomorrow

[JonathanMagnan]

Hello @KingR1 ,

A new version has been released today (v4.0.97)

We are not sure if we fixed exactly the same issue as you or not. The only way we successfully reproduced it is when a schema name in the option TemporaryTableSchemaName was specified.

If you still have the issue, let us know and we will investigate further but with your scenario description, we believe we successfully fixed it.

Best Regards,

Jon

[JonathanMagnan]

Hello @KingR1 ,

Since our last conversation, we haven't heard from you.

Did you get the time to try the new release v4.0.97?

Don't hesitate to contact us if you are still encountering the issue.

Best regards,

Jon

[KingR1]

@JonathanMagnan Yes, trying with the new version. I Will let you know. Thx

[KingR1]

@JonathanMagnan yes, the issue was fixed in 4.0.97. Thanks.

[JonathanMagnan]

Awesome @KingR1

Don't hesitate to contact us for questions, issues or feedback!

Best regards,

Jon