Options
Comparing Views
pince
Posts: 7
I can use SQL Data Compare to compare two views with the same structure in two differant databases, but why can't I do this programmatically? When I create a TableMappings object that contains 1 TableMapping, which is the view, when I invoke CompareDatabases the resulting TableDifferences collection has 0 objects in it.
I have also set the session MappingOptions to IncludeIndexedViews, but the view is NOT indexed (it is not Schema Bound). My sample code is:
Dim session As New ComparisonSession
Dim mappings As New TableMappings
Dim tableMapping As TableMapping = Nothing
If _tableName.ToLower().StartsWith("tbl") Then
tableMapping = CType(mappings.Join(coreDatabase.Tables("[dbo].[" & _tableName & "]"), satelliteDatabase.Tables("[dbo].[" & _tableName & "]")), TableMapping)
Else
tableMapping = CType(mappings.Join(coreDatabase.Views("[dbo].[" & _tableName & "]"), satelliteDatabase.Views("[dbo].[" & _tableName & "]")), TableMapping)
End If
tableMapping.MatchingMappings.Clear()
tableMapping.RefreshMappingStatus()
' Set the custom comparison key for the table
tableMapping.MatchingMappings.Add(tableMapping.FieldMappings("GlobalId"))
mappings.Add(tableMapping)
'compare the databases
Dim mappingOptions As New MappingOptions
mappingOptions = RedGate.SQLDataCompare.Engine.MappingOptions.Default - RedGate.SQLDataCompare.Engine.MappingOptions.IncludeTimestamps + RedGate.SQLDataCompare.Engine.MappingOptions.IncludeIndexedViews
session.Options.MappingOptions = mappingOptions
Dim sessionSettings As New SessionSettings
sessionSettings = RedGate.SQLDataCompare.Engine.SessionSettings.Default - RedGate.SQLDataCompare.Engine.SessionSettings.IncludeIdenticalRecords
session.CompareDatabases(coreDatabase, satelliteDatabase, mappings, sessionSettings)
Dim _difference As TableDifference = session.TableDifferences("[dbo].[" & _tableName & "]")
At this point, if the object I'm comparing is a Table then _difference is not null, but if the object is a view then session.TableDifferences("[dbo].[" & _tableName & "]") does not exist, so _difference is null
I have also set the session MappingOptions to IncludeIndexedViews, but the view is NOT indexed (it is not Schema Bound). My sample code is:
Dim session As New ComparisonSession
Dim mappings As New TableMappings
Dim tableMapping As TableMapping = Nothing
If _tableName.ToLower().StartsWith("tbl") Then
tableMapping = CType(mappings.Join(coreDatabase.Tables("[dbo].[" & _tableName & "]"), satelliteDatabase.Tables("[dbo].[" & _tableName & "]")), TableMapping)
Else
tableMapping = CType(mappings.Join(coreDatabase.Views("[dbo].[" & _tableName & "]"), satelliteDatabase.Views("[dbo].[" & _tableName & "]")), TableMapping)
End If
tableMapping.MatchingMappings.Clear()
tableMapping.RefreshMappingStatus()
' Set the custom comparison key for the table
tableMapping.MatchingMappings.Add(tableMapping.FieldMappings("GlobalId"))
mappings.Add(tableMapping)
'compare the databases
Dim mappingOptions As New MappingOptions
mappingOptions = RedGate.SQLDataCompare.Engine.MappingOptions.Default - RedGate.SQLDataCompare.Engine.MappingOptions.IncludeTimestamps + RedGate.SQLDataCompare.Engine.MappingOptions.IncludeIndexedViews
session.Options.MappingOptions = mappingOptions
Dim sessionSettings As New SessionSettings
sessionSettings = RedGate.SQLDataCompare.Engine.SessionSettings.Default - RedGate.SQLDataCompare.Engine.SessionSettings.IncludeIdenticalRecords
session.CompareDatabases(coreDatabase, satelliteDatabase, mappings, sessionSettings)
Dim _difference As TableDifference = session.TableDifferences("[dbo].[" & _tableName & "]")
At this point, if the object I'm comparing is a Table then _difference is not null, but if the object is a view then session.TableDifferences("[dbo].[" & _tableName & "]") does not exist, so _difference is null
Comments
The first thing I would try would be the use the GUI version of SQL Data compare, and see if you can map together the views using that. If you can get it to work through the GUI, then it will work through the API.
I suspect that maybe the views don't contain a matching unique clustered index, which will mean that you will need to manually set a comparison key.
I hope this helps.
I have used the GUI, and that is why I raised this post because I figured that if it can be done through the GUI, it can be done through the API, but I must be doing something wrong.
I have built the key manually. The lines of code in my example are:
' Set the custom comparison key for the table
tableMapping.MatchingMappings.Add(tableMapping.FieldMappings("GlobalId"))
mappings.Add(tableMapping)
I found the answer to my problem.
When setting the MatchMappings on a table, because there is an index on these columns, the MatchMapping object's status gets set to Success by default. On a non-indexed view, the status is set to UnableToCompare. I had my RefreshMappingStatus() method call in the wrong place.
The compare is now working.
Thanks,
Phil Ince
I'm glad you figured out the problem, rather than needing to wait for me to put a test case together.
Let me know if you need any more information.