Filters for SQL Compare ?
Gary Kuipers
Posts: 5
There are certain improvements in version 5 which increase the amount of time required to synchronize databases. Even though the differences are trivial, each and every one of them has to be reviewed from top to bottom every single time that a comparison is made.
Is there a filter that can be used to "ignore" the following situations?
1) The only difference is -- Stored Procedure is not equal to -- Stored Procedure (there is no difference, the rest of the comparison shows no differences, only the first line which seems to be placed there by SQL Compare in the first place).
2) The only difference is that one side has "SET QUOTED_IDENTIFIER OFF" and the other does not.
3) The only difference is that one is SQL 2000 and names its stored procedure "StoredProcedureX" and the other is SQL 2005 and has it named "[dbo].[StoredProcedureX]"
4) The only difference in a view is the name of the target DB (example "CREATE VIEW xyz AS SELECT * FROM X.xy versus CREATE VIEW xyz AS SELECT * FROM X.xy2"). It would be nice to have something that says "X.xy is equivalent to X.xy2".
5) One side says " [DF_Event_vin] DEFAULT ((0))" and the other says " [DF_Event_vin] DEFAULT (0)"
6) One side says IDENTITY(1,1) and the other says "IDENTITY(10,1). It does not really mater as there are hundreds of records already in the table and the starting value is immaterial).
7) And my favorite of all becasue it causes the include of every single table as a difference even though there is really no significant difference: One side has "IDENTITY(1,1) NOT FOR REPLICATION" and the other side lacks the "NOT FOR REPLICATION". Some customers replicate, others don't.
The reason I ask is because it is getting to the point where I can do the following and be faster at spotting changes than by using your tools:
1) Get a schema of the DB from both databases
2) Run a perl program that addresses the 7 issues above
3) use a text difference tool
4) Apply the change manually
Any information would be appreciated, even if that means there is no cure for the problems and I need to stop using SQL compare and rely on my other procedure.
Thanks!
Gary Kuipers
Is there a filter that can be used to "ignore" the following situations?
1) The only difference is -- Stored Procedure is not equal to -- Stored Procedure (there is no difference, the rest of the comparison shows no differences, only the first line which seems to be placed there by SQL Compare in the first place).
2) The only difference is that one side has "SET QUOTED_IDENTIFIER OFF" and the other does not.
3) The only difference is that one is SQL 2000 and names its stored procedure "StoredProcedureX" and the other is SQL 2005 and has it named "[dbo].[StoredProcedureX]"
4) The only difference in a view is the name of the target DB (example "CREATE VIEW xyz AS SELECT * FROM X.xy versus CREATE VIEW xyz AS SELECT * FROM X.xy2"). It would be nice to have something that says "X.xy is equivalent to X.xy2".
5) One side says " [DF_Event_vin] DEFAULT ((0))" and the other says " [DF_Event_vin] DEFAULT (0)"
6) One side says IDENTITY(1,1) and the other says "IDENTITY(10,1). It does not really mater as there are hundreds of records already in the table and the starting value is immaterial).
7) And my favorite of all becasue it causes the include of every single table as a difference even though there is really no significant difference: One side has "IDENTITY(1,1) NOT FOR REPLICATION" and the other side lacks the "NOT FOR REPLICATION". Some customers replicate, others don't.
The reason I ask is because it is getting to the point where I can do the following and be faster at spotting changes than by using your tools:
1) Get a schema of the DB from both databases
2) Run a perl program that addresses the 7 issues above
3) use a text difference tool
4) Apply the change manually
Any information would be appreciated, even if that means there is no cure for the problems and I need to stop using SQL compare and rely on my other procedure.
Thanks!
Gary Kuipers
Comments
Sorry to hear that you experiences with SQL Compare 5 haven't been as one would have hoped. I will try to address your points as best I can, but can I ask which version of SQL Compare you were using before v5?
1) If you cannot see a different then it probably is in a line ending, can you check that your line endings are identical. This issue (66330) is marked down for addressing in a future version of SQL Compare.
2) There is an Ignore "SET QUOTED_IDENITIFER and SET ANSI NULLS" option in the Project Configuration > Options > More Options dialog which should give you what you need here.
3) I completely agree with you that this is a bug (68764) and a very idiotic one at that :oops:. We will be addressing it in future versions of the product. Hopefully, the first point release.
4) Because SQL Compare used the object definitions straight from the SQL Server system catalogs then I think that we would always consider this a difference the names are different therefore the object is different.
5) The problem you are seeing is because of the two ways that SQL Compare compares objects. At the start of the comparison, or each time you refresh, the engine does a semantic comparison of all the objects in the data sources and stores these results in the top grid.
However, each time you select an object in the top grid the SQL Differences pane does a simple textual comparison of objects. This is done to improve performance and memory usage, and because 90% of textual differences are actually semantic differences too.
However, in some cases like the one you highlight, a textual difference isn't actually a semantic difference as extra brackets don't change the result of the default. But the SQL Differences pane will highlight the textual differences although the top grid reports the object as identical. As we display most of these objects exactly as they are stored in the system catalogs we don't feel that we should alter them, and some of these differences may actually be of interest to users.
The extra brackets that SQL Server 2005 adds shouldn't cause the top grid to be report a difference if that is the only difference in the object.
This behaviour does cause a fair amount of confusion when looking at results so we are going to have to look into improving the SQL Differences pane difference matching in future releases, but this won't be changed in the initial point release of version 6.
6) There is also an ignore "Identity seed and increment values" in the same location as the option in (2) that should help.
7) This will be hopefully addressed in SQL Compare 6.1
If you have any further queries, you can contact me in this tread or via email (below).
Regards,
Jonathan :
-Project Manager
-Red Gate Software Ltd
Still, I think you should allow for the processing of "equivalent" strings. It would be simple to create an equivalence dictionary for a project and simply do a substitute in line with the comparison processing and have a switch that says "apply or do not apply equivalent strings in the comparison"
Would make it real easy and is probably a 4 hour project (plus the requisite 72 hours of debugging and the 1000 bug reports that seem to follow changes, right?)
Thanks again