Re: Two Way Bidirectional Rep

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: 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. ... > Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: sp_replcmds
    ... Hilary Cotter ... Looking for a book on SQL Server replication? ... "Andre" wrote in message ... > execute 'sp_replcmds' on sql01...". ...
    (microsoft.public.sqlserver.replication)
  • Re: Replication with identity key failing
    ... You have to comment out the update of the identity column portions of your ... By definition identity properties cannot be updated. ... Hilary Cotter ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: Identity columns
    ... Will this sql affect any current or future Identity column values? ... "Hilary Cotter" wrote: ... > Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: Identity columns problem in replication among n no of subscribers
    ... If I am having file called EMP with identity column Recid. ... I want to give automatic range control on publisher for n no. subscribers ... Director of Text Mining and Database Strategy ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)