mapping.Obj1.FullyQualifiedName Throws Exception

Automate and integrate using the SQL comparison API

Moderator: Chris Auckland

mapping.Obj1.FullyQualifiedName Throws Exception

Postby CoastalData » Mon Oct 26, 2009 6:02 am

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

Code: Select all
    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: Select all
                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
CoastalData
 
Posts: 16
Joined: Thu Jun 18, 2009 9:03 pm

Postby CoastalData » Mon Oct 26, 2009 6:19 am

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.
CoastalData
 
Posts: 16
Joined: Thu Jun 18, 2009 9:03 pm

Postby Brian Donahue » Mon Oct 26, 2009 10:37 am

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Re:

Postby CoastalData » Mon Oct 26, 2009 3:25 pm

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.
CoastalData
 
Posts: 16
Joined: Thu Jun 18, 2009 9:03 pm

Postby Brian Donahue » Mon Oct 26, 2009 5:20 pm

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: Select all
C#:
string objectName=mapping.Obj1 == null ? mapping.Obj2.FullyQualifiedName : mapping.Obj1.FullyQualifiedName;
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

How to force the record to sync no matter what?

Postby CoastalData » Mon Oct 26, 2009 5:43 pm

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: Select all
session.CompareDatabases(db1, db2, mappings)


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


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


to this:
Code: Select all
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?
CoastalData
 
Posts: 16
Joined: Thu Jun 18, 2009 9:03 pm

Postby Brian Donahue » Mon Oct 26, 2009 6:14 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby CoastalData » Mon Oct 26, 2009 6:25 pm

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.
CoastalData
 
Posts: 16
Joined: Thu Jun 18, 2009 9:03 pm

Postby CoastalData » Mon Oct 26, 2009 6:59 pm

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?
CoastalData
 
Posts: 16
Joined: Thu Jun 18, 2009 9:03 pm

Postby Brian Donahue » Tue Oct 27, 2009 10:25 am

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: Select all
mappings.Options = new EngineDataCompareOptions(
                                                MappingOptions.Default,
                                                ComparisonOptions.Default,
                                                SqlOptions.Default ^ SqlOptions.UseTransactions | SqlOptions.ReseedIdentity);

...

session.Options = mappings.Options;

...

provider.Options = session.Options;
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

C# to VB

Postby CoastalData » Tue Oct 27, 2009 3:24 pm

How does the carat (^) translate to VB, is that "and"? I'm pretty sure the pipe is "or"...
CoastalData
 
Posts: 16
Joined: Thu Jun 18, 2009 9:03 pm

Re: C# to VB

Postby CoastalData » Tue Oct 27, 2009 3:26 pm

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.
CoastalData
 
Posts: 16
Joined: Thu Jun 18, 2009 9:03 pm

Postby Brian Donahue » Tue Oct 27, 2009 4:08 pm

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
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Comparison SDK Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests