Umbraco 9 failing to boot SqlException: Incorrect syntax near the keyword 'IF'.

[itsliamprowse]

I've just installed v3 alpha 2 into an Umbraco 9 RC 3 project, and I get the following error when the website boots, If I remove the redirects package it works again, but as soon as I reinstall it, it fails to boot.

Have I missed something?

Thanks in advance

SqlException: Incorrect syntax near the keyword 'IF'. System.Data.SqlClient.SqlConnection.OnError(SqlException exception, bool breakConnection, Action<Action> wrapCloseInAction) System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, bool breakConnection, Action<Action> wrapCloseInAction) System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, bool callerHasConnectionLock, bool asyncClose) System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, out bool dataReady) System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(string methodName, bool async, int timeout, bool asyncWrite) System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource<object> completion, bool sendToPipe, int timeout, bool asyncWrite, string methodName) System.Data.SqlClient.SqlCommand.ExecuteNonQuery() StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery() in ProfiledDbCommand.cs Umbraco.Cms.Infrastructure.Persistence.FaultHandling.FaultHandlingDbCommand.<ExecuteNonQuery>b__31_0() Umbraco.Cms.Infrastructure.Persistence.FaultHandling.FaultHandlingDbCommand+<>c__DisplayClass33_0<T>.<Execute>b__0() Umbraco.Cms.Infrastructure.Persistence.FaultHandling.RetryPolicy.ExecuteAction<TResult>(Func<TResult> func) Umbraco.Cms.Infrastructure.Persistence.FaultHandling.FaultHandlingDbCommand.Execute<T>(Func<T> f) Umbraco.Cms.Infrastructure.Persistence.FaultHandling.FaultHandlingDbCommand.ExecuteNonQuery() NPoco.Database.ExecuteNonQueryHelper(DbCommand cmd) NPoco.Database.Execute(string sql, CommandType commandType, object[] args) NPoco.Database.Execute(Sql Sql) NPoco.Database.Execute(string sql, object[] args) Skybrud.Umbraco.Redirects.Migrations.RemoveIsRegexColumnMigration.Migrate() Umbraco.Cms.Infrastructure.Migrations.MigrationBase.Run() Umbraco.Cms.Infrastructure.Migrations.MigrationPlanExecutor.Execute(MigrationPlan plan, string fromState) Umbraco.Cms.Infrastructure.Migrations.Upgrade.Upgrader.Execute(IMigrationPlanExecutor migrationPlanExecutor, IScopeProvider scopeProvider, IKeyValueService keyValueService) Skybrud.Umbraco.Redirects.Components.MigrationComponent.Initialize() Umbraco.Cms.Core.Composing.ComponentCollection.Initialize() Umbraco.Cms.Infrastructure.Runtime.CoreRuntime.StartAsync(CancellationToken cancellationToken) Microsoft.Extensions.Hosting.Internal.Host.StartAsync(CancellationToken cancellationToken) Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token) Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token) Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.Run(IHost host) ProjectName.Program.Main(string[] args) in Program.cs + => CreateHostBuilder(args)

[abjerner]

Hi @itsliamprowse

What kind of database are you using?

The latest alpha contains a migration that removes a single column from the database table - but only if the column already exists, so the query doesn't fail.

I had a small suspicion this wouldn't work in all database types, but this being an alpha release, I only ended up testing with SQL Server for now.

[itsliamprowse]

Hey @abjerner

Thanks for getting back to me, I am using SQL Server, however our dev server uses SQL Server 2014 v12.0.2000, so it could be something that's not supported on older versions?

[abjerner]

I've now pushed a new release that should fix this. I currently don't have a setup for testing in SQL Server 2014, but I will try to get something for that setup for testing future releases. The fix uses some of Umbraco's build-in logic around migration that I didn't knew about, so there should be better support for even more database engines.

As the migration never finished in your solution, it should work by updating the package to 3.0.0-alpha003, and then boot up Umbraco so it can run the migration 😉

[itsliamprowse]

Just tested, working perfectly! Thank you! :D