insert doesn't work from subscriber in immediate updating in SQL2K

insert doesn't work from subscriber in immediate updating in SQL2K

Post by shaikh bas » Sat, 16 Feb 2002 01:23:34



Hi everyone
I have setup immediate update replication with two phase commit in SQL
2000.
I am unable to insert at subscribers end. I can insert, delete and
update records from Publishers side. I can also update and delete from
subscriber's side.
Insert at subscribers doesn't seems to work. I get the message

Server: Msg 2627, Level 14, State 1, Line 4
Violation of PRIMARY KEY constraint 'TBLxxx_PK'. Cannot insert
duplicate key in object 'TBLxxx'.
Server: Msg 7221, Level 18, State 2, Line 4
Could not relay results of procedure 'sp_MSsync_ins_TBLxxx_20' from
remote server 'SQLOLEDB'.
The statement has been terminated.
The statement has been terminated.

The identity columns at Publishers end are setup as yes (NOT for
Replication).
identity columns at subscribers end were automatically set to NO.

I'll really appreciate.
Thanks.

 
 
 

insert doesn't work from subscriber in immediate updating in SQL2K

Post by Ananth Shenoy [M » Sat, 23 Feb 2002 16:49:04


Set the subscriber identity to "not for replication" as well. What is most
likely happening is that the row you are trying to insert at the subscriber
side has a primary key which is already present, most probably due to the
changes made at the publisher.
Hope this helps,
Ananth

----------------------------------------------------------------------------
---
"This posting is provided AS IS with no warranties, and confers no
rights."
----------------------------------------------------------------------------
---
--------------------

| Newsgroups: microsoft.public.sqlserver.replication
| Subject: insert doesn't work from  subscriber in immediate updating in
SQL2K
| Date: 14 Feb 2002 08:23:34 -0800
| Organization: http://groups.google.com/
| Lines: 23

| NNTP-Posting-Host: 63.114.235.82
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1013703815 17496 127.0.0.1 (14 Feb 2002
16:23:35 GMT)

| NNTP-Posting-Date: 14 Feb 2002 16:23:35 GMT
| Path:
cpmsftngxa07!tkmsftngp01!newsfeed00.sul.t-online.de!t-online.de!newsfeed.gam
ma.ru!Gamma.RU!nntp.abs.net!feeder.qis.net!sn-xit-02!supernews.com!postnews1
google.com!not-for-mail
| Xref: cpmsftngxa07 microsoft.public.sqlserver.replication:26357
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| Hi everyone
| I have setup immediate update replication with two phase commit in SQL
| 2000.
| I am unable to insert at subscribers end. I can insert, delete and
| update records from Publishers side. I can also update and delete from
| subscriber's side.
| Insert at subscribers doesn't seems to work. I get the message
|
| Server: Msg 2627, Level 14, State 1, Line 4
| Violation of PRIMARY KEY constraint 'TBLxxx_PK'. Cannot insert
| duplicate key in object 'TBLxxx'.
| Server: Msg 7221, Level 18, State 2, Line 4
| Could not relay results of procedure 'sp_MSsync_ins_TBLxxx_20' from
| remote server 'SQLOLEDB'.
| The statement has been terminated.
| The statement has been terminated.
|
| The identity columns at Publishers end are setup as yes (NOT for
| Replication).
| identity columns at subscribers end were automatically set to NO.
|
| I'll really appreciate.
| Thanks.
|

 
 
 

1. Immediate updating subscriber can't update!

I've setup transactional replication my two servers, server1(publisher) &
server2(subscriber). If I logon to server2 using the sa username, I can
select and update the replicated data just fine. But if I try to logon using
a different name, I can select data from the replicated table, but I can't
perform updates, deletes, or inserts. I get the error:

Server: Msg 7415, Level 16, State 1, Line 4
Ad hoc access to OLE DB provider 'SQLOLEDB' has been denied. You must access
this provider through a linked server.

I checked all my login mappings and it looks like they're okay.
1. The username I'm using is part of the PAL list for the publication.
2. The username has update, insert, delete privileges.
3. The username exists on both the publisher and subscriber.
4. A remote server entry is setup for server1 & server2 (on both the
publisher and subscriber), with the RPC box checked.

If I try and setup a linked server like the error asks, it tells me that the
server already exists, even though I don't have any linked servers in the
item list. What else do I need to do? I don't want all my subscriber users
to use the sa logins to update tables.

2. ORACLE Opportunities

3. Insert/Delete/Udate Hangs on some subscribers (Immediate Update)

4. question on "old-style" join syntax

5. ASP/SQL Server Problem, INSERT Works, UPDATE Doesn't

6. unique index tradeoffs

7. Subscriber can't perform any immediate updates

8. Informix 7.11.UC1 and Tuxedo/T 6.1

9. Subscriber login failure using immediate-updating subscribers revised

10. Subscriber login failure using immediate-updating subscribers

11. SQL Mail doesn't work after ugrading to SQL2K

12. Scheduling DTS doesn't fail but doesn't work

13. ORACLE DOESN'T COMPLY TO BASIC STANDARD SQL: SIMPLE QUERIES DOESN'T WORK