Comparing Views

Automate and integrate using the SQL comparison API

Moderator: Chris Auckland

Comparing Views

Postby pince » Tue Aug 03, 2010 6:21 am

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
pince
 
Posts: 5
Joined: Fri Jul 30, 2010 8:04 am

Postby Chris Auckland » Wed Aug 04, 2010 3:18 pm

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.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby pince » Wed Aug 04, 2010 10:01 pm

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
 
Posts: 5
Joined: Fri Jul 30, 2010 8:04 am

Solved!

Postby pince » Fri Aug 06, 2010 12:55 am

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
pince
 
Posts: 5
Joined: Fri Jul 30, 2010 8:04 am

Postby Chris Auckland » Fri Aug 06, 2010 2:40 pm

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.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.


Return to SQL Comparison SDK Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests