What do you use SQL Toolkit for?

richardjm

After an interesting post about using SQL Toolkit to check the scripts for upgrading a database I thought I'd create a sticky.

Please respond with what you're using SQL Toolkit for, how we can make it better and possibly even coding tips for other users.

Have fun people.

jimboh

Just can't understand how you have had no responses in nearly two years. No doubt there are a number of uses and wanted features that are hidden amongst the various posts.
I'm just starting out exploring the possibilities. But to name one use - its looking handy to allow me to generate exports of static data to csv/xml files for version control ( similar to the SQLDataCompare Export but without the duplicate columns). Obviously i have to provide my own file persistence, but you might think about this.
On a slight diversion ... when using SQLDataCompare against a SQL Server (2000) database backup in the UI, the button for the "Where clause" in the table selection configuration is disabled. But it is available when comparing a live database. Can you explain this?

richardjm

Sadly unless we were to implement SQL Server in it's entirity we can't support WHERE clauses when comparing to a backup. This is because we read directly from the binary backup format and re-create the data of the tables without even needing SQL Server.

jimboh

But can't the condition still be applied to the LHS ( live side) ?
i don't want to pollute this thread with too many asides though.
Jimboh

richardjm

We try to keep it symmetric but yes it could be - but isn't

PeterHageus

I'll primarily use it to update customer databases, and insert lookup data.

Justin Spindler

My company uses SQL Toolkit to automate SQL Compare in an application we use to distribute schema changes to our clients when they upgrade to a newer version of our product. It works quite well for this.

We did have to write our own data synchronization due to limitations with SQL Data Compare at the time that the tool was written, mostly due to problems with our database schema, such as the lack of proper keys. We also wanted additional logic on how to identify the records in question and what to do with a record if it already existed. It's possible that SQL Data Compare does handle all of this now. For example, we might identify a record by a combination of two VARCHAR fields that are not the primary key. If the record already exists we might optionally overwrite the contents of some of the other fields of that record with data within our snapshot, but leave the over fields alone.

The one area we had a lot of problems with SQL Compare is that the level of granularity on differences does not go any deeper than table-level. For better or worse (definitely worse), our software is sold with the idea that the client may alter the schema. We now try to discourage modifying the tables that we ship and keeping schema changes to custom tables, but some of our clients still have and rely on these custom changes. By default SQL Compare would drop these extraneous columns as they would not exist in the model snapshot. In order to overcome this, and several similar situations, I had to resort to .NET reflection to add those extra columns to the underlying object model of the database snapshot so that SQL Compare would think that those columns belong there and would not touch them. This is certainly a fragile solution and I fear that an SQL Toolkit upgrade might break it. I would really like to see built-in support for being able to ignore differences down to the column and index level, or maybe some supported way of modifying the snapshot in code.