What are the challenges you face when working across database platforms? Take the survey

Programmable objects deployed even though mark as deployed set

Running into an issue and not sure why...

When deploying, the programmable objects are being deployed, even though below is set and it shows nothing being included:

Sequence I went through:
1. Restored a copy of the database to a dev environment and a staging environment
2. Created a new SQL Change Automation Project.  Closed solution, updated project file with appropriate sync options
3. Added filter file to project directory
4. Opened VS Project
5. Changed target to SQL 2017
6. Checked "all programmable objects/additional scripts as deployed when setting the baseline"
7. Unchecked Validate database containment by checking three-part object references during build
8. Completed project setup, specifying dev database and staging databases
9. Build -> Fine
10. Refresh -> Identical
11. Added new table in dev, refresh, checked table, import, changes script generated with new table
12. Refresh, Pending scripts found (in Dev) - clicking on scripts shows "443 migration(s) will be executed on XXXXX when the Deploy command is performed.  To see the list of pending migrations, build the solution and refer to output window.", shows 725 programmable objects, and under Generating package.sql, it shows 443 migrations, including 442 programmable objects.

I ran it the first time, not realizing this, and it absolutely deployed the 443 objects.  I then did the restores (starting at #1 above) so I could capture for this.

Is there something I'm doing wrong?  Why does it show as identical for all of these, the programmable objects are marked as deployed when setting baseline checked, yet it deploys?  I verified the project file also:

This is expected, since I don't want them in the migration scripts:

Assuming this is the setting corresponding to the baseline setting for programmable objects:



Best Answer

  • Options
    davejhahndavejhahn Posts: 15 Bronze 2
    This ended up being an issue related to Git commits.  When committing and pushing the changeset, it was adding line terminations.  The line terminations are being detected by Redgate SQL Change Automation as a change.  Not sure of the internals of how this get detected, but it identified every single programmable object as a change. 

    The workaround for this is to add a .gitattributes file to your project (or use global settings if that is not a problem for your other projects) with the following in it:  * -text 

    Problem was resolved after re-creating the project and the repo.

    This was never an issue with DLM, so it appears that some of the change detection logic is more sensitive.


  • Options
    Another thing that is happening, after I see the "deploy", and hit refresh (above is incorrect, it shows deploy in #12 without hitting refresh, I can't seem to edit above), even if I re-open the project and try, I get a Script Validation Failed error for a programmable object which does not fail up to this point (when building and/pr refreshing, only after it detects a change to deploy), it fails with Invalid Object name, for a table object that exists in the Schema-Model (and is not filtered).  

  • Options
    davejhahndavejhahn Posts: 15 Bronze 2
    I added debug logging and it says "Cannot save <view file> because it is not open in the editor".  Not sure what that means, I did try leaving it open in VS, but the same error.  Details below, with sensitive information X'd out:

    025188|2018-11-27 19:45:04.853|ERROR|RedGate.Versioning.Client.VsShell.VisualStudio.ShellIntegration.VisualStudioFileEditor|1|Could not save project file requested: C:\Git\database\XXX\Programmable Objects\dbo\Views\XXXXXXXXXX.sql
    System.InvalidOperationException: Cannot save 'C:\Git\database\XXX\Programmable Objects\dbo\Views\XXXXXXXX.sql' because it is not open in the editor.
       at Microsoft.VisualStudio.Data.Tools.Package.Project.Internal.HierarchyNode.SaveItem(VSSAVEFLAGS saveFlag, String silentSaveAsName, UInt32 itemid, IntPtr docData, Int32& cancelled)
       at Microsoft.VisualStudio.Data.Tools.Package.Internal.OAFileItem.<>c__DisplayClass13_0.<DoSave>b__0()
       at Microsoft.VisualStudio.Data.Tools.Package.UI.DataPackage.<>c__DisplayClass114_0.<DoOnUIThread>b__0()
       at Microsoft.VisualStudio.Shell.ThreadHelper.Invoke(Action action)
       at Microsoft.VisualStudio.Data.Tools.Package.UI.DataPackage.DoOnUIThread(Func`1 callback)
       at Microsoft.VisualStudio.Data.Tools.Package.Internal.OAFileItem.DoSave(Boolean isCalledFromSaveAs, String fileName)
       at Microsoft.VisualStudio.Data.Tools.Package.Internal.OAFileItem.Save(String fileName)
       at RedGate.Versioning.Client.VsShell.VisualStudio.ShellIntegration.VisualStudioFileEditor.Save(FileInfo file)

    And for all of the programmable objects that should not be deployed, there is no indication to me why, every object just has a line such as (again sensitive information X'd out):

    025188|2018-11-27 19:45:08.100|DEBUG|RedGate.Versioning.Engine.Interaction.ProjectManipulation.MigrationLoad.MigrationLoader|71|Loading DeployChanges migration: Programmable Objects\dbo\Views\XXXXXXX.sql

    So I seem to have two problems, both seem like bugs to me, unless I'm doing something wrong.


  • Options
    What you are doing should work, I have just tested you scenario and in my case it didn't attempt to deploy the baseline (including programmable objects).

    I have noticed that you have a support ticket open, so we will carry on troubleshooting there
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
Sign In or Register to comment.