Options
Determining whether SDC 10 can do what I need
pkortlevers
Posts: 3
Hi,
For a new client we need to automate a process where they send us a new version of their database, which we then compare to the previous version to determine what was changed, and then have the changes processed.
From what I got from the help file I think Sql Data Compare might do the trick, but to ensure we're not ending up with a license for something we can't use after all, I was wondering if you could verify Sql Data Compare can indeed fulfill all of these requirements:
-- Compare a subset of tables from two data sources (at least Sql Server, but support for other DBMS's or even certain file formats would be a plus)
-- Configure which columns should or shouldn't be included in the comparison
-- Export the records that are different to some structured format that can be processed by other applications (I read that CSV exports are supported for one)
-- Configure the export to include or not include certain columns or even additional data from different tables
-- This should all be possible to drive through code (.NET / BizTalk) (preferably by accessing methods in a dll rather than having to start a command line process); the idea is to fully automate this process, so no GUI usage would be possible except maybe for some incidental configuration of the "comparison job" or something like that. In fact, I guess it would actually be a plus if you can set up the process through the GUI, then activate it through code :-) .
Questions I would have:
-- Can the comparison be done on two Sql Server database files, or does it have to be running Sql Server instances?
-- When comparing multiple tables, would this result in multiple exported CSV files, or a single one that holds the changes for multiple tables?
-- Are there any other export options?
-- We have Sql Compare licenses, and I actually have a Sql Data Compare trial on my dev machine (version 9 though). Would that be enough to try all this, or do I need additional dev kits or something like that?
My apologies for the long first post :-). I'm looking forward to your replies.
Thanks,
Patrick
For a new client we need to automate a process where they send us a new version of their database, which we then compare to the previous version to determine what was changed, and then have the changes processed.
From what I got from the help file I think Sql Data Compare might do the trick, but to ensure we're not ending up with a license for something we can't use after all, I was wondering if you could verify Sql Data Compare can indeed fulfill all of these requirements:
-- Compare a subset of tables from two data sources (at least Sql Server, but support for other DBMS's or even certain file formats would be a plus)
-- Configure which columns should or shouldn't be included in the comparison
-- Export the records that are different to some structured format that can be processed by other applications (I read that CSV exports are supported for one)
-- Configure the export to include or not include certain columns or even additional data from different tables
-- This should all be possible to drive through code (.NET / BizTalk) (preferably by accessing methods in a dll rather than having to start a command line process); the idea is to fully automate this process, so no GUI usage would be possible except maybe for some incidental configuration of the "comparison job" or something like that. In fact, I guess it would actually be a plus if you can set up the process through the GUI, then activate it through code :-) .
Questions I would have:
-- Can the comparison be done on two Sql Server database files, or does it have to be running Sql Server instances?
-- When comparing multiple tables, would this result in multiple exported CSV files, or a single one that holds the changes for multiple tables?
-- Are there any other export options?
-- We have Sql Compare licenses, and I actually have a Sql Data Compare trial on my dev machine (version 9 though). Would that be enough to try all this, or do I need additional dev kits or something like that?
My apologies for the long first post :-). I'm looking forward to your replies.
Thanks,
Patrick
Comments
I believe you should have a look at SQL Comparison SDK, which is the API for schema and data comparisons.
http://www.red-gate.com/products/sql-de ... rison-sdk/
The common usage is to compare two databases and output a synchronization script, but you do get access to the results store, so you *could* export data to CSV if you can write your own code to pull from the results store and write to file.
It can compare live databases, scripts folders, and backup files*. It cannot compare detached database files.
*Compare to backup does not perform as well as comparing live databases hosted on a SQL Server.
I installed Comparison SDK, but when trying to run the application I keep getting licensing errors. I had an older version of the toolbelt installed (don't know why, as so far I've only been using our licensed copy of SqlCompare), and that probably used up the trial period.
I uninstalled everything except for SqlCompare, then reinstalled the latest ToolBelt, but the problems persist.
I'm pretty sure that either on the download page or during installation it was mentioned the trial period would be reset, but apparently it wasn't. Is there any way around this?
Thanks again,
Patrick
Did you have a chance to test this out with the SQL Data Compare UI and/or command line? These should give you a pretty good idea what is or isn't possible.
If there is anything you're unable to do with these, then it might be possible through the API, but the command line or GUI is a good starting point.
For example, to export the results as CSV, you can use the following command line switch.
/export:<directory> (Alias: /e)
Exports results as .csv files to the specified directory.
You mentioned previously that you didn't want to use the command line. Was there any particular reason for this if you can automate the task through scheduled task?
If you need to reset your trial, let me know and I'll send you an extension key.