explicit revision number may destroy target database

maturmel

Hi,

I have noticed that when using sqlcompare command-line with parameters:
/sourcecontrol1 and /revision with a specific revision number, if that number does not happen to be a changeset that includes changes to the scripts folder in Source control, SQL compare seems to generate an empty DB as the source of comparison. This results in generating a script that drops everything on the database.

I am looking at this scenario for automation from a build server (TeamCity in this case). I wish to pass explicity the revision number (there's a team city variable suited for that) rather than using HEAD as it seems conceptually more appropriate (in case something is checked in between the moment the build started and when it gets to actually run SQLcompare.exe, as I would have several other build steps before that).

I have been able to replicate the same issue with the GUI SQL Compare, I just put an explicit revision number that is a code check-in (not database objects), and I get everything marked as 'drop' in target db.

For example:
My latest changeset in numbered 6639. My latest changeset of DB objects was 6634. If I use /revision: with a number between 6635-6639, it yield what I described above.

My source control is Vault Standard.

Brian Donahue

Thanks for contacting us. I'm trying to reproduce the problem using SVN, and not having any success. If I can't get it to happen in SVN, I'll have to set up a Vault server and this can take some time.

maturmel

Thanks for replying Brian. I also got notification that a support ticket was opened - under reference # F0064326.

Chris Auckland

I've had a chance to try this out with Vault, but I can't reproduce the behaviour either.

This is how I tested it, maybe you can spot what I'm doing wrong:

1) Created a database called 'Test'
2) Linked to Vault repository
3) Created table 'Table_a'
4) Checked in, this was revision 596
5) Deployed revision 596 to another database (Test_B)
6) Outside of SQL Source Control I added a file text.txt to the repository. This was revision 597
7) Set revision 597 as the source and compared to Test_B. This was reported as identical (is there you would expect a drop of Table_a to be scripted?)
8) Added a view 'View_a' - this was revision 598
9) If I deployed 598, it added the view to Test_B
10) Set revision 597 as the source. This time it wanted to drop the view, but that's only because it was created in a later revision, so it worked as I expected.

Is this different to the behaviour you're seeing.

Could you send any reproduction steps that I could try out?

maturmel

Hi Chris,

I believe the following sequence would mimic what I have been doing:

1) create a new table "table_b"
2) check in
3) check in some other file (no SQL related) to the repo
4) try to deploy with source being the revision number obtained by check-in at step #3.

What I'm trying to achieve is to actually deploy code+DB kind of "all at once" as check-ins in steps #2 and #3 in the above would represent my DB changes and VS code related to db changes.

Also, maybe it's something in my command-line parameters (I first found it from CMD line but also replicated in SQL Compare UI). Here's the command line I executed:

SQLCompare.exe /sourcecontrol1 /revision1:<revisionnumber> /scriptsfolderxml:scriptsfolderxml.xml /migrationfolderxml:migrationsfolderxml.xml /db2:<target_db>
/server2:(local) /Include:Identical /Report:BuildPackage\SchemaDiffReport.html /ReportType:Interactive /ScriptFile:BuildPackage\SchemaUpdate_version.sql /Force /Verbose

If that can help I am willing to setup a web link to demo it directly on my server.
Marc-Andre

Chris Auckland

Sorry for the delay, I missed you last update.

I'm pretty sure your sequence of steps are exactly what I was doing, so maybe this would be good to see happening on your machine.

When would be a good time to schedule a remote session?

maturmel

Hi Chris,

No worries for the delay. I have been busy as well.

For an online session, it works best for me on mornings. Tomorrow and Friday are good for me. Anytime between 9-12 EDT.

Can you email me at marct[AT]technicost.com and we can schedule a phone appointment. I will setup a session using http://join.me

I have a batch file invoking the SQL Compare command line already setup to replicate the issue at will.

Thank you very much.
Marc-Andre