Red Gate forums :: View topic - mapping.Obj1.FullyQualifiedName Throws Exception
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

mapping.Obj1.FullyQualifiedName Throws Exception

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



Joined: 18 Jun 2009
Posts: 16

PostPosted: Mon Oct 26, 2009 6:02 am    Post subject: mapping.Obj1.FullyQualifiedName Throws Exception Reply with quote

I don't know why, but when I try to use this function, I'm getting an exception on FullyQualifiedName:

Code:
    Sub AddTableWhereClause(ByVal mappings As TableMappings, ByVal strTableName As String, ByVal strKeyName As String, ByVal intKeyVal As Integer)
        On Error Resume Next
        Dim mapping As TableMapping
        For Each mapping In mappings
            If (mapping.Obj1.FullyQualifiedName = strTableName) Then
                mapping.Include = True
                mapping.Where = New WhereClause(strKeyName & "=" & intKeyVal)
            Else
                mapping.Include = False
            End If
        Next
        On Error GoTo 0
    End Sub


I'm calling it like this:
Code:
                Dim mappings As New TableMappings
                mappings.CreateMappings(db1.Tables, db2.Tables)
                AddTableWhereClause(mappings, "[dbo].[tblClients]", "ClientNo", intClientNo)


Ultimately, I know that this is all overkill -- I'm trying to update just one record of one table that exists and is identical in both databases.

Anybody have any clue what's wrong, or better yet, how to achieve the real goal of simply updating that one record?

Thanks in advance,

--Jon
Back to top
View user's profile Send private message
CoastalData



Joined: 18 Jun 2009
Posts: 16

PostPosted: Mon Oct 26, 2009 6:19 am    Post subject: Reply with quote

I've found I can, for instance, use try/catch or on error resume next to ignore the errors and the code completes, but no records are updated, and so I can't help but be suspicious that this error is playing a part.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6675

PostPosted: Mon Oct 26, 2009 10:37 am    Post subject: Reply with quote

You could try setting a breakpoint and examining your local variables when the probect runs. If "Obj1" is null, then you will get an exception in your code. Obj1 can be null when the table exists in database 2 but not database 1.
Back to top
View user's profile Send private message
CoastalData



Joined: 18 Jun 2009
Posts: 16

PostPosted: Mon Oct 26, 2009 3:25 pm    Post subject: Re: Reply with quote

Brian Donahue wrote:
You could try setting a breakpoint and examining your local variables when the probect runs. If "Obj1" is null, then you will get an exception in your code. Obj1 can be null when the table exists in database 2 but not database 1.


Hmmm, well, yes, in this case, database 2 has more tables than database 1.

So, when looping through each mapping in mappings, which database are we looping through? I had presumed that it was looping through db1, but from what you're saying, it's actually looping through db2.

Because, again in "this case", I wanted to "download" a record from the remote db which is ordinarily the target, I had reversed the source and target.

Maybe I need to keep the source and target the same as for the "regular" syncs, and just change the comparison options? This idea came to me while I was sleeping.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6675

PostPosted: Mon Oct 26, 2009 5:20 pm    Post subject: Reply with quote

Mappings contain a union of objects in both databases, so either Obj1 could be null, or Obj2, but not both. I think I check for nullability in both and then use whichever object is not null
Code:
C#:
string objectName=mapping.Obj1 == null ? mapping.Obj2.FullyQualifiedName : mapping.Obj1.FullyQualifiedName;
Back to top
View user's profile Send private message
CoastalData



Joined: 18 Jun 2009
Posts: 16

PostPosted: Mon Oct 26, 2009 5:43 pm    Post subject: How to force the record to sync no matter what? Reply with quote

Okay, I'm confused now... I ran the where example in the test suite, and figured out how to download a changed record from the "live" database to the "dev" database... I just changed this:
Code:
session.CompareDatabases(db1, db2, mappings)


to this:
Code:
session.CompareDatabases(db2, db1, mappings)


and then this:
Code:
executor.ExecuteBlock(block, Program.LiveServerName, Program.LiveDatabaseName)


to this:
Code:
executor.ExecuteBlock(block, Program.DevServerName, Program.DevDatabaseName)


I then make a change to a record in the "live" db, run the example again, and the change is added to the "dev" db.

BUT, when I change the connection properties to my REAL databases, the changes in the remote record are not detected, and the update is not performed.

What's up with that? How can I force it to JUST MAKE THE UPDATE without even bothering to check for differences? IE, I wouldn't be running this procedure if I did not already know for sure that there was a change!

Ideas?
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6675

PostPosted: Mon Oct 26, 2009 6:14 pm    Post subject: Reply with quote

Data Compare is designed to detect and synchronize differences -- it can't know to do an insert unless a comparison is done to determine that the row of data identified by the comparison key column does not exist in the target database. This way, you always have to do a compare.

If you have to push an entire table, there is a mapping option called "MissingFrom2AsInclude" that will script a bunch of inserts for a table that is missing in db2. However, you must ensure that you create the table in db2 before running the Data script -- the data compare library won't create any necessary schema.

I think you should also be aware that there is an overload in the BuildFromDifferences method called "RunOnTwo" which indicates the direction that the migration will take when the script is generated.

ExecuteBlock is the method where you specify the connection properties of the database against which the script will run, so I think that may be the area for you to focus on.
Back to top
View user's profile Send private message
CoastalData



Joined: 18 Jun 2009
Posts: 16

PostPosted: Mon Oct 26, 2009 6:25 pm    Post subject: Reply with quote

No, in this case there is a record that is already known to exist in both tables, local and remote, and I just want to force an update to that table irregardless of whether or not a change is detected.
Back to top
View user's profile Send private message
CoastalData



Joined: 18 Jun 2009
Posts: 16

PostPosted: Mon Oct 26, 2009 6:59 pm    Post subject: Reply with quote

Aha! After fixing a couple of minor syntax errors, I'm now getting some success! The update statement IS being generated, but when I try to put it into production, the ExecutionBlock never completes...

I tried copying the generated sql into Management Studio, and got the same results... So, I then cut out everything but the actual statement, and then it completed instantly.

In this case, I'm not affecting any relationships, keys, or anything, so there's no need for transactions, options, or DBCC CHECKIDENT... how do I turn all of that stuff off?
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6675

PostPosted: Tue Oct 27, 2009 10:25 am    Post subject: Reply with quote

Hello,
When you execute a block, it should not run forever. You may be silently trapping and handling some SqlExceptions and not knowing it. I'm not sure. But if you want to, you can set up your EngineExecutionOptions to disable reseeding and transactions if you want to.
Code:
mappings.Options = new EngineDataCompareOptions(
                                                MappingOptions.Default,
                                                ComparisonOptions.Default,
                                                SqlOptions.Default ^ SqlOptions.UseTransactions | SqlOptions.ReseedIdentity);

...

session.Options = mappings.Options;

...

provider.Options = session.Options;
Back to top
View user's profile Send private message
CoastalData



Joined: 18 Jun 2009
Posts: 16

PostPosted: Tue Oct 27, 2009 3:24 pm    Post subject: C# to VB Reply with quote

How does the carat (^) translate to VB, is that "and"? I'm pretty sure the pipe is "or"...
Back to top
View user's profile Send private message
CoastalData



Joined: 18 Jun 2009
Posts: 16

PostPosted: Tue Oct 27, 2009 3:26 pm    Post subject: Re: C# to VB Reply with quote

CoastalData wrote:
How does the carat (^) translate to VB, is that "and"? I'm pretty sure the pipe is "or"...


Hmmm, no, that doesn't make sense... it your code seems to be saying "use the default options EXCEPT for UseTransactions or ReseedIdentity", so I'm not quite sure how to mark that up in VB.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6675

PostPosted: Tue Oct 27, 2009 4:08 pm    Post subject: Reply with quote

Hello,

Sorry about that -- I do most of my coding in C#.

^ is an XOR operation -- if UseTransactions is set in Default, this op will unset it

| is bitwise OR

& is bitwise AND
Back to top
View user's profile Send private message
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