SC writes empty script for drop, adds "1" to recreated name

isme · April 7, 2014 12:54PM

In October I reported that SQL Compare sometimes creates empty files in source control and appends "1" to file names for some objects.

Now we can reliably reproduce the problem.

Follow these instructions to reproduce three behaviors that look like bugs. Two of them were described in the earlier report.

Are you able to reproduce the bugs?

Bugs

Bug 1: SQL Compare does not delete the script for a dropped object. Instead it writes an empty file.

Potential bug 2: SQL Compare deletes the server version from the metadata file.

Bug 3: SQL Compare does not reuse the existing empty file for a recreated object. Instead it appends a "1" to the file name. This is related to bug 1.

Bug 4: The Deployment Wizard falsely reports changes to the metadata file.

Bugs 1 and 3 are the major ones.

I'm not sure if number 2 is actually a bug, but it is confusing.

Repro Steps

1. Create a new database with a one object. The definition doesn't matter, but remember the name.

$ sqlcmd -Q "CREATE DATABASE DummyDB;"

$ sqlcmd -d DummyDB -Q "CREATE PROCEDURE DummyProc AS RETURN 1;"

2. Script out the database to new folder.

$ sqlcompare /database1:DummyDB /makescripts:DummyDB
SQL Compare: activated, edition: professional, serial number: XXX-XXX-XXXXXX-XXXX
SQL Compare Command Line V10.5.0.611
=============================================================================================
Copyright Ã‚© Red Gate Software Ltd 1999-2013

Creating folder of scripts 'C:\Users\iain\DummyDB' from database 'DummyDB' on '&#40;local&#41;'...
OK

3. Commit to a new source control repo. In this example I use Mercurial, but any source control system should do.

$ hg init DummyDB

$ hg add DummyDB
adding DummyDB\RedGateDatabaseInfo.xml
adding DummyDB\Stored Procedures\dbo.DummyProc.sql

$ hg commit DummyDB --message "Initial commit. The database contains just the DummyProc procedure."

4. Drop the object you just created.

$sqlcmd -d DummyDB -Q "DROP PROCEDURE DummyProc;"

5. Sync the scripts folder.

$ sqlcompare /database1:DummyDB /scripts2:DummyDB /sync
SQL Compare: activated, edition: professional, serial number: XXX-XXX-XXXXXX-XXXX
SQL Compare Command Line V10.5.0.611
==================================================================================
Copyright Ã‚© Red Gate Software Ltd 1999-2013                                
                                                                           
Registering data sources                                                   
Creating mappings                                                          
Comparing                                                                  
Applying Command Line Items                                                
Retrieving migration scripts                                               
Checking for identical databases                                           
Calculating script changes                                                 
Checking folders                                                           
Updating scripts folder &#40;from DB1 to DB2&#41;                                  
                                                                           
Summary Information                                                        
===================================================================================
DB1 = &#40;local&#41;.DummyDB                                                      
DB2 = DummyDB                                                              
                                                                           
Object type             Name                                               DB1 DB2
-----------------------------------------------------------------------------------
StoredProcedure         &#91;dbo&#93;.&#91;DummyProc&#93;                                      &lt;&lt;
-----------------------------------------------------------------------------------

6. Inspect the changes. The two affected files are "DummyProc.sql" and "RedGateDatabaseInfo.xml".

$ hg diff DummyDB
diff -r 9af204a793eb RedGateDatabaseInfo.xml
--- a/RedGateDatabaseInfo.xml   Mon Apr 07 12:18:16 2014 +0100
+++ b/RedGateDatabaseInfo.xml   Mon Apr 07 12:24:25 2014 +0100
@@ -6,7 +6,6 @@
   &lt;DefaultUser&gt;dbo&lt;/DefaultUser&gt;
   &lt;DefaultFilegroup&gt;PRIMARY&lt;/DefaultFilegroup&gt;
   &lt;DatabaseVersion&gt;11&lt;/DatabaseVersion&gt;
-  &lt;ServerVersion&gt;11.0.3128&lt;/ServerVersion&gt;
   &lt;MaxDataFileSize&gt;10485760&lt;/MaxDataFileSize&gt;
   &lt;WriteToFileOptions&gt;
     &lt;Prefixes&gt;
diff -r 9af204a793eb Stored Procedures/dbo.DummyProc.sql
--- a/Stored Procedures/dbo.DummyProc.sql       Mon Apr 07 12:18:16 2014 +0100
+++ b/Stored Procedures/dbo.DummyProc.sql       Mon Apr 07 12:24:25 2014 +0100
@@ -1,6 +0,0 @@
-SET QUOTED_IDENTIFIER ON
-GO
-SET ANSI_NULLS ON
-GO
-CREATE PROCEDURE &#91;dbo&#93;.&#91;DummyProc&#93; AS RETURN 1;
-GO

Bug 1: SQL Compare does not delete DummyProc.sql. Instead it makes it empty.

Potential bug 2: SQL Compare deletes the server version from the metadata file.

7. Commit the changes to source control.

$ hg commit DummyDB --message "Drop procedure DummyProc."

8. Create a new object with the same name as before.

$ sqlcmd -d DummyDB -Q "CREATE PROCEDURE DummyProc AS RETURN 2;"

9. Sync the scripts folder again.

$ sqlcompare /database1:DummyDB /scripts2:DummyDB /sync
SQL Compare: activated, edition: professional, serial number: XXX-XXX-XXXXXX-XXXX
SQL Compare Command Line V10.5.0.611
==================================================================================
Copyright Ã‚© Red Gate Software Ltd 1999-2013                                
                                                                           
Registering data sources                                                   
Creating mappings                                                          
Comparing                                                                  
Applying Command Line Items                                                
Retrieving migration scripts                                               
Checking for identical databases                                           
Calculating script changes                                                 
Checking folders                                                           
Updating scripts folder &#40;from DB1 to DB2&#41;                                  
                                                                           
Summary Information                                                        
===================================================================================
DB1 = &#40;local&#41;.DummyDB                                                      
DB2 = DummyDB                                                              
                                                                           
Object type             Name                                               DB1 DB2
-----------------------------------------------------------------------------------
StoredProcedure         &#91;dbo&#93;.&#91;DummyProc&#93;                                  &gt;&gt;
-----------------------------------------------------------------------------------

10. Inspect the changes. There is a new unversioned file called dbo.DummyProc1.sql, and no diffs in dbo.DummyProc.sql.

$ hg status DummyDB
? DummyDB\Stored Procedures\dbo.DummyProc1.sql

$ hg diff DummyDB

Bug 3: SQL Compare does not reuse the existing dbo.DummyProc.sql file. Instead it appends a 1 to the file name when recreating the object. This is related to bug 1.

11. Delete the unversioned file to prepare for the next step.

$ del "DummyDB\Stored Procedures\dbo.DummyProc1.sql"

12. Use the Deployment Wizard GUI to repeat step 9 up to the review stage.

13. Review the files that will be affected by this deployment.

Modify fileRedGateDatabaseInfo.xml
Create file Stored Procedures\dbo.DummyProc1.sql

14. Deploy the changes to the scripts folder.

15. Check which files really changed. The status of the metadata file has not changed.

$ hg status DummyDB
? DummyDB\Stored Procedures\dbo.DummyProc1.sql

Bug 4: The Deployment Wizard said RedGateDatabaseInfo.xml would change, but it did not.

isme · April 8, 2014 6:01AM

In October Brian Donahue quoted a developer's explanation for bug 1.

If we delete a file, then the source control will just think that the file is missing, and will not delete it from source control when the changes are checked in.

Good version control tools can deal with missing files. It's a common scenario.

hg has the --addremove option which automatically removes missing files.

TortoiseSVN automatically deletes missing files that are checked for commit.

I think SQL Compare should not worry about what version control thinks.

When it's in script-writing mode, it should just concentrate on writing a tidy set of scripts.

Evan Moss · April 9, 2014 6:52PM

Hi Iain,

Thanks for the reproduction steps; that's definitely useful info. I'll try to get everything reproduced here in the next couple of days and I'll update our bug tracking system and research internally and let you know what the status is.

Thanks,
Evan

isme · April 15, 2014 1:05PM

Hi Evan,

Thanks for looking into it.

Were you able to reproduce the issues?

Can you report on the internal status?

Thanks,
Iain

Evan Moss · April 15, 2014 5:53PM

Hi Iain,

Sorry about the delay.

I was able to reproduce all the behavior you mentioned. It looks like bugs 1 and 3 are logged as SC-3645, which I've updated internally. It doesn't look like it's on the radar for an immediate fix, so I don't have a good timeline for you.

Regarding 2 and 4, it looks like no matter what, when you deploy to a scripts file source, it writes the xml file in the format that doesn't include the server version. So 2 looks like an inconsistency in which makescripts includes that tag when the folder is created, but it's removed if it's ever the target in a comparison. 4 looks like it occurs because it actually rewrites the file with the same content each time (I tried messing with the file right before deploying and it was changed back right after the deployment).

I logged 2 SC-7074 as and 4 as SC-7073.

Thanks,
Evan

isme · April 18, 2014 1:28PM

Thanks for logging those, Evan.

SC-3645 is the most important one here.

Somehow our workflow ends up producing hundreds of empty files paired with script files with 1 on the end every few months.

When we gather enough someone shouts about it and we decide to clean up.

We've learned to be more vigilant about our commits in any case.

It would be great if SQL Compare was more tidy in the first place :-)

SC writes empty script for drop, adds "1" to recreated name

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk

SC writes empty script for drop, adds &quot;1&quot; to recreated name

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk

SC writes empty script for drop, adds "1" to recreated name