SET QUOTED_IDENTIFIER OFF leads to Unhandled exception error

[shareef777]

Describe the bug
Stored Procedure with SET QUOTED_IDENTIFIER OFF leads to the following error:

`Unhandled exception: grate.Exceptions.MigrationFailed: Migration failed due to errors:

• Incorrect syntax near 'ScriptsRun'.
  ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'ScriptsRun'.
  at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
  at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
  at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
  at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
  at Microsoft.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)
  at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
  at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
  at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
  at Microsoft.Data.SqlClient.SqlCommand.<>c.b__208_1(IAsyncResult result)
  at System.Threading.Tasks.TaskFactory1.FromAsyncCoreLogic(IAsyncResult iar, Func2 endFunction, Action1 endAction, Task1 promise, Boolean requiresSynchronization)
  --- End of stack trace from previous location ---
  at Dapper.SqlMapper.ExecuteImplAsync(IDbConnection cnn, CommandDefinition command, Object param) in /_/Dapper/SqlMapper.Async.cs:line 647
  at grate.Migration.AnsiSqlDatabase.ExecuteAsync(DbConnection conn, String sql, Object parameters)
  at grate.Migration.AnsiSqlDatabase.InsertScriptRun(String scriptName, String sql, String hash, Boolean runOnce, Int64 versionId, TransactionHandling transactionHandling)
  at grate.Migration.DbMigrator.RunTheActualSql(String sql, String scriptName, MigrationType migrationType, Int64 versionId, ConnectionType connectionType, TransactionHandling transactionHandling)
  at grate.Migration.DbMigrator.<>c__DisplayClass25_0.<g__LogAndRunSql|0>d.MoveNext()
  --- End of stack trace from previous location ---
  at grate.Migration.DbMigrator.RunSql(String sql, String scriptName, MigrationType migrationType, Int64 versionId, GrateEnvironment environment, ConnectionType connectionType, TransactionHandling transactionHandling)
  at grate.Migration.GrateMigrator.Process(DirectoryInfo root, MigrationsFolder folder, String changeDropFolder, Int64 versionId, ConnectionType connectionType, TransactionHandling transactionHandling)
  at grate.Migration.GrateMigrator.LogAndProcess(DirectoryInfo root, MigrationsFolder folder, String changeDropFolder, Int64 versionId, ConnectionType connectionType, TransactionHandling transactionHandling)
  at grate.Migration.GrateMigrator.Migrate()
  ClientConnectionId:68ac6fbc-8621-4b69-a296-8357ce9da664
  Error Number:102,State:1,Class:15
  --- End of inner exception stack trace ---
  at grate.Migration.GrateMigrator.Migrate()
  at grate.Commands.MigrateCommand.<>c__DisplayClass0_0.<<-ctor>b__0>d.MoveNext()
  --- End of stack trace from previous location ---
  at System.CommandLine.NamingConventionBinder.CommandHandler.GetExitCodeAsync(Object returnValue, InvocationContext context)
  at System.CommandLine.NamingConventionBinder.ModelBindingCommandHandler.InvokeAsync(InvocationContext context)
  at System.CommandLine.Invocation.InvocationPipeline.<>c__DisplayClass4_0.<b__0>d.MoveNext()
  --- End of stack trace from previous location ---
  at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass17_0.<b__0>d.MoveNext()
  --- End of stack trace from previous location ---
  at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass12_0.<b__0>d.MoveNext()
  --- End of stack trace from previous location ---
  at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass19_0.<b__0>d.MoveNext()
  --- End of stack trace from previous location ---
  at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<b__18_0>d.MoveNext()
  --- End of stack trace from previous location ---
  at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass16_0.<b__0>d.MoveNext()
  --- End of stack trace from previous location ---
  at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<b__5_0>d.MoveNext()
  --- End of stack trace from previous location ---
  at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass8_0.<b__0>d.MoveNext()`

To Reproduce
Changing quoted_identifier to ON allows grate to run successfully.

Expected behavior
Allow the setting to remain OFF

Screenshots
n/a

Desktop (please complete the following information):

• OS: [Linux
• Version: [Amazon Linux 2023]

Additional context
This is a migration attempt from RoundHouse.

[HalliTT]

Had the same issue, but for me, I found a solution.
Hope it can help you.

After using SET QUOTED_IDENTIFIER OFF in a script I have to set it back to SET QUOTED_IDENTIFIER ON.
It seems that it does not matter where as long as it is set back to ON before the end of the script file.
So I can set the QUOTED_IDENTIFIER multiple times through a script, but when the OFF setting is no longer needed I will set it back to ON.

Example 1.
This will give the error: Unhandled exception: grate.Exceptions.MigrationFailed: Migration failed due to errors:

SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [aspnet_CheckSchemaVersion]
    @Feature                   nvarchar(128),
    @CompatibleSchemaVersion   nvarchar(128)
AS
BEGIN
    IF (EXISTS( SELECT  *
                FROM    dbo.aspnet_SchemaVersions
                WHERE   Feature = LOWER( @Feature ) AND
                        CompatibleSchemaVersion = @CompatibleSchemaVersion ))
        RETURN 0
    RETURN 1
END
GO

Example 2.
This will not give the error.

SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [aspnet_CheckSchemaVersion]
    @Feature                   nvarchar(128),
    @CompatibleSchemaVersion   nvarchar(128)
AS
BEGIN
    IF (EXISTS( SELECT  *
                FROM    dbo.aspnet_SchemaVersions
                WHERE   Feature = LOWER( @Feature ) AND
                        CompatibleSchemaVersion = @CompatibleSchemaVersion ))
        RETURN 0
    RETURN 1
END
GO
SET QUOTED_IDENTIFIER ON
GO

[erikbra]

Resolved in #500 500