I would really appreciate some feedback.

Coordinator
Jul 14, 2008 at 3:23 PM
Hello.

I would could really use some feedback from the people, who use SQLToolbox.
Please leave your comments, feature-requests, and help me make a better application we all would enjoy working with.


Feb 25, 2009 at 12:17 PM
Hi,

Love this tool!! I am not sure if some of these are already available but I couldn't figure out, so thought would post them.

  1. Need the option to ignore white-spaces and CASE while comparing
  2. Need the option to export the differences in user-friendly formats like Text file,  Excel file
  3. Need to option to automatically merge changes (similar to Merge tool of TFS)
  4. Need the option to filter object types at the start of comparison, i.e. check-boxes for comparing procedures, tables etc

Jun 24, 2009 at 3:40 PM

I have download a couple of programs that do database comparsions and am currently evaluating them, so I have only had a quick run-through of this program, but some initial thoughts.

1) Your installation program has been compiled for any Target CPU, when it should be targeting just the x86 platform.

As I understand it, you are using SQLDMO which does not work in 64-bit mode, so your program crashes (with the helpful 'SqlToolBox has stopped working' message) when running in a 64-bit Operating system. Works fine if you use Corflags to mark the application as 32-bit only, but not everyone will know to do that.

2) The supplied program as compiled does not really do what I need. (I have not yet looked at the source to see if it can be changed easily to suit my needs.)

In my case, I am comparing two databases with nearly two hundred tables which have come from two different servers. These databases are replicated between each other, but they have gone through some schema updates , and I want to check that my changes have replicated across to the other database.

  However, every single table is maked as different because the servers are using different collations. I would need an option to exclude collation differences.

Every single table is also marked as different because they have related replication triggers which are different. I am not interested in any of these.

There are also hundreds of system generated stored procedures (probably relating to replication) which are on one server but not the other. I am only worried about my stored procedures, not any of the Microsoft stuff. 

So I am so overwhelmed with differences that I can't see if there any important differences.

Sorry to be so negative.

Stephen

Coordinator
Jun 28, 2009 at 7:07 AM

Hi.

Don't worry about being negative -all criticism is appreciated.

Here are my comments:

1) Your installation program has been compiled for any Target CPU, when it should be targeting just the x86 platform.

As I understand it, you are using SQLDMO which does not work in 64-bit mode, so your program crashes (with the helpful 'SqlToolBox has stopped working' message) when running in a 64-bit Operating system. Works fine if you use Corflags to mark the application as 32-bit only, but not everyone will know to do that.

My bad - I'll fix it

2) The supplied program as compiled does not really do what I need. (I have not yet looked at the source to see if it can be changed easily to suit my needs.)

In my case, I am comparing two databases with nearly two hundred tables which have come from two different servers. These databases are replicated between each other, but they have gone through some schema updates , and I want to check that my changes have replicated across to the other database.

However, every single table is maked as different because the servers are using different collations. I would need an option to exclude collation differences.
Every single table is also marked as different because they have related replication triggers which are different. I am not interested in any of these.

I'll look into it. I would appreciate if you could think of a list of objects that the comparison should be able to ignore like the obvious :tables, SPs views; and the less obvious - Collations, (what else?)

There are also hundreds of system generated stored procedures (probably relating to replication) which are on one server but not the other. I am only worried about my stored procedures, not any of the Microsoft stuff.

Which MS SQL version are you working on? In all of my tests (which are done on MSSQL 2005) the application ignores any system objects. If you're working on MSSQL2000 - then I think that the SMO objects are missing a IsSystemObject property, so the I can't diff system objects from non-system ones.

Alexey

Jun 30, 2009 at 10:12 PM

In between my message and your reply, I had downloaded the source code to see if I could hack it. (Incidently, I did have a strange error message which looked like VS was crashing, but googling for the error took me to http://www.brianpeek.com/forums/t/690.aspx  which told me to remove the source control settings which got me going)

> Which MS SQL version are you working on? In all of my tests (which are done on MSSQL 2005) the application ignores any system objects. If you're working on MSSQL2000 - then I think that the SMO objects are missing a IsSystemObject property, so the I can't diff system objects from non-system ones.

This was MSSQL 2005. When I was tracing through the code, I did see it was looking for the IsSystemObject, but it turns out that most of these items have IsSystemObject set false !

There is an object properrty called IsMsShipped which was set true for some of these, but I didn't see this exposed. Since I was in a hurry to try to get some comparsions done on my real database, I just hacked the code a little. This just involved looked at the output and getting rid of the most verbose output

The first thing I did was in the EnumerateSMOOBjectsToSchema method, I ignore any objects where the name starts with either sp_ or MsMerge_  and in the ReadAttachedObject, I ignore any indexes that started with ms_. Of course I could only do this because I know that none of my objects are using names like that. 

The other items I got rid off was system triggers. These do have the IsSystemObject property, but are not being filtered out 

Once I was done that, although every table was still flagged as different, the differences were managable. For example, when I click on a table to see the differences I now longer see serveral pages of triggers and indexes etc, but only the table definition and since the differences were highlighted, it was just a matter of confirming that it was just the collation that was different.

So in the end the SQlToolkit was very helpful, so thanks for it.

> I'll look into it. I would appreciate if you could think of a list of objects that the comparison should be able to ignore like the obvious :tables, SPs views; and the less obvious - Collations, (what else?)

It makes a big difference if your target market are programmers who can hack the source code, or if you want to also target the non-programmer (eg  DB admins).  If the latter then the program would probably need to be very configurable at runtime. For example, if my case I was checking that my database schema changes had all replicated to the subscriber database and was not interested in any of the required procedure triggers etc that has been added to support replication since replication was working fine. But if replication was not working, then perhaps I would want to see those objects.

Not sure how you handle the sp_ MsMerge_ , Ms_ issue unless you give some sort of exclusion list.  For example, I didn't have an issue with statistics, (maybe there were flagged as system objects), but if I was I might need to exclude WA_ items

There are two differences that I have would like to be able to ignore.

Firstly if you compare a table which has replication enabled with one that does not, then the former will have a column added for row_guid. So again if every table is replicated, this flags every table as different.

Secondly I would like to ignore the order of columns.  Over the years we have had to increase the size of some database columns. On some older sql servers that meant moving the data to a temporary column and dropping the old column and creating a new column which means that this column is now at the end of the table. Again this is flagged as a difference in SqlToolkit.

Not really as issue for myself, but some people might want to ignore differences in indexes if they have been optimised per installation.

Can't think of anything else, but I would suspect that are are loads of differences that I have not come across.

 

Hope this helps

Stephen