mapping.Obj1.FullyQualifiedName Throws Exception

CoastalData

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

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:

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

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.

Brian Donahue

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.

CoastalData

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.

Brian Donahue

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

C#:
string objectName=mapping.Obj1 == null ? mapping.Obj2.FullyQualifiedName : mapping.Obj1.FullyQualifiedName;

CoastalData

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:

session.CompareDatabases(db1, db2, mappings)

to this:

session.CompareDatabases(db2, db1, mappings)

and then this:

executor.ExecuteBlock(block, Program.LiveServerName, Program.LiveDatabaseName)

to this:

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?

Brian Donahue

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.

CoastalData

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

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?

Brian Donahue

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.

mappings.Options = new EngineDataCompareOptions( 
                                                MappingOptions.Default, 
                                                ComparisonOptions.Default, 
                                                SqlOptions.Default ^ SqlOptions.UseTransactions | SqlOptions.ReseedIdentity); 

...

session.Options = mappings.Options; 

...

provider.Options = session.Options; 

CoastalData

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

CoastalData

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.

Brian Donahue

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