Issue with DROP TABLE IF EXISTS in older SQL Server versions

[nicolazreinh]

Description:
I am facing an error when trying to deprovision SyncProvision.ScopeInfo and SyncProvision.ScopeInfoClient:

incorrect syntax near the keyword 'IF'. drop table if exists

The DROP TABLE IF EXISTS syntax is supported starting from SQL Server 2016 (13.x) and later versions. For older versions of SQL Server, an alternative approach is required.

Proposed Solution:

Modify the methods GetDropScopeInfoTableCommand and GetDropScopeInfoClientTableCommand in SqlScopeBuilder to use a compatible query for older SQL Server versions.

Here is an equivalent query that works in older versions of SQL Server:

IF OBJECT_ID('dbo.TableName', 'U') IS NOT NULL 
  DROP TABLE dbo.TableName;

I will make a pull request with the proposed fix.

Pull Request Description

Title: Fix for DROP TABLE IF EXISTS compatibility in older SQL Server versions

Description:
This pull request addresses the issue with the DROP TABLE IF EXISTS syntax, which is not supported in SQL Server versions earlier than 2016. The methods GetDropScopeInfoTableCommand and GetDropScopeInfoClientTableCommand in SqlScopeBuilder are updated to use a compatible query for older versions of SQL Server.

Changes:

• Modified GetDropScopeInfoClientTableCommand in SqlScopeBuilder to:

  public override DbCommand GetDropScopeInfoClientTableCommand(DbConnection connection, DbTransaction transaction)
        {
            var tableName = $"{this.ScopeInfoTableName.Unquoted().Normalized().ToString()}_client";
            var tableWithSchema = $"[dbo].[{tableName}]";
            var command = connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText = $"IF OBJECT_ID('{tableWithSchema}', 'U') IS NOT NULL " +
                $"DROP TABLE {tableWithSchema};";
            return command;
        }

• Modified GetDropScopeInfoTableCommand in SqlScopeBuilder to:

        public override DbCommand GetDropScopeInfoTableCommand(DbConnection connection, DbTransaction transaction)
        {
            var tableName = this.ScopeInfoTableName.Unquoted().Normalized().ToString();
            var tableWithSchema = $"[dbo].[{tableName}]";
            var command = connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText = $"IF OBJECT_ID('{tableWithSchema}', 'U') IS NOT NULL " +
                $"DROP TABLE {tableWithSchema};";
            return command;
        }
  }

By implementing these changes, the library will be compatible with older versions of SQL Server.

I have tested these changes on SQL Server versions earlier than 2016 and confirmed that the issue is resolved.

[Mimetis]

Thanks for this really detailed feedback !
I'll implement it in the next patch
Thanks !

[nicolazreinh]

Thank you for your prompt response and your great library. Let us know if you need any help.