Re: Two Way Bidirectional Rep

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