June 26, 2018 at 3:35 pm
Hi
I inherited old system and trying to copy replication bewteen sql2012 servers standard edition
We have ServerA and ServerB
We have 3 WORKING!!! Transaction replications
1 and 2
ServerA database Customers to ServerB database Billing
ServerA database Inventory to ServerB database Billing
3
ServerB database Billing to ServerA database Customers
I successfuly restored databases from ServerA to ServerA_copy and ServerB to ServerB_copy and scripted and enabled Transaction replications from
ServerA_copy database Customers to ServerB_copy database Billing
ServerA_copy database Inventory to ServerB_copy database Billing
when I scripted and enabled replication
ServerB_copy database Billing to ServerA_copy database Customers
I started to see errors bellow in replication monitor for replication 1 and 2 at publisher
"Explicit value must be specified for identity column in table 'orders' either when IDENTITY_INSERT is set to ON
or when a replication user is inserting into a NOT FOR REPLICATION identity column. (Source: MSSQLServer, Error number: 545)
""
"Explicit value must be specified for identity column in table 'MoneyTranser' either when IDENTITY_INSERT is set to ON
or when a replication user is inserting into a NOT FOR REPLICATION identity column. (Source: MSSQLServer, Error number: 545)
""
Following was discovered
1.in replication ServerA_copy database Customers to ServerB_copy database Billing:
after data replicated to database Billing SERVERAL after insert triggers executed from multiple table and inserting data into table Orders (contain identity column)
2. replication ServerA_copy database Inventory to ServerB_copy database Billing :
after data replicated to database Billing SERVERAL after insert triggers executed from multiple table and inserting data into table MoneyTranser(contain identity column)
as workaround I tried add
SET IDENTITY_INSERT "sometableWithIdentity" OFF in each trigger but still getting "Explicit value must be specified for identity " errorsAfter setting replication from ServerB_copy database Billing to ServerA_copy database Customers I used to "Red gate sql compare tool " to compare schema at database Billing on serverB and serverB_copy
I discovered that serverB
TABLES orders and MoneyTranser used by replication defined as
CREATE TABLE [dbo].[]
(
[ID] [int] NOT NULL IDENTITY(1, 1)
while
serverB_copy same tables used by replication defined
CREATE TABLE [dbo].[]
(
[[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL
Questions
What am I missing in my replication scripts for serverB_copy ?
June 26, 2018 at 5:10 pm
ebooklub - Tuesday, June 26, 2018 3:35 PMHiI inherited old system and trying to copy replication bewteen sql2012 servers standard edition
We have ServerA and ServerB
We have 3 WORKING!!! Transaction replications
1 and 2
ServerA database Customers to ServerB database Billing
ServerA database Inventory to ServerB database Billing3
ServerB database Billing to ServerA database CustomersI successfuly restored databases from ServerA to ServerA_copy and ServerB to ServerB_copy and scripted and enabled Transaction replications from
ServerA_copy database Customers to ServerB_copy database Billing
ServerA_copy database Inventory to ServerB_copy database Billingwhen I scripted and enabled replication
ServerB_copy database Billing to ServerA_copy database CustomersI started to see errors bellow in replication monitor for replication 1 and 2 at publisher
"Explicit value must be specified for identity column in table 'orders' either when IDENTITY_INSERT is set to ON
or when a replication user is inserting into a NOT FOR REPLICATION identity column. (Source: MSSQLServer, Error number: 545)
"""Explicit value must be specified for identity column in table 'MoneyTranser' either when IDENTITY_INSERT is set to ON
or when a replication user is inserting into a NOT FOR REPLICATION identity column. (Source: MSSQLServer, Error number: 545)
""Following was discovered
1.in replication ServerA_copy database Customers to ServerB_copy database Billing:
after data replicated to database Billing SERVERAL after insert triggers executed from multiple table and inserting data into table Orders (contain identity column)
2. replication ServerA_copy database Inventory to ServerB_copy database Billing :
after data replicated to database Billing SERVERAL after insert triggers executed from multiple table and inserting data into table MoneyTranser(contain identity column)
as workaround I tried add
SET IDENTITY_INSERT "sometableWithIdentity" OFF in each trigger but still getting "Explicit value must be specified for identity " errorsAfter setting replication from ServerB_copy database Billing to ServerA_copy database Customers I used to "Red gate sql compare tool " to compare schema at database Billing on serverB and serverB_copyI discovered that serverB
TABLES orders and MoneyTranser used by replication defined asCREATE TABLE [dbo].[]
(
[ID] [int] NOT NULL IDENTITY(1, 1)while
serverB_copy same tables used by replication defined
CREATE TABLE [dbo].[]
(
[[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULLQuestions
What am I missing in my replication scripts for serverB_copy ?
Did you create the triggers using NOT FOR REPLICATION. Or is that property set for the triggers?
Sue
June 27, 2018 at 11:27 am
1. no, all triggers defined without NOT FOR REPLICATION
I found workaround for my case
As as said tables used by publisher in db serverB and ServerB_copy haves different defintions
CREATE TABLE [dbo].[]
(
[ID] [int] NOT NULL IDENTITY(1, 1)
while
serverB_copy same tables used by replication defined
CREATE TABLE [dbo].[]
(
[[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL
1.I drooped all the triggers that referencing replicated tabels on ServerB_copy database Billing
2. Dropped recreated and enabled replication from ServerB_copy database Billing to ServerA_copy database Customers
Once publication created I executed
ALTER TABLE [dbo].[orders] ALTER COLUMN [ID] DROP NOT FOR REPLICATION
ALTER TABLE [dbo].[moneystransfer] ALTER COLUMN [ID] DROP NOT FOR REPLICATION
3. start replication snapshot and review replication status
after snapshot applied to ServerA_copy database Customers
I executed script that recreate all triggers on ServerB_copy database Billing
Replication up and running
db schema currently identical on serverA and serverA_copy and serverB and serverB_copy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply