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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy