Azure Data sync from azure sql hub database to azure sql member database with system versioned tables errors

Question

Azure Data sync from azure sql hub database to azure sql member database with system versioned tables errors

Nguyen, Hoa 421

I have a hub database 'Db1' with 2 selective system versioned tables Table1 and Table2 to sync to a member database 'DB2'.
Both Table1 and Table2 are system versioned.

When I manually sync'ed , I received these errors ( even when the member database DID NOT have the empty tables to start out with).

Hub database
Database provisioning failed with the exception "Cannot insert an explicit value into a GENERATED ALWAYS column in table ‘X1’. Use INSERT with a column list to exclude the GENERATED ALWAYS column, or insert a DEFAULT into GENERATED ALWAYS column.Inner exception: SqlException ID: 98d3c237-0faf-4013-a44a-7f258df9ac5f, Error Code: -2146232060 - SqlError Number:13536, Message: SQL error with code 13536 For more information, provide tracing ID ‘b3452a68-b62c-41b9-942b-69b5e2447416’ to customer support."

Member database
Database provisioning failed with the exception "Incorrect syntax near the keyword 'NOT'.Inner exception: SqlException ID: 93f12b12-d892-4172-b309-a2de1fadcb5f, Error Code: -2146232060 - SqlError Number:156, Message: SQL error with code 156 For more information, provide tracing ID ‘edff9d8e-2c49-449a-91a6-6045f1acdbc1’ to customer support."

Can some throw some light or guidance as I am lost? Thank you in advance.

Nguyen, Hoa 421 Reputation points

2022-06-07T23:22:54.683+00:00
Here is a portion of the period columns from the hub database tables

COl1 Int, Col2 int,

....
[ModifiedBy] [int] NOT NULL,
[CreatedTime] datetime2 NOT NULL,
[SysStartTime] datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
[SysEndTime] datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
CONSTRAINT [PK_Table1] PRIMARY KEY NONCLUSTERED
(
[Table1ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[ Table1History] )
)

Answer accepted by question author

0 additional answers

Your answer

Nguyen, Hoa 421 Reputation points

2022-06-07T23:22:54.683+00:00

Here is a portion of the period columns from the hub database tables

COl1 Int, Col2 int,

....
[ModifiedBy] [int] NOT NULL,
[CreatedTime] datetime2 NOT NULL,
[SysStartTime] datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
[SysEndTime] datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
CONSTRAINT [PK_Table1] PRIMARY KEY NONCLUSTERED
(
[Table1ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[ Table1History] )
)

Answer 1

GeethaThatipatri-MSFT 29,587 Microsoft Employee Moderator

Hi, @Nguyen, Hoa Thanks for posting your question in the Microsoft Q&A forum.
You need to skip the syncing of the system-generated columns in temporal tables.
It is documented here:

Please let me know if you need any additional information.
Regards
Geetha

GeethaThatipatri-MSFT 29,587 Reputation points Microsoft Employee Moderator

2022-06-09T13:43:33.07+00:00

Thank you for visiting the Microsoft QA forums! Have a great day!

Regards
Geetha
Nguyen, Hoa 421 Reputation points

2022-06-09T16:02:30.917+00:00

Thank you for the assistance!

Share via

Azure Data sync from azure sql hub database to azure sql member database with system versioned tables errors

0 additional answers

Your answer