Re: Two Way Bidirectional Rep

There is nothing at all wrong with your script. When the snapshot for
transactional replication is generated, the identity property is removed
from the table creation script being sent to the subscriber. This happens,
because transactional replication does not expect changes to be issued
against that database, therefore an identity column is irrelevant. All
bi-directional replication is doing is creating 2 transactional publications
going in opposite directions. It does not change this behavior. So, the
only way to get an identity on your tables is to do this yourself. The
snapshot process also generates parameterized stored procedure which is what
the replication engine actually uses to issue the transactions against the
subscriber. Because this is transactional, the snapshot process just
generates a generic insert statement since transactional does not expect an
identity column to exist on a subscriber.

SO, you have one more step to perform which you must also do manually. That
is to open the parameterized stored procedures and add the code to set
identity_insert on as well as add the column list to the insert statements.
Until you do this final step, you will ALWAYS get this error message.

--
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com


"CLM" <CLM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:34B85BDE-7700-4422-8DD9-8DE19EC93287@xxxxxxxxxxxxxxxx
> Guys, sorry to cause trouble. Let me post my entire script with what I
> did.
> I'm sure I'm just missing one small point or mistake. I didn't do that
> originally cuzz I didn't want to overwhelm anyone. But maybe that will
> help.
> Here it is from serverA, which I consider my "primary":
>
> use web_tsk_mgmt
> go
> alter table tbl_IssueTracker_IssueCategories
> with nocheck add constraint category_id_range2 check NOT FOR REPLICATION
> ( category_id BETWEEN 1 and 10000 )
> go
>
> use master
> go
> sp_adddistributor @distributor = 'RNDSQL2'
> go
> use master
> go
> sp_adddistributiondb @database='distribution'
> go
>
> use master
> go
> --Notice the password!
> exec sp_adddistpublisher
> @publisher = 'RNDSQL2',
> @distribution_db ='distribution',
> @security_mode = 0,
> @login = 'sa',
> @password = '',
> @working_directory ='d:\data'
>
> use master
> go
> exec sp_replicationdboption N'WEB_TSK_MGMT', N'publish', true
> go
>
> use WEB_TSK_MGMT
> go
> -- Adding the transactional publication.
> exec sp_addpublication
> @publication = N'WEB_TSK_MGMT',
> @restricted = N'false',
> @sync_method = N'native',
> @repl_freq = N'continuous',
> @description = N'Transactional publication of database.',
> @status = N'active',
> @allow_push = N'true',
> @allow_pull = N'true',
> @allow_anonymous = N'false',
> @enabled_for_internet = N'false',
> @independent_agent = N'false',
> @immediate_sync = N'false',
> @allow_sync_tran = N'true',
> @autogen_sync_procs = N'true',
> @retention = 72
> go
> exec sp_addpublication_snapshot
> @publication = N'WEB_TSK_MGMT',
> @frequency_type = 4,
> @frequency_interval = 1,
> @frequency_relative_interval = 0,
> @frequency_recurrence_factor = 1,
> @frequency_subday = 1,
> @frequency_subday_interval = 0,
> @active_start_date = 0,
> @active_end_date = 0,
> @active_start_time_of_day = 233000,
> @active_end_time_of_day = 0
> go
> -- Adding the transactional articles.
> sp_addarticle
> @publication = N'WEB_TSK_MGMT',
> @article = N'tbl_IssueTracker_IssueCategories',
> @source_owner = N'dbo',
> @source_object = N'tbl_IssueTracker_IssueCategories',
> @destination_table = N'tbl_IssueTracker_IssueCategories',
> @type = N'logbased',
> @creation_script = null,
> @description = null,
> @pre_creation_cmd = N'drop',
> @schema_option = 0x00000000000000F1,
> @status = 16,
> @vertical_partition = N'false',
> @filter = null,
> @sync_object = null
> go
>
> --Notice the password!
> exec sp_addsubscriber
> @subscriber = 'GRSSQLQA',
> @login = 'sa',
> @password = ''
> go
>
> use web_tsk_mgmt
> go
> exec sp_addsubscription
> @publication = N'WEB_TSK_MGMT',
> @article = N'tbl_IssueTracker_IssueCategories',
> @subscriber = 'GRSSQLQA',
> @destination_db = N'WEB_TSK_MGMT',
> @sync_type = N'none',
> @status = N'active',
> @update_mode = N'sync tran',
> @loopback_detection = 'true'
> go
>
> --This showed there were 0 records over there:
> select count(*) from dbo.tbl_IssueTracker_IssueCategories (nolock)
> --Build a DTS package that has Enable Identity Insert checked and
> -- Check Constraints unchecked.
> select * from dbo.tbl_IssueTracker_IssueCategories (nolock)
> --delete from tbl_IssueTracker_IssueCategories where category_id = 7
>
> --This was built for testing:
> -- The first column, category_id, is the identity:
> insert tbl_IssueTracker_IssueCategories
> ([name], parent_category_id)
> values ('name1', 20)
>
>
> And here it is for serverB if you will:
>
> CREATE TABLE [dbo].[tbl_IssueTracker_IssueCategories] (
> [category_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
> [name] [varchar] (100) NOT NULL ,
> [parent_category_id] [int] NOT NULL ,
> [msrepl_tran_version] [uniqueidentifier] NOT NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tbl_IssueTracker_IssueCategories] WITH NOCHECK ADD
> CONSTRAINT [PK_tbl_IssueTracker_IssueCategories] PRIMARY KEY CLUSTERED
> (
> [category_id]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tbl_IssueTracker_IssueCategories] ADD
> CONSTRAINT [DF__tbl_Issue__msrep__6C39D5A3] DEFAULT (newid()) FOR
> [msrepl_tran_version]
> GO
>
> alter table tbl_IssueTracker_IssueCategories
> with nocheck add constraint category_id_range2 check NOT FOR REPLICATION
> ( category_id BETWEEN 10001 and 20000 )
> go
>
> use master
> go
> sp_adddistributor @distributor = 'GRSSQLQA'
> go
> use master
> go
> sp_adddistributiondb @database='distribution'
> go
>
> use master
> go
> --I am running the script below on a dev server. When I run the last
> step,
> sp_addpublication, the log reader job fails with the following error msg:
>
> "The process could not connect to server 'GRSSQL'. The step failed."
>
> use master
> go
> sp_adddistributor @distributor = 'GRSSQL'
> go
> use master
> go
> sp_adddistributiondb @database='distribution'
> go
>
> use master
> go
> exec sp_adddistpublisher
> @publisher = 'GRSSQL',
> @distribution_db ='distribution',
> @security_mode = 0,
> @login = 'sa',
> @password = '',
> @working_directory ='e:\data'
>
> use master
> go
> exec sp_replicationdboption N'WEB_TSK_MGMT', N'publish', true
> go
>
> use WEB_TSK_MG
> go
> -- Adding the transactional publication.
> exec sp_addpublication
> @publication = N'WEB_TSK_MG',
> @restricted = N'false',
> @sync_method = N'native',
> @repl_freq = N'continuous',
> @description = N'Transactional publication of database.',
> @status = N'active',
> @allow_push = N'true',
> @allow_pull = N'true',
> @allow_anonymous = N'false',
> @enabled_for_internet = N'false',
> @independent_agent = N'false',
> @immediate_sync = N'false',
> @allow_sync_tran = N'true',
> @autogen_sync_procs = N'true',
> @retention = 72
> go
>
> I am running the script below on a dev server. When I run the last step,
> sp_addpublication, the log reader job fails with the following error msg:
>
> "The process could not connect to server 'GRSSQL'. The step failed."
>
> use master
> go
> sp_adddistributor @distributor = 'GRSSQL'
> go
> use master
> go
> sp_adddistributiondb @database='distribution'
> go
>
> --Notice the password!
> use master
> go
> exec sp_adddistpublisher
> @publisher = 'GRSSQL',
> @distribution_db ='distribution',
> @security_mode = 0,
> @login = 'sa',
> @password = '',
> @working_directory ='e:\data'
>
> use master
> go
> exec sp_replicationdboption N'WEB_TSK_MGMT', N'publish', true
> go
>
> use WEB_TSK_MG
> go
> -- Adding the transactional publication.
> exec sp_addpublication
> @publication = N'WEB_TSK_MG',
> @restricted = N'false',
> @sync_method = N'native',
> @repl_freq = N'continuous',
> @description = N'Transactional publication of database.',
> @status = N'active',
> @allow_push = N'true',
> @allow_pull = N'true',
> @allow_anonymous = N'false',
> @enabled_for_internet = N'false',
> @independent_agent = N'false',
> @immediate_sync = N'false',
> @allow_sync_tran = N'true',
> @autogen_sync_procs = N'true',
> @retention = 72
> go
>
>
> exec sp_adddistpublisher
> @publisher = 'GRSSQLQA',
> @distribution_db ='distribution',
> @security_mode = 0,
> @login = 'sa',
> @password = '',
> @working_directory ='e:\data'
>
> use master
> go
> exec sp_replicationdboption N'WEB_TSK_MGMT', N'publish', true
> go
>
> use WEB_TSK_MGMT
> go
> -- Adding the transactional publication.
> exec sp_addpublication
> @publication = N'WEB_TSK_MGMT',
> @restricted = N'false',
> @sync_method = N'native',
> @repl_freq = N'continuous',
> @description = N'Transactional publication of database.',
> @status = N'active',
> @allow_push = N'true',
> @allow_pull = N'true',
> @allow_anonymous = N'false',
> @enabled_for_internet = N'false',
> @independent_agent = N'false',
> @immediate_sync = N'false',
> @allow_sync_tran = N'true',
> @autogen_sync_procs = N'true',
> @retention = 72
> go
>
> --Be sure to disable the snapshot. You don't want it to work as it would
> -- blow away the range constraint that you set up.
> exec sp_addpublication_snapshot
> @publication = N'WEB_TSK_MGMT',
> @frequency_type = 4,
> @frequency_interval = 1,
> @frequency_relative_interval = 0,
> @frequency_recurrence_factor = 1,
> @frequency_subday = 1,
> @frequency_subday_interval = 0,
> @active_start_date = 0,
> @active_end_date = 0,
> @active_start_time_of_day = 233000,
> @active_end_time_of_day = 0
> go
> -- Adding the transactional articles.
> sp_addarticle
> @publication = N'WEB_TSK_MGMT',
> @article = N'tbl_IssueTracker_IssueCategories',
> @source_owner = N'dbo',
> @source_object = N'tbl_IssueTracker_IssueCategories',
> @destination_table = N'tbl_IssueTracker_IssueCategories',
> @type = N'logbased',
> @creation_script = null,
> @description = null,
> @pre_creation_cmd = N'drop',
> @schema_option = 0x00000000000000F1,
> @status = 16,
> @vertical_partition = N'false',
> @filter = null,
> @sync_object = null
> go
>
> --Notice the password!
> exec sp_addsubscriber
> @subscriber = 'RNDSQL2',
> @login = 'sa',
> @password = ''
> go
>
> use web_tsk_mgmt
> go
> exec sp_addsubscription
> @publication = N'WEB_TSK_MGMT',
> @article = N'tbl_IssueTracker_IssueCategories',
> @subscriber = 'RNDSQL2',
> @destination_db = N'WEB_TSK_MGMT',
> @sync_type = N'none',
> @status = N'active',
> @update_mode = N'sync tran',
> @loopback_detection = 'true'
> go
>
> --This showed there were 0 records over there:
> select count(*) from dbo.tbl_IssueTracker_IssueCategories (nolock)
> --Build a DTS package that has Enable Identity Insert checked and
> -- Check Constraints unchecked.
> select * from dbo.tbl_IssueTracker_IssueCategories (nolock)
>
>
> --This was never used from this server:
> insert tbl_IssueTracker_IssueCategories
> ([name], parent_category_id)
> values
> ('name1', 20 )
>
>
> Thx for any consideration...
>
> "Michael Hotek" wrote:
>
>> Hilary, STOP, STOP, STOP!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! I might not
>> have
>> been posting out here for a while, but I'm certainly not stupid and I
>> definitely know that I'm not dreaming stuff up. Let me get rid of 100%
>> of
>> the extraneous stuff in this post for you.
>>
>> Here is the problem, which is pretty explicitly stated in the original
>> post.
>> "An explicit value for the identity column in table
>> 'tbl_IssueTracker_IssueCategories' can only be specified when a column
>> list
>> is used and IDENTITY_INSERT is ON."
>> and
>> "insert into "tbl_IssueTracker_IssueCategories" values (7, 'name1',
>> 20,{71A73DA7-1280-434C-9EB8-472487E724A4})
>> Transaction sequence number and command ID of last execution batch are
>> 0x000002B900000102000700000000 and 1."
>>
>> Now it might just be me, but last time I checked, this error message was
>> one
>> of the more explicit error messages that SQL Server has ever returned and
>> it
>> hasn't changed since I first saw it well over a decade ago in SQL Server
>> 6.5. This error has absolutely, precisely, NOTHING at all to do with
>> replication. There isn't a single, solitary thing that you could do with
>> the replication engine to fix this error. There is EXACTLY one fix to
>> this
>> error, ONE only, and no more than ONE. The insert statement absolutely
>> MUST
>> be the following:
>>
>> insert into "tbl_IssueTracker_IssueCategories"
>> (category_id, name, parent_category_id, ms_repl_tran_version)
>> values (7, 'name1', 20,{71A73DA7-1280-434C-9EB8-472487E724A4})
>>
>> If the code within the stored procedure is not changed to include a
>> column
>> list, as VERY EXPLICITLY stated by the error message, then there is ZERO
>> possibility of getting this to work and there is ZERO repro for this
>> since
>> it is a code problem and not a replication problem.
>>
>> Is THAT put clearly enough?
>>
>> --
>> Mike
>> Mentor
>> Solid Quality Learning
>> http://www.solidqualitylearning.com
>>
>>
>> "Hilary Cotter" <hilary.cotter@xxxxxxxxx> wrote in message
>> news:e$jbg1eEGHA.3820@xxxxxxxxxxxxxxxxxxxxxxx
>> > well, the correct identity_insert statement is set identity_insert
>> > tablename on, but I don't think this is the problem. I'm trying to
>> > repro
>> > it.
>> >
>> > --
>> > Hilary Cotter
>> > Looking for a SQL Server replication book?
>> > http://www.nwsu.com/0974973602.html
>> >
>> > Looking for a FAQ on Indexing Services/SQL FTS
>> > http://www.indexserverfaq.com
>> >
>> > "Michael Hotek" <mike@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> > news:%23RRaoycEGHA.1816@xxxxxxxxxxxxxxxxxxxxxxx
>> >> Ummmmm.........I know we just got done with a holiday and people miss
>> >> things from time to time, but this is a bit much. Your script has
>> >> absolutely nothing whatsoever to do with what is going on.
>> >>
>> >> You mean to tell me that the following script will actually execute?
>> >> (I
>> >> challenge you to get this to execute successfully.)
>> >>
>> >> create table test
>> >> (pk int identity(1,1), charcol char)
>> >> go
>> >> set identity_insert on
>> >> insert into test values(1,'a')
>> >> go
>> >>
>> >> tbl_IssueTracker_IssueCategories has an identity column in it. If you
>> >> are explicitly inserting into an identity column, then you MUST
>> >> specify a
>> >> column list. The insert statement in the post very explicitly does
>> >> not
>> >> have a column list for the insert statement which will cause the error
>> >> listed to be thrown 100% of the time it is executed. This has nothing
>> >> to
>> >> do with the "not for replication" setting. The code within the proc
>> >> which is issuing the insert statement is wrong.
>> >>
>> >> --
>> >> Mike
>> >> Mentor
>> >> Solid Quality Learning
>> >> http://www.solidqualitylearning.com
>> >>
>> >>
>> >> "Hilary Cotter" <hilary.cotter@xxxxxxxxx> wrote in message
>> >> news:egMdDpaEGHA.1288@xxxxxxxxxxxxxxxxxxxxxxx
>> >>> Ummm..I don't think so.
>> >>>
>> >>> create table test
>> >>> (pk int, charcol char)
>> >>> go
>> >>> insert into test values(1,'a')
>> >>> go
>> >>> (1 row(s) affected)
>> >>>
>> >>> --
>> >>> Hilary Cotter
>> >>> Looking for a SQL Server replication book?
>> >>> http://www.nwsu.com/0974973602.html
>> >>>
>> >>> Looking for a FAQ on Indexing Services/SQL FTS
>> >>> http://www.indexserverfaq.com
>> >>>
>> >>> "Michael Hotek" <mike@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >>> news:uNrzGhaEGHA.1816@xxxxxxxxxxxxxxxxxxxxxxx
>> >>>> Take a look at the insert statement.
>> >>>>
>> >>>> You are saying: "insert into table values(...)". You MUST do the
>> >>>> following: "insert into table (column list) values(...)"
>> >>>>
>> >>>> --
>> >>>> Mike
>> >>>> Mentor
>> >>>> Solid Quality Learning
>> >>>> http://www.solidqualitylearning.com
>> >>>>
>> >>>>
>> >>>> "CLM" <CLM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >>>> news:5172165E-88AE-455F-8051-CBBBEB8936DA@xxxxxxxxxxxxxxxx
>> >>>>>I implemented the above between 2000 two servers according to the
>> >>>>>following
>> >>>>> link :
>> >>>>> http://support.microsoft.com/default.aspx?scid=kb;en-us;820675
>> >>>>> I am just testing on one table and put in NOT FOR REPLICATION on
>> >>>>> both
>> >>>>> servers according to the following:
>> >>>>>
>> >>>>> CREATE TABLE [dbo].[tbl_IssueTracker_IssueCategories] (
>> >>>>> [category_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>> >>>>> [name] [varchar] (100) NOT NULL ,
>> >>>>> [parent_category_id] [int] NOT NULL ,
>> >>>>> [msrepl_tran_version] [uniqueidentifier] NOT NULL
>> >>>>> ) ON [PRIMARY]
>> >>>>> GO
>> >>>>>
>> >>>>> ALTER TABLE [dbo].[tbl_IssueTracker_IssueCategories] WITH NOCHECK
>> >>>>> ADD
>> >>>>> CONSTRAINT [PK_tbl_IssueTracker_IssueCategories] PRIMARY KEY
>> >>>>> CLUSTERED
>> >>>>> (
>> >>>>> [category_id]
>> >>>>> ) ON [PRIMARY]
>> >>>>> GO
>> >>>>>
>> >>>>> ALTER TABLE [dbo].[tbl_IssueTracker_IssueCategories] ADD
>> >>>>> CONSTRAINT [DF__tbl_Issue__msrep__6C39D5A3] DEFAULT (newid()) FOR
>> >>>>> [msrepl_tran_version]
>> >>>>> GO
>> >>>>>
>> >>>>> alter table tbl_IssueTracker_IssueCategories
>> >>>>> with nocheck add constraint category_id_range2 check NOT FOR
>> >>>>> REPLICATION
>> >>>>> ( category_id BETWEEN 10001 and 20000 )
>> >>>>> go
>> >>>>>
>> >>>>> The problem is that I get the following error when I insert a
>> >>>>> record
>> >>>>> into
>> >>>>> ServerA that is supposed to copy over to ServerB:
>> >>>>>
>> >>>>> "An explicit value for the identity column in table
>> >>>>> 'tbl_IssueTracker_IssueCategories' can only be specified when a
>> >>>>> column
>> >>>>> list
>> >>>>> is used and IDENTITY_INSERT is ON."
>> >>>>> and
>> >>>>> "insert into "tbl_IssueTracker_IssueCategories" values (7, 'name1',
>> >>>>> 20,
>> >>>>> {71A73DA7-1280-434C-9EB8-472487E724A4})
>> >>>>> Transaction sequence number and command ID of last execution batch
>> >>>>> are
>> >>>>> 0x000002B900000102000700000000 and 1."
>> >>>>>
>> >>>>> From what I understand, IDENTITY_INSERT is "on" if I put NOT FOR
>> >>>>> REPLICATION, so I don't understand what the problem is.
>> >>>>>
>> >>>>>
>> >>>>>
>> >>>>>
>> >>>>>
>> >>>>>
>> >>>>
>> >>>>
>> >>>
>> >>>
>> >>
>> >>
>> >
>> >
>>
>>
>>


.

Relevant Pages

  • Re: Merge versus Two Way, Bidirectional Transactional Replication
    ... ranges are blown and transactions are kicked back and lost. ... Looking for a SQL Server replication book? ... You can restore a publisher or subscriber without ...
    (microsoft.public.sqlserver.replication)
  • Re: Adding a nosync article to an existing transactional publication
    ... Queued updating is not scalable where a large ... portion of the DML originates at the Subscriber. ... Looking for a SQL Server replication book? ... online, accepting transactions. ...
    (microsoft.public.sqlserver.replication)
  • Re: MaxCmdsInTran in SQL 2005 SP2
    ... has made the log reader skip transactions. ... Looking for a SQL Server replication book? ... agent. ... No more new replicated transactions. ...
    (microsoft.public.sqlserver.replication)
  • Re: Enterprise Replication question
    ... Jarrod Teale wrote: ... transactions are still coming back VERY fast ... The reason is that when using timed based replication, the transaction is split into multiple transmission units and you simply can't support things such as referential integrity when doing that. ... A possible solution for you which would allow you to maintain the transactional unit - and to control the transmissions would be to use "cdr disconnect" and "cdr connect" periodically. ...
    (comp.databases.informix)
  • Re: Updating table looping through fields in the subform.
    ... You have a table I'll call Master, ... >fields OrderNumber, SKU, Quantity, ... >Transactions, with these fields: ... > Exit Sub ...
    (microsoft.public.access.formscoding)