I have put together a 1-way (download) peer-to-peer sync app between 2 SQL Server databases that uses custom DbSyncProviders and custom adapters. The app is straightforard with one exception - I am passing a custom parameter to my UpdateMetaData command
that represents a type of versioning; the param is guid that I use basically as a signature to detect "version differences" between peers.
My tracking tabl looks like:
, [signature] uniqueidentifier
My UpdateMetaData stored proc is straightforward and simpy updates my tracking table normally with the exception of also updating my [Signature] field which is also on the tracking table on the source peer.
MY MAIN CONFUSION is HOW the value of [Signature] gets passed to this stored proc? It works and the [Signature] value gets updated in the tracking table on my destination peer when records are synched from the source peer. I am *not* passing the value via
my app code; the call to the proc is coded in my custom adapter like:
"@CustomerId", SqlDbType.BigInt );
"@" + DbSyncSession.SyncScopeLocalId,
"@" + DbSyncSession.SyncRowIsTombstone,
"@" + DbSyncSession.SyncCreatePeerKey,
"@" + DbSyncSession.SyncCreatePeerTimestamp,
"@" + DbSyncSession.SyncUpdatePeerKey,
"@" + DbSyncSession.SyncUpdatePeerTimestamp,
"@" + DbSyncSession.SyncCheckConcurrency,
"@" + DbSyncSession.SyncRowTimestamp,
"@signature", SqlDbType.UniqueIdentifier, 16 );
"@" + DbSyncSession.SyncRowCount,
SqlDbType.Int ).Direction =
In the SelectChanges command on my source peer, I do include in my Select list the [Signature] value from the source tracking table, although I can't seem to confirm that the value comes from here when synched on the destination peer. Can someone tell me
precisely how the value for a *custom* parameter to an UpdateMetaData command is passed? Although the sample I set up works, I'm not sure how the value is being passed to my custom [signature] paramter, and that makes me feel a little uneasy about my sopution.
Now this is where things get really wierd - in my sample app used above, my base table uses a uniqueidentifier field as the PK on the table. IF I CHANGE this field to be a BigInt and an *IDENTITY* field, then the updating of my [signature] field in my UpdateMetaData
proc on my destination peer fails and for some odd reason I can't explain, my @signature parameter is now passed in a NULL value. What was working in the previous example now fails by passing NULL for my [Signature] parameter when I move to using an IDENTITY
field as my PK field. In my Insert comand, I turn IDENTITY_INSERT ON and OFF like:
And now the @Signature param to the UpdateMetaData proc on my destination peer is always passed a value of NULL.
Can anyone shed light on this bizarre behavior? What I basically need to know is:
1. How are custom parameters passed values to the UpdateMetaData command/proc? Funny thing is that I pass a value (in my app code) to a filter parameter to my SelectChanges proc on my Source peer and it is passed the value fine. If I pass a value for
my UpdateMetaData parm on my destination peer in exactly the same way via app code, it consistently is passed NULL and seemingly ignore the value I set. Very wierd and highly confusing.
2. Are there any caveats or wierdness that is introduced to the sync process simply by having an IDENTITY field as a PK on the base table, requiring setting IDENTITY_INSERT on and off for inserts? This seems to be the only change in my app that breaks everything
and I can't explain it.
Someone please help me understand how this works. It is *very* unintuitive and inconsistent to say the least.