Primary key issue in HUB using SQL Data

[Hampden Tech]

I have a situation that is causing me some issues. In my MirrorSync configuration I am able to download data from the HUD, make changes in the spoke DB and then sync back up and the changes are properly reflected in the HUB (Microsoft SQL) database.

However, I tried to add a new record on the spoke DB and when I did a sync operation, the error on the HUB indicated that the primary key field could not be null.

We manage the primary keys in our SQL database ourselves, so the next number for each table is stored and updated within the DB. MirrorSync must be expecting that our HUB table is using an Identify column to set the primary key. There is no way that I see of specifying the primary key value in the HUB database. In other words, if I could match up a value in the spoke DB with the primary key and then insert it, I could use an insert trigger on the HUB database to catch this and then assign it a new number in sequence. For example, I could setup a new field in the table called "Temp PK" and set it to some magic number like "999916". Then, I can trap for this on an insert trigger in SQL and assign it the correct way. The only issue that I see with this is that

1. I'm not sure how MirrorSync will be able to match up this new HUB PK with the Spoke PK, and

2. I'm not sure if there would be any conflict issues with multiple users syncing at the same time

 

Any advice on how to work around this would be helpful.

[Jesse Barnum]

This is only the second time I've ever come across this issue, but fortunately we solved it for somebody before you, so you can use the same solution :-)

In the screen where you select the primary key, check the box that says 'Custom PK.' Whatever a new record is being inserted into SQL Server, this SQL statement will be executed, and the result will be used as the value for the primary key.

Use the MirrorSync-managed primary key option in this case. That will make it so that the same record can have different primary keys for different devices / databases, and MirrorSync will keep track of that.

[Hampden Tech]

Thanks for the quick reply Jesse.

I just checked this out. Is that an open SQL query where any SQL can be executed? Can you give me an example of what type of SQL statement you could use there?

Thanks!

Bob

 

[Jesse Barnum]

Here's an example from another customer. I'm not an expert at SQL Server syntax, but I think anything that ends with a SELECT statement will work:

DECLARE @newaccountid char(12)
EXEC [sysdba].[GenerateSLXId]  3, @newaccountid OUTPUT;
SELECT @newaccountid;

[Hampden Tech]

Outstanding! That worked like a charm.

Thanks for the help!