Comparing Views

pince

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

Chris Auckland

Thanks for your post.

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.

pince

Chris,
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)

pince

Chris,

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

Chris Auckland

Thanks for the update.

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.