Data Compare DTE Commands


In this blog I’ll lead you through the various parameters for the “Data.NewDataComparison” command available in the General Distribution Release (GDR) for Visual Studio Team System 2008 Database Edition. This command launches the Visual Studio Data Compare editor.

If you have the Visual Studio Team System 2008 GDR installed, open it and navigate to the Command Window. To display the Command Window, open the View menu, point to Other Windows, and click Command Window. At the prompt type “Data.NewDataComparison”. The New Data Comparison dialog will appear. This is the expected result when executing “Data.NewDataComparison” without parameters. To avoid this dialog and immediately produce a data comparison result the following parameters are available:

Data.NewDataComparison /SrcServerName srcServer /SrcDatabaseName srcDatabaseName /SrcDisplayName displayName [/SrcUserName username] [/SrcPassword password] /TargetServerName targetServer /TargetDatabaseName targetDatabaseName /TargetDisplayName displayName [/TargetUserName username] [/TargetPassword password]

The command will have the general form is “Data.NewDataComparison <source options> <target options>”

/SrcServerName

/TargetServerName

Specify the name of the server that contains the data for the source/target of the comparison.

/SrcDatabaseName

/TargetDatabaseName

Specify the name of the database that contains the data for the source/target of the comparison.

/SrcDisplayName

/TargetDisplayName

Specify the name that you want to appear in the Schema Compare window for the source/target of the comparison.

/SrcUserName

/TargetUserName

Specify the user name that you want to use to connect to the database that contains the data for the source/target of the comparison.

If you’re using windows authentication you can ignore this option.

/SrcPassword

/TargetPassword

Specify the password for the user name that you want to use to connect to the database that contains the data for the source/target of the comparison. 

If you’re using windows authentication you can ignore this option.

I have two named SQL Server 2008 instances on my box – SQL2008_1 and SQL2008_2. The first instance has a database named ‘Drake’ and the other ‘Josh’. I’m using windows authentication, so no username or password is required.  A Data Compare session can be launched by typing the following command into the Command Window

Data.NewDataComparison /SrcServerName .\SQL2008_1 /SrcDatabaseName Drake /SrcDisplayName Drake /TargetServerName .\SQL2008_2 /TargetDatabaseName Josh /TargetDisplayName Josh

If differences are available, I can now use the command “Data.DataCompareExportToFile [filename]” to create a data update script. When I execute “Data.DataCompareExportToFile C:\JoshUpgrade.sql" in the Command Window a data upgrade script will be created.

Conclusion

At this point I’ve illustrated how to use the Import Script, Schema Compare and Data Compare DTE commands. I hope you’re starting to see the flexibility this provides for authoring your own addins, packages or applications tailored to your specific requirements.

– Patrick Sirr

“DataDude” Programmer


Comments (7)

  1. Data Dude says:

    Patrick Sirr , one of the key developers in our team started his own blog. If you want to learn about

  2. Data Dude says:

    Finally the moment is there, the final version of the Visual Studio Team System 2008 Database Edition

  3. Ca n’est pas encore Visual Studio 2010, mais pour la partie base de données, on s’en approche à grand

  4. paulsmith68 says:

    Thanks for this post; I’m surprised how little documentation is out there on scripting out Visual Studio’s Schema Compare and Data Compare.

    Is there really no way to script a list of tables/views to use in a Data Comparison? I’d love to automate reference data synchronization as part of my build process, since it’s one of the last things left that we actually have to do by hand.

    Using bit of automation on top of VS’s Data Compare to accomplish this would be ideal, if it were possible.

  5. bahill says:

    Hi Paul,

    No, sorry, there is not a way to select specific tables/views through DTE.

    You can automate the synchronization of your reference data as a part of the deploy process by including the sync in the database project’s post deployment script.  The upside to this is the reference data is source code controlled.  Check out this example and some of the other resources others have suggested: http://blogs.msdn.com/bahill/archive/2009/04/01/maintaining-and-synchronizing-your-reference-data.aspx

    Thanks.

    Thanks.

  6. Ralf says:

    When does one know 'Data.NewDataComparison' is ready comparing?

    (And when I say 'one' I mean DTE ExecuteCommand)

    Thanks!

  7. Albert says:

    Patrick Sirr really nice post. I wanted to ask for any option to execute this data and schema comparison from a Powersheell script. The tool is very usefull but there's no way to get a report or log id the schema are equal for example. So, for documentation proporse is missing something…thanks