Red Gate forums :: View topic - Comparing Views
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK Previous Versions
SQL Comparison SDK Previous Versions forum

Comparing Views

Search in SQL Comparison SDK Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
pince



Joined: 30 Jul 2010
Posts: 5

PostPosted: Tue Aug 03, 2010 6:21 am    Post subject: Comparing Views Reply with quote

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
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Wed Aug 04, 2010 3:18 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
pince



Joined: 30 Jul 2010
Posts: 5

PostPosted: Wed Aug 04, 2010 10:01 pm    Post subject: Reply with quote

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)
Back to top
View user's profile Send private message
pince



Joined: 30 Jul 2010
Posts: 5

PostPosted: Fri Aug 06, 2010 12:55 am    Post subject: Solved! Reply with quote

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. Smile
Thanks,
Phil Ince
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Fri Aug 06, 2010 2:40 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group