I have the same issue, but with a SQL Server 2000 Publisher an
Distributor (Same machine is the Publisher and Distributor) and a SQ
Server 2005 Subscriber.
Its a Transactional Replication.
The problem occurs when I try to replicate tables with Identity column
and set the "Action if name is in use" property of the "Aricle Property
dialogbox to NOT drop the existing object. The actual option that
choose is "Truncate all data in the existing object"
The tables on both the Publisher and Subscriber have the identit
column set with "NOT FOR REPLICATION"
The strange thing that I've found is that only the insert store
procedure is created on the Subscriber. I.e. the stored procedure wit
the name sp_MSins_dbonfr, where dbo is the owner name and "nfr" is th
table name.
There is no stored procedure named sp_MSdel_dbonfr created nor is ther
any stored procedure named sp_MSupd_dbonfr created.
In fact, if I manually created these procedures on the Subscriber (b
geting their script by calling sp_scriptPublicationCustomProcs), and
re-start the synchronization process, the synchronization process fail
again AND also DELETES the sp_MSupd_dbonfr procedure that I had manuall
created!!!! Yes, deletes it and does not re-create it!!!!!!!!
Is this a bug???
However, if I re-create the deleted sp_MSupd_dbonfr stored procedure o
the subscriber once again, and then change "Copy INSERT, UPDATE, an
DELETE stored procedures" property on the "Article Properties" dialo
box from "true" to "false" and save the chages, SQL Server tells me t
re-run the snapshot. After the snapshot is re-run and th
synchronization process stated once again, the replication works, an
continues to work.
Also note that changing the "Copy INSERT, UPDATE, and DELETE store
procedures" property on the "Article Properties" dialog box from "true
to "false" before having generated the replication procedures (thos
sp_MS* procedures), causes the sp_scriptPublicationCustomProcs not t
script out any of the replication stored procedures. This however,
believe is correct behavior.
Things work fine if I replicate tables that don't have an identit
column or I choose not to have the identity column property replicate
to the subscriber tables.
I could be wrong but based on the above experience, I believe ther
seems to be a bug in either SQL 2000 or SQL 2005.
Here's the info requested by Hilary:
The insert, update, and delete commands are respectively:
CALL sp_MSins_dbonfr
MCALL sp_MSupd_dbonfr
CALL sp_MSdel_dbonfr
schema option value: 0x00000000000080A1
No, the subscription was not a "no-sync" subscription
Does anyone have a solution to this?
Thanks in advance
--
hi
-----------------------------------------------------------------------
his's Profile:
http://www.dbtalk.net/m65 View this thread:
http://www.dbtalk.net/t32376
