RoundhousE Migration Fails On Valid SQL On SQL Server

Question

I have a mature code-base with a set of SQL migration scripts. We run the migrations to update or create the database using RoundhousE via a Powershell script. Until recently these migrations were running successfully, however now I am getting an error on with the actual SQL in the migration scripts.

    System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'IF'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.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, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at roundhouse.databases.AdoNetDatabase.run_command_with(String sql_to_run, ConnectionType connection_type, IList`1 parameters) in C:\projects\roundhouse\product\roundhouse.core\databases\AdoNetDatabase.cs:line 143
   at roundhouse.databases.AdoNetDatabase.run_sql(String sql_to_run, ConnectionType connection_type, IList`1 parameters) in C:\projects\roundhouse\product\roundhouse.core\databases\AdoNetDatabase.cs:line 134
   at roundhouse.databases.DefaultDatabase`1.run_sql(String sql_to_run, ConnectionType connection_type) in C:\projects\roundhouse\product\roundhouse.core\databases\DefaultDatabase.cs:line 213
   at roundhouse.migrators.DefaultDatabaseMigrator.run_sql(String sql_to_run, String script_name, Boolean run_this_script_once, Boolean run_this_script_every_time, Int64 version_id, EnvironmentSet environment_set, String repository_version, String repository_path, ConnectionType connection_type) in C:\projects\roundhouse\product\roundhouse.core\migrators\DefaultDatabaseMigrator.cs:line 206
   at roundhouse.runners.RoundhouseMigrationRunner.traverse_files_and_run_sql(String directory, Int64 version_id, MigrationsFolder migration_folder, EnvironmentSet migrating_environment_set, String repository_version, ConnectionType connection_type) in C:\projects\roundhouse\product\roundhouse.core\runners\RoundhouseMigrationRunner.cs:line 290
   at roundhouse.runners.RoundhouseMigrationRunner.log_and_traverse(MigrationsFolder folder, Int64 version_id, String new_version, ConnectionType connection_type) in C:\projects\roundhouse\product\roundhouse.core\runners\RoundhouseMigrationRunner.cs:line 222
   at roundhouse.runners.RoundhouseMigrationRunner.run() in C:\projects\roundhouse\product\roundhouse.core\runners\RoundhouseMigrationRunner.cs:line 145
ClientConnectionId:a60834d8-8eb0-4de7-9735-02c53968c4d3
Error Number:156,State:1,Class:15
SqlErrors:
Error Number: 156, Message: Incorrect syntax near the keyword 'IF'.

It isn't an issue with the SQL - the SQL runs fine manually on the database.

The very same migrations run successfully without alteration on my colleagues machine, so it must be something unique to my machine that is causing it.

I have tried:

• updating RoundhousE from 1.2.1 to 1.3.1
• uninstalling RoundhouseE and re-installing
• running the migrations as part of unit tests in Visual Studio test runner
• running the migrations as part of unit tests in Jetbrains Rider test runner
• running the migrations against an existing database
• running the migrations against a bran new database

I get the same SQL error: "Error Number: 156, Message: Incorrect syntax near the keyword 'IF'."

I'm kind of reluctant to post the SQL itself because it isn't an issue with the SQL - these scripts have been ran over and over again locally and in the release pipeline and I don't want to invite recommendations to change the SQL. However, the SQL is:

GO

DROP INDEX IF EXISTS [specialapp].[TypeAnalyis].[IX_TypeAnalysis_UserId]

CREATE NONCLUSTERED INDEX [IX_TypeAnalysis_UserId] ON [specialapp].[TypeAnalyis]
(
    [UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

The RH command being used is:

rh.exe --db SpecialAppDatabase --transaction  --silent  --files C:\Code\specialapp\src\SpecialApp.Database\sql --instance (localdb)\mssqllocaldb --version unversioned --output C:\Code\specialapp\src\Database\Scripts\log

The actual output is as below:

What could be the setting or difference on my machine that is preventing the migrations running? This codebase was recently upgraded to .Net 6 so it may be related to that. However as mentioned above, the same RH command works on a colleague's machine.