SQL Change Automation: Avoid deploying Migration-1.0.0, and updated procs not creating new migration

StephenASC

Hi all,

Apologies if this has already been asked, but I'm seeing a behavior which I haven't seen before, but this is the first time deploying stored procedure changes.

After baselining, generating V1.0.0 migration, I check into source control. I then alter the stored procedure, validate again in SCA, and it identifies the change correctly.

But it does not generate a V1.1.0 migration, it updates the script already stored in Programmable Objects, and upon deploy, it attempts to apply the V1.0.0 migration to an existing database, which causes a timeout.

I'm sure the problem is between the chair and the keyboard, but appreciate any advice.

Thanks

Peter_Laws

Nothing to apologise for, thank you for your query Stephen,

The behaviour you've described is intended for programmable objects, but there are a few different ways to handle this. From your description it sounds like the following would be the most useful to you.

I've also included the associated documentation in case there's additional options that would suit your use case better.

ScriptInMigrations - This effectively disables the programmable object feature and scripts all programmable object types out in migration scripts, as with any other object type.

• Note that if you have already generated programmable objects before selecting this setting, these objects will continue to be deployed. If this is not desirable, delete the programmable objects folder.

Regarding the timeout you've experienced, we don't anticipate it being related to programmable objects, or in fact any conflict, which would typically return an error relating to the incompatibility rather than just hanging.

Since you mentioned a baseline, just to clarify; If V1.0.0 is a baseline script it should not be deployed to a non-empty database, except if the _MigrationLog table exists and does not have a row stating that the Baseline has been deployed. 

This scenario is fairly improbable and most likely to occur through manual manipulation, but included for sake of thoroughness.

Kind regards,

Peter