Error when dropping a schema in SQL Change Automation

behibberd

I'm seeing an error when I try to drop a schema in SQL Change Automation.  I have a schema named test_schema that does not have any objects in it.  Here is the migration script that creates it:

PRINT N'Creating schemas'
GO
CREATE SCHEMA [test_schema]
GO

I then drop the schema in my development database, go to the SQL Change Automation tab in Visual Studio, and click "Import and generate script".  I get an error message with the following contents:

Message = Could not find file 'D:\git\[ProjectName]\Schema-Model\Security\Schemas\test_schema.sql'.
FileName = D:\git\[ProjectName]\Schema-Model\Security\Schemas\test_schema.sql
FusionLog = 
Data = System.Collections.ListDictionaryInternal
InnerException = 
TargetSite = Void WinIOError(Int32, System.String)
StackTrace =    at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access)
   at System.IO.Abstractions.FileStreamFactory.Create(String path, FileMode mode, FileAccess access)
   at RedGate.Versioning.Engine.Core.ScriptGeneration.Synchronization.Folder.RerunnableScriptFolderComparerAndSynchronizer`1.MakeScriptsRerunnable(IEnumerable`1 projectFilesToParse, IComparisonSettings settings)
   at RedGate.Versioning.Engine.Core.ScriptGeneration.Synchronization.Folder.RerunnableScriptFolderComparerAndSynchronizer`1.<SyncDatabaseWithFolder>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at RedGate.Versioning.Engine.Interaction.DatabaseProjectSynchronization.OfflineSchemaModel.OfflineSchemaModelSynchronizer`1.<SyncOfflineSchemaModelForSpecificDifferences>d__7.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at RedGate.Versioning.Engine.Facade.Imports.SchemaModelAwareScriptGenerator.<GenerateScriptsAndHandleUnsupportedProgrammableObjects>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at RedGate.Versioning.Engine.Facade.Imports.ProjectScriptGenerator.<GenerateScriptsAndHandleUnsupportedProgrammableObjects>d__10.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at RedGate.Versioning.Engine.Facade.Imports.ProjectScriptGeneratorAndImporter.<ImportAndHandleUnsupportedProgrammableObjects>d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at RedGate.Versioning.Client.VsShell.VisualStudio.Commands.SyncCommand.<Synchronize>d__28.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)
   at RedGate.Versioning.Client.VsShell.VisualStudio.Commands.SyncCommand.<ExecuteOrThrow>d__22.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at RedGate.Versioning.Client.VsShell.VisualStudio.Commands.SyncCommand.<Execute>d__21.MoveNext()
HelpLink = 
Source = mscorlib
HResult = -2147024894

To be clear, SQL Change Automation appears to have created merge scripts and updated the offline model:

• Migrations\1.1.0-Changes\041_20200116-1039_bhibberd - Drop test_schema schema.sql [add]
• Schema-Model\Security\Schemas\test_schema.sql [delete]

But SQL Change Automation did not update the SqlProj file.  I can update the SqlProj file manually, adding the migration script and deleting the offline schema definition -- and then I also have to re-add the schema in my development database and deploy the migration script to my development database -- and then finally the project file builds/validates/deploys appropriately.

I'm working in Visual Studio Enterprise 2019, Version 16.1.3.  I'm running Redgate SQL Change Automation 4.1.20006.14488.

The project is configured such that it does not use the programmable objects feature.  However, programmable objects are otherwise synced to the offline schema.  Here are my offline schema settings:

  <PropertyGroup>
    <SyncToOfflineSchemaModel>True</SyncToOfflineSchemaModel>
    <SyncToOfflineSchemaModelObjectTypes>
      Table;Contract;Default;MessageType;Queue;Route;Rule;Schema;Sequence;
      Service;UserDefinedType;XmlSchemaCollection;StoredProcedure;Function;
      View;DdlTrigger
    </SyncToOfflineSchemaModelObjectTypes>
  </PropertyGroup>

Anyway, I thought you might want to be aware of this error.  If it's some kind of bad configuration on my end, let me know.  If I can provide any additional information, please don't hesitate to ask.

Thanks!