What do you use SQL Toolkit for?
richardjm
Posts: 598
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.
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.
Comments
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?
Project Manager
Red Gate Software Ltd
i don't want to pollute this thread with too many asides though.
Jimboh
Project Manager
Red Gate Software Ltd
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.