CLI behavior change with /MakeScripts switch in 14.4

rdaunce

I'm using the Windows version of sqldatacompare.exe in a git pre-commit git hook script. An example of the command that it uses is:

SQLDataCompare.exe /scripts1:d:\git\repo /MakeScripts:C:\temp\scripts\863YARnqJv0L3NGWcrSrby7fwgDXRU7q

The source path identified in the /scripts1 switch is the git directory for a database configured for SQL Source Control. The path identified in the /MakeScripts switch is always a newly created empty directory. The command previously created a scripts folder that included scripts for the static data identified in the <DataFileSet> node of the source RedGateDatabaseInfo.xml file.  Since the 14.4.0.16632 release, the static data scripts are no being included. I installed the latest version, 14.4.12.17429, and it doesn't include the data scripts either.  The last version that it included the data scripts was version 14.3.4.16598.

Is this an intentional change or is this a bug?

David_D

Hello @rdaunce,

Unfortunately, I was not able to reproduce this issue on my end. When running the /MakeScripts option with SQL Data Compare, does the directory contain any object creation scripts or does it create an empty directory? Does the directory contain any newly created files while SQL Data Compare is running from command line?

If you add /LogLevel:Verbose, this will generate a log file at C:\Users\<user name>\AppData\Local\Red Gate\Logs\SQL Data Compare that may provide answers as to why the scripts are not created. Can you please send over this log file?

Kind regards,
David Dang
Product Support Engineer

rdaunce

Hi,

Using V14.3.4.16598 or earlier, the /MakeScripts option creates a directory containing all object creation scripts including the static data in a data folder. Using version V14.4.0.16632 or later, the /MakeScripts option creates a directory containing all object creation scripts except the static data. The Data folder isn't even created.

I setup a basic reproduction of the issue using a 1 table database that has 2 rows of data. I created it using SQL Source Control V7.1.29.11028, set the 1 table as a static data table, and published it to a public repository on GitHub.

• At a command prompt, clone the repo:

  cd %LOCALAPPDATA%\temp\
  
  git clone https://github.com/rdaunce/DataTest.git

• With SQLDataCompare V14.4.0.16632 or later installed (I used V14.4.12.17429), run the following commands:

  cd "\Program Files (x86)\Red Gate\SQL Data Compare 14"
  
  SQLDataCompare.exe /scripts1:%LOCALAPPDATA%\temp\DataTest /MakeScripts:%LOCALAPPDATA%\temp\DataTestCopy14.4 /LogLevel:Verbose
  
  explorer %LOCALAPPDATA%\temp\DataTestCopy14.4
  

• This is the output of the SQLDataCompare.exe command when I ran it:

  SQLDataCompare V14.4.12.17429
  ======================================================================================================================
  Copyright c Red Gate Software Ltd 1999-2020
  
  SQL Data Compare: activated, edition professional, serial number: XXX-XXX-XXXXXX-XXXX
  Warning: Some settings are missing from the RedGateDatabaseInfo.xml file in the scripts folder
  'C:\Users\rdaunce\AppData\Local\temp\DataTest': default values will be used for any omitted setting.

• The resulting directory only has the Tables folder with a table creation script.  There is no Data folder.
  
  
• Uninstall the current version of SQLDataCompare.
• Install SQLDataCompare 14.3.4.16598 or earlier and return to the command prompt:

  cd "\Program Files (x86)\Red Gate\SQL Data Compare 14"
  
  SQLDataCompare.exe /scripts1:%LOCALAPPDATA%\temp\DataTest /MakeScripts:%LOCALAPPDATA%\temp\DataTestCopy14.3 /LogLevel:Verbose
  
  explorer %LOCALAPPDATA%\temp\DataTestCopy14.3
  

• This is the output of the SQLDataCompare.exe command when I ran it.  This version includes more output.
  

  SQL Data Compare Command Line V14.3.4.16598
  ======================================================================================================================
  Copyright c Red Gate Software Ltd 2020
  
  SQL Data Compare: activated, edition professional, serial number: XXX-XXX-XXXXXX-XXXX
  Warning: Some settings are missing from the RedGateDatabaseInfo.xml file in the scripts folder
  'C:\Users\rdaunce\AppData\Local\temp\DataTest': default values will be used for any omitted setting.
  Creating folder of scripts 'C:\Users\rdaunce\AppData\Local\temp\DataTestCopy14.3' from the scripts folder 'C:\Users\rdaunce\AppData\Local\temp\DataTest'...
  Registering databases
  Mapping
  Comparing databases
  Generating SQL scripts
  Deploying databases

• The resulting directory has the Tables folder and the Data folder.  Both contain the expected creation scripts.
  

The behavior in the second run is what I expected when I upgraded to V14.4.

I have attached the log files that were created from these commands to this post.  They have been renamed to match the SQLDataCompare version that produced them.

The log files both start mostly the same with different placement of the diagnostics JSON block in each.  The end of SQLDataCompare-V14.3.4.16598.log (line # 501 to 621) does not match anything found in SQLDataCompare-V14.4.12.17429.log.  The missing section looks like the portion where the data is processed.  There are no log messages or errors to indicate a problem in SQLDataCompare-V14.4.12.17429.log, it just looks like it doesn't do that part.

David_D

Hello @rdaunce,

Thanks for your patience. As a quick follow up, I've confirmed with our development team that the old SqlDataCompare.exe /makescripts would have created a copy of the original folder and changes to the command line in later versions has likely caused a regression in this functionality. Is there a specific use case for this as it seems like copying the folder would be equivalent to the old /makescripts?

In addition, I hope the workaround of either copying the folder or using an older version of SQL Data Compare would work for you. Please don't hesitate to reach out if you have any other questions.

Kind regards,
David Dang
Product Support Engineer

rdaunce

Thanks for confirming that there was a change in behavior.

My specific use case is cloning the working version in a pre-commit hook on the SQL Source Control repository.  Once the clone is made, the pre-commit hook can perform validation and other operations can on the clone without unwanted side effects in the actual git repository.  For example, I can validate that naming conventions are followed, or that consistent formatting is applied. If the hook identifies and corrects something, it copies it back to the original repository and the updated version is committed.

Almost all of our per-commit hook script can be work on using a copy of the contents of the directory instead of having SQL Data Compare create the script folder.  The one thing copying doesn't handle is applying consistent ordering of static data in the cloned version's creation scripts.  In the scripts created by SQL Source Control, the order of the insert statements is inconsistent.  In a commit to SQL Source Control involving static data, the commit frequently moves unchanged rows to different locations in the creation script and moves updated rows to the top of the creation script.  There are many other forum and user-voice posts about SQL Source Control Static Data ordering inconsistencies and the issues it causes.  The inconsistent ordering makes for extremely difficult merge conflict resolution and it makes it difficult to identify what has actually changed when viewing diffs on the git repository.  These issues make it easy to for one developer to accidentally revert static data changes made by another developers during a merge conflict because they didn't notice that a row was moved AND changed.

As part of our pre-commit hook, the creation of the clone using SQL Data Compare with the /MakeScripts switch has the additional desirable side effect of creating all static data scripts with the insert statements ordered by the primary key of the table.  Our pre-commit hook then replaces any static data creation script that was part of the original commit with the consistently ordered cloned version.  This ensures all developers have the same order applied and makes it much easier to see how static data changed over time when viewing the git repository.

The change in behavior prevents us from doing this anymore unless we stick with an older version or come up with a different method.  An older version works for the short term but is not a viable solution.  It would exclude us from bug fixes, new features, and security updates.

My primary goal is to generate a version of the static data creation scripts in a consistent order.  I could accomplish it by running two separate SQL Data Compare commands, one to create the schema scripts and one to copy the data.  The issue that I have with that is that it forces us to do two completely separate SQL Data Compare operations.  The inclusion of a single compare in our pre-commit hook already added a noticeable delay on every commit that involves static data.  Adding a second compare operation would double the time it takes for the pre-commit hook to complete. It would be preferable to be able to run a single compare operation..

I don't have an issue using a different command, different switches, or even a completely different method - I'm mostly interested in accomplishing the same end result.  I would even be satisfied with method that creates only the static data creation scripts.  Please let me know if you have any suggestions.

David_D

Hello @rdaunce,

Thanks for sharing your feedback.

I've confirmed that running /MakeScripts just creates a copy of the source. Essentially copying the scripts folder and pasting yields the same results. If you do decide to upgrade to a later version of SQL Data Compare, /MakeScripts will no longer create copies of the file as you've noted. Although, the workaround is to run a copy command or manually copying the directory over.

Please don't hesitate to let me know if you have any questions or concerns.

Kind regards,
David Dang
Support Engineer