Replication does not seem to work for rows inserted by a trigger.

Hi all,
Maybe I'm being a bit thick, but here goes.
I have merge replication set up between TableA on different databases
(say X and Y). X and Y are on different servers on different physical
machines.
If I run (in Query Analyser) an insert statement that inserts onto
X.TableA, all is well. The row appears on Y.TableA. Ditto for update
and delete.
If, instead, I place the exact same insert statement as the body of a
trigger on X.TableB and execute a SQL statement on X that causes the
trigger to fire - then the row(s) is(are) inserted onto X.TableA but
never arrive at Y.TableA.
This worked fine when both databases were on the same server.
What am I missing?
Kirsty
Do they show up in the conflict viewer? If not, can you post the schema for
the tables and triggers here?
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
<duff@.netcomuk.co.uk> wrote in message
news:1117029413.775980.20700@.g43g2000cwa.googlegro ups.com...
> Hi all,
> Maybe I'm being a bit thick, but here goes.
> I have merge replication set up between TableA on different databases
> (say X and Y). X and Y are on different servers on different physical
> machines.
> If I run (in Query Analyser) an insert statement that inserts onto
> X.TableA, all is well. The row appears on Y.TableA. Ditto for update
> and delete.
> If, instead, I place the exact same insert statement as the body of a
> trigger on X.TableB and execute a SQL statement on X that causes the
> trigger to fire - then the row(s) is(are) inserted onto X.TableA but
> never arrive at Y.TableA.
> This worked fine when both databases were on the same server.
> What am I missing?
> Kirsty
>
|||Hilary,
thanks for your response.
There are no conflicts.
SQL below (LONG: sorry, the tables do have a fair few fields).
I have also tried with a different subscriber on a different server (we
have different servers in different countries). Same problem.
Additionally, and MOST strangely, if I then UPDATE (in query analyser)
one of the rows sitting in the table on server X, the row then turns up
on server Y.
Kirsty
/************************************/
--'tableA'
CREATE TABLE [_ST_SageToTess] (
[Order_no] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Order_line_no] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CountryCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CountrySiteCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Cust_Num] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Cust_Name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cust_Address] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Cust_County] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Cust_Post_Code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Cust_Fax] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cust_Phone] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Cust_VAT_Num] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Cust_Cur_Code] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Cust_VAT_Code] [smallint] NULL ,
[Cust_Country] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Part_Num] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Part_Desc] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Prod_Num] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Prod_Desc] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site_Num] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Site_Name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site_Address] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Site_County] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Site_Post_Code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Site_Main_Contact] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Site_Email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Site_Fax] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site_Phone1] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Site_Cust_Num] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Site_Country] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Ser_Num] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Cont_Num] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Employ_Num] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
[DF___ST_SageT__rowgu__51628081] DEFAULT (newid()),
CONSTRAINT [PK_ST_SageToTess] PRIMARY KEY CLUSTERED
(
[Order_no],
[Order_line_no]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
--'TableB'
if not exists (select * from dbo.sysobjects where id =
object_id(N'[scheme].[opheadm]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
BEGIN
CREATE TABLE [opheadm] (
[order_no] [char] (10) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__order_n__65062755] DEFAULT (' '),
[alpha] [char] (8) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__alpha__65FA4B8E] DEFAULT (' '),
[customer] [char] (8) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__custome__66EE6FC7] DEFAULT (' '),
[address1] [char] (32) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__address__67E29400] DEFAULT (' '),
[address2] [char] (32) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__address__68D6B839] DEFAULT (' '),
[address3] [char] (32) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__address__69CADC72] DEFAULT (' '),
[address4] [char] (32) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__address__6ABF00AB] DEFAULT (' '),
[address5] [char] (32) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__address__6BB324E4] DEFAULT (' '),
[invoice_customer] [char] (8) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__invoice__6CA7491D] DEFAULT (' '),
[statement_customer] [char] (8) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__stateme__6D9B6D56] DEFAULT (' '),
[group_customer] [char] (8) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__group_c__6E8F918F] DEFAULT (' '),
[date_entered] [datetime] NULL CONSTRAINT
[DF__opheadm__date_en__6F83B5C8] DEFAULT
(convert(datetime,'31/12/1899',103)),
[date_received] [datetime] NULL CONSTRAINT
[DF__opheadm__date_re__7077DA01] DEFAULT
(convert(datetime,'31/12/1899',103)),
[date_required] [datetime] NULL CONSTRAINT
[DF__opheadm__date_re__716BFE3A] DEFAULT
(convert(datetime,'31/12/1899',103)),
[date_despatched] [datetime] NULL CONSTRAINT
[DF__opheadm__date_de__72602273] DEFAULT
(convert(datetime,'31/12/1899',103)),
[invoice_due_date] [datetime] NULL CONSTRAINT
[DF__opheadm__invoice__735446AC] DEFAULT
(convert(datetime,'31/12/1899',103)),
[customer_order_no] [char] (20) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__custome__74486AE5] DEFAULT (' '),
[territory] [char] (6) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__territo__753C8F1E] DEFAULT (' '),
[class] [char] (6) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__class__7630B357] DEFAULT (' '),
[region] [char] (6) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__region__7724D790] DEFAULT (' '),
[cust_disc_code] [char] (4) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__cust_di__7818FBC9] DEFAULT (' '),
[customer_vat_code] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__custome__790D2002] DEFAULT (' '),
[credit_category] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__credit___7A01443B] DEFAULT (' '),
[status] [char] (1) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__status__7AF56874] DEFAULT (' '),
[hold_indicator] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__hold_in__7BE98CAD] DEFAULT (' '),
[ack_sent_indicator] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__ack_sen__7CDDB0E6] DEFAULT (' '),
[invoice_date] [datetime] NULL CONSTRAINT
[DF__opheadm__invoice__7DD1D51F] DEFAULT
(convert(datetime,'31/12/1899',103)),
[invoice_no] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__invoice__7EC5F958] DEFAULT (' '),
[indicators1] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__indicat__7FBA1D91] DEFAULT (' '),
[indicators2] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__indicat__00AE41CA] DEFAULT (' '),
[price_list] [char] (3) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__price_l__01A26603] DEFAULT (' '),
[order_discount] [float] NOT NULL CONSTRAINT
[DF__opheadm__order_d__02968A3C] DEFAULT (0),
[credit_limit] [float] NOT NULL CONSTRAINT
[DF__opheadm__credit___038AAE75] DEFAULT (0),
[settlement_discoun] [float] NOT NULL CONSTRAINT
[DF__opheadm__settlem__047ED2AE] DEFAULT (0),
[hdcomm_unused] [float] NOT NULL CONSTRAINT
[DF__opheadm__hdcomm___0572F6E7] DEFAULT (0),
[spare1] [float] NOT NULL CONSTRAINT [DF__opheadm__spare1__06671B20]
DEFAULT (0),
[spare2] [float] NOT NULL CONSTRAINT [DF__opheadm__spare2__075B3F59]
DEFAULT (0),
[settlement_days] [int] NOT NULL CONSTRAINT
[DF__opheadm__settlem__084F6392] DEFAULT (0),
[labels] [char] (2) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__labels__094387CB] DEFAULT (' '),
[warehouse] [char] (2) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__warehou__0A37AC04] DEFAULT (' '),
[wo_order_flag] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__wo_orde__0B2BD03D] DEFAULT (' '),
[pick_list] [char] (6) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__pick_li__0C1FF476] DEFAULT (' '),
[vehicle_reference] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__vehicle__0D1418AF] DEFAULT (' '),
[spare_field] [char] (18) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__spare_f__0E083CE8] DEFAULT (' '),
[consolidation_flag] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__consoli__0EFC6121] DEFAULT (' '),
[transaction_anals1] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__transac__0FF0855A] DEFAULT (' '),
[transaction_anals2] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__transac__10E4A993] DEFAULT (' '),
[transaction_anals3] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__transac__11D8CDCC] DEFAULT (' '),
[back_to_back_ind] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__back_to__12CCF205] DEFAULT ('N'),
[vat_method] [char] (1) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__vat_met__13C1163E] DEFAULT (' '),
[po_allocated_flag] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__po_allo__14B53A77] DEFAULT ('N'),
[quote_proforma] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__quote_p__15A95EB0] DEFAULT (' '),
[pricing_date] [datetime] NULL CONSTRAINT
[DF__opheadm__pricing__169D82E9] DEFAULT
(convert(datetime,'31/12/1899',103)),
[consol_invoice_no] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__consol___1791A722] DEFAULT (' '),
[comment_only_flag] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__comment__1885CB5B] DEFAULT ('N'),
[direct_debit_inv] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__direct___1979EF94] DEFAULT (' '),
[invoice_required] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__invoice__1A6E13CD] DEFAULT ('Y'),
[vat_type] [char] (10) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__vat_typ__1B623806] DEFAULT (' '),
[lang] [char] (10) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__lang__1C565C3F] DEFAULT (' '),
[delivery_method] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__deliver__1D4A8078] DEFAULT (' '),
[carrier_code] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__carrier__1E3EA4B1] DEFAULT (' '),
[payment_method] [char] (2) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__payment__1F32C8EA] DEFAULT (' '),
[customer_class] [char] (8) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__custome__2026ED23] DEFAULT (' '),
[sales_type] [char] (2) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__sales_t__211B115C] DEFAULT (' '),
[lower_value] [float] NOT NULL CONSTRAINT
[DF__opheadm__lower_v__220F3595] DEFAULT (0),
[payment_date_1] [char] (2) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__payment__230359CE] DEFAULT (' '),
[payment_date_2] [char] (2) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__payment__23F77E07] DEFAULT (' '),
[payment_date_3] [char] (2) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__payment__24EBA240] DEFAULT (' '),
[court_charges] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__court_c__25DFC679] DEFAULT (' '),
[shipno] [char] (10) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__shipno__26D3EAB2] DEFAULT (' '),
[date_promised] [datetime] NULL CONSTRAINT
[DF__opheadm__date_pr__27C80EEB] DEFAULT
(convert(datetime,'31/12/1899',103)),
[from_indicator] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__from_in__28BC3324] DEFAULT (' '),
[from_reference] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__from_re__29B0575D] DEFAULT (' '),
[sl_batch] [char] (6) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__sl_batc__2AA47B96] DEFAULT (' '),
[sl_period] [char] (2) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__sl_peri__2B989FCF] DEFAULT (' '),
[sl_year] [char] (2) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__sl_year__2C8CC408] DEFAULT (' '),
[payer] [char] (10) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__payer__2D80E841] DEFAULT (' '),
[responsibility] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__respons__2E750C7A] DEFAULT (' '),
[schedule_number] [char] (15) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__schedul__2F6930B3] DEFAULT (' '),
[to_reference] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__to_refe__305D54EC] DEFAULT (' '),
[payment_terms_desc] [char] (20) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__payment__31517925] DEFAULT (' '),
[shipping_note_ind] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__shippin__32459D5E] DEFAULT ('Y'),
[delivery_reason] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__deliver__3339C197] DEFAULT (' '),
[shipper_code1] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__shipper__342DE5D0] DEFAULT (' '),
[shipper_code2] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__shipper__35220A09] DEFAULT (' '),
[shipper_code3] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__shipper__36162E42] DEFAULT (' '),
[shipping_text] [char] (60) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__shippin__370A527B] DEFAULT (' '),
[appearance] [char] (60) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__appeara__37FE76B4] DEFAULT (' '),
[total_packages] [int] NOT NULL CONSTRAINT
[DF__opheadm__total_p__38F29AED] DEFAULT (0),
[total_ship_weight] [float] NOT NULL CONSTRAINT
[DF__opheadm__total_s__39E6BF26] DEFAULT (0),
[shipping_site] [char] (2) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__shippin__3ADAE35F] DEFAULT (' '),
[sett_category] [char] (2) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__sett_ca__3BCF0798] DEFAULT (' '),
[settlement_days2] [int] NOT NULL CONSTRAINT
[DF__opheadm__settlem__3CC32BD1] DEFAULT (0),
[settlement_days3] [int] NOT NULL CONSTRAINT
[DF__opheadm__settlem__3DB7500A] DEFAULT (0),
[settlement_days4] [int] NOT NULL CONSTRAINT
[DF__opheadm__settlem__3EAB7443] DEFAULT (0),
[settlement_disc2] [float] NOT NULL CONSTRAINT
[DF__opheadm__settlem__3F9F987C] DEFAULT (0),
[settlement_disc3] [float] NOT NULL CONSTRAINT
[DF__opheadm__settlem__4093BCB5] DEFAULT (0),
[settlement_disc4] [float] NOT NULL CONSTRAINT
[DF__opheadm__settlem__4187E0EE] DEFAULT (0),
[short_name] [char] (3) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__short_n__427C0527] DEFAULT (' '),
[shipping_dept] [char] (2) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__shippin__43702960] DEFAULT (' '),
[effective_date] [datetime] NULL CONSTRAINT
[DF__opheadm__effecti__44644D99] DEFAULT
(convert(datetime,'31/12/1899',103)),
[period] [char] (2) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__period__455871D2] DEFAULT (' '),
[slyear] [char] (4) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__slyear__464C960B] DEFAULT (' ')
) ON [PRIMARY]
END
GO
--trigger (for testing replication)
CREATE TRIGGER scheme.ST_upd_opheadm ON [scheme].[opheadm]
FOR UPDATE
AS
BEGIN
insert into _ST_SageToTess (Order_no,
Order_line_no,CountryCode,CountrySiteCode, Cust_Num, Part_Num,
Prod_Num,
Site_Num, Ser_Num, Cont_Num, Employ_Num)
values
(1,1,'UK','XX','ABC','12345','12345A','gggg','kkk' ,'jkjkgjg','999')
end
GO
|||Sorry, the opheadm table is actually scheme.opheadm. As you might
guess, this one's not mine, but part of our Sage database.
Kirsty