Re: Two Way Bidirectional Rep

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: Two Way Bidirectional Rep
    ... > You mean to tell me that the following script will actually execute? ... > tbl_IssueTracker_IssueCategories has an identity column in it. ... >> Hilary Cotter ... >> Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: Table owner in dynamic filtered merge replication
    ... parts of every script step by step. ... restores initial state of stored procedure (which is changed after ... new servers I am installing (where merge replication is used) and every ...
    (microsoft.public.sqlserver.replication)
  • Re: Error 14113 while trying to setup replication on SQL Server 20
    ... "Hilary Cotter" wrote: ... Looking for a SQL Server replication book? ... Error 14113: Could not execute c:\... ...
    (microsoft.public.sqlserver.replication)
  • Re: Error 14113 while trying to setup replication on SQL Server 20
    ... Can you reapply SP3, or better yet SP4 and try again? ... Looking for a SQL Server replication book? ... Error 14113: Could not execute c:\... ...
    (microsoft.public.sqlserver.replication)
  • 2005 Log Reader
    ... We are having a problem with a recently upgraded 2005 replication setup. ... just upgrading the databases. ... execute 'sp_replcmds' on 'PASASERV\PASLIVE'.'. ... See the SQL Server errorlog for more ...
    (microsoft.public.sqlserver.replication)