Replication does not work properly

Hi,
I have a problem with rows deleted at subscriber could not get deleted at
publisher in particular case where article published say table1 has row
filter like
"SELECT <published_columns> FROM [dbo].[TABLE1]
WHERE ([FILED1]='XYZ ' OR [FIELD4]= 'XYZ ') and FIELD3 in(select
[FIELD3] from [TABLE2] A where A.[FIELD1]=[FIELD1] and A.[FIELD2]=[FIELD2] )"
Rest of the Articles published with simpler row filter works perfectly.
Please Reply,
Thanking You
fatiya
are both Table1 and Table2 published? are there any constraints between teh
two tables? Are you enforcing these constraints for replication? Cascading
Updates/Deletes?
Have you used the conflict viewer to see if there are any conflicts?
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
"Fatiya" <Fatiya@.discussions.microsoft.com> wrote in message
news:6A20CE4B-7D05-4202-BCEE-79AC26E28326@.microsoft.com...
> Hi,
> I have a problem with rows deleted at subscriber could not get deleted
at
> publisher in particular case where article published say table1 has row
> filter like
> "SELECT <published_columns> FROM [dbo].[TABLE1]
> WHERE ([FILED1]='XYZ ' OR [FIELD4]= 'XYZ ') and FIELD3 in(select
> [FIELD3] from [TABLE2] A where A.[FIELD1]=[FIELD1] and
A.[FIELD2]=[FIELD2] )"
> Rest of the Articles published with simpler row filter works perfectly.
> Please Reply,
> Thanking You
> fatiya
>
>
|||Dear Hilary
Thanks for reply
Yes both table 1 and table2 are published No there are no contrainst between
them
we have not used any cascading
We are getting replicaion conflicts that the row was inserted at subscriber
but
at publisher we are getting primary key constrains.
Please advice
Fatiya
"Hilary Cotter" wrote:

> are both Table1 and Table2 published? are there any constraints between teh
> two tables? Are you enforcing these constraints for replication? Cascading
> Updates/Deletes?
> Have you used the conflict viewer to see if there are any conflicts?
> --
> 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
> "Fatiya" <Fatiya@.discussions.microsoft.com> wrote in message
> news:6A20CE4B-7D05-4202-BCEE-79AC26E28326@.microsoft.com...
> at
> A.[FIELD2]=[FIELD2] )"
>
>
sql

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

Replication can solve may problem ?

Hi,
In my db I have a big table with at least 40 millions rows.
In fact my appz soffer about performance and I had to put
offline the oldest data.
I keep online only the last 6 monthsm and I copy the oldest in
some tables with data costraints...
I mean I have a table called "TbPrices" and then I have
TbPrices_2003_12,
TbPrices_2003_11
and so on.
I think to use a replication db to have the entire archive with a clustered
view and
the OLTP with the last 10 days online.
But I don't know how replication works because I don't want to delete the
record in
the destination.
Thanks
You can use replication for this but you have to be careful.
I am confused by what you mean by a clustered view? Do you mean an indexed
view, or perhaps a distributed partitioned view?
Replication can be used to replicate a subset of data to an archive table,
ie only replicate data older than 6 months perhaps.
You should use a date filter to only replicate data which is less than 6
months old. Then when you are creating your publication ensure that you use
the 'delete all data which matches the row filter clause' you can find in
the specify articles dialog of the create publication wizard. Click the
three ellipses to the right of your article name, and select the snapshot
tab. In the name conflicts section select this option.
This will ensure when you reinitialize the archived data on your subscriber
is not wiped out.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"opianeta" <opianetaNONMISPAMMAREPLEASE@.yahoo.it> wrote in message
news:7R0Xc.458$v24.9883@.news.it.colt.net...
> Hi,
> In my db I have a big table with at least 40 millions rows.
> In fact my appz soffer about performance and I had to put
> offline the oldest data.
> I keep online only the last 6 monthsm and I copy the oldest in
> some tables with data costraints...
> I mean I have a table called "TbPrices" and then I have
> TbPrices_2003_12,
> TbPrices_2003_11
> and so on.
> I think to use a replication db to have the entire archive with a
clustered
> view and
> the OLTP with the last 10 days online.
> But I don't know how replication works because I don't want to delete the
> record in
> the destination.
> Thanks
>

replication cache plan

Hi,
I heard that simple delete stmt which deletes no of rows everytime in
replication is not using came plan caching why you have any idea or is there
any option that we can turn on to use cache plan..
Thanks in advance
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200706/1
No, it will use the plan that either the ad hoq sql statements use or the
stored procedure plan.
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
"pardhiveswar via droptable.com" <u8956@.uwe> wrote in message
news:73eafdf38c451@.uwe...
> Hi,
> I heard that simple delete stmt which deletes no of rows everytime in
> replication is not using came plan caching why you have any idea or is
> there
> any option that we can turn on to use cache plan..
>
> Thanks in advance
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200706/1
>
|||Hillary,
ok if Autoparameterization is not using for simple delete statement in 2000
what we have to do..
is there an option to force sql server...
Thanks in advance
Hilary Cotter wrote:[vbcol=seagreen]
>No, it will use the plan that either the ad hoq sql statements use or the
>stored procedure plan.
>[quoted text clipped - 4 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200706/1
|||Hillary,
Just ignore the above stsmt.
Actually my questions is during replication from sql to sql it will create
stored prodecudres
but from sql to oracle iinstead of stored procedure it will only create sql
stmt so during this time it is not using autoparameterisation for simple
delete stmt(adhoc stmt),is there a way that we can use autoparam...
please let me know if you didn't understand
sorry for the confusion before..
Thanks, in advance
pardhiveswar wrote:[vbcol=seagreen]
>Hillary,
>ok if Autoparameterization is not using for simple delete statement in 2000
>what we have to do..
>is there an option to force sql server...
>Thanks in advance
>[quoted text clipped - 3 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200706/1

Replication and Deleting Rows

Hello,
I have a large table with over 20 million rows.
It is replicated accross a 4 subscribers using Pull subscription. One of the
subscribers is also a Distributor.
We are running some processed to "clean up" the data in this table by
issuing a lot of DELETE commands (using Primary Key as the where clause)
against the large table. The DELETE commands are enclosed in BEGIN TRAN -
COMIT TRAN batches of 20 each.
The deletion works extremely fast on the Publisher.
However, it takes very very long time to apply the very same commands on all
of the subscribers. The subscribers also have the PRIMARY KEY index that is
on the Publisher.
We need to delete a few million rows from the large table using this
technique. However, it makes the Subscribers very slow and unresponsive.
Any suggestions?
Thanks,
ArsenConsider doing this in even smaller batches. If that is not possible, and
transactional consistency is not really of importance, then you could use a
parameter called MaxCmdsInTran for logreader (introduced in SQL Server 2000
SP1 I believe). This breaks huge transactions into smaller chunks of
specified size, while storing the commands in distribution database.
There is another approach, that I would recommend. Consider using 'stored
procedure execution' replication. With this method, you keep a copy of the
archiving stored procedure on all subscribers. When you run this procedure
on the publisher, the distribution agent will call this sp on all
subscribers, instead of sending millions of DELETEs over the network. See
SQL Server Books Online for more information on this.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Arsen V." <arsen@.community.nospam> wrote in message
news:%23hpi4tKoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a large table with over 20 million rows.
> It is replicated accross a 4 subscribers using Pull subscription. One of
the
> subscribers is also a Distributor.
> We are running some processed to "clean up" the data in this table by
> issuing a lot of DELETE commands (using Primary Key as the where clause)
> against the large table. The DELETE commands are enclosed in BEGIN TRAN -
> COMIT TRAN batches of 20 each.
> The deletion works extremely fast on the Publisher.
> However, it takes very very long time to apply the very same commands on
all
> of the subscribers. The subscribers also have the PRIMARY KEY index that
is
> on the Publisher.
> We need to delete a few million rows from the large table using this
> technique. However, it makes the Subscribers very slow and unresponsive.
> Any suggestions?
> Thanks,
> Arsen
>

Replication and Deleting Rows

Hello,
I have a large table with over 20 million rows.
It is replicated accross a 4 subscribers using Pull subscription. One of the
subscribers is also a Distributor.
We are running some processed to "clean up" the data in this table by
issuing a lot of DELETE commands (using Primary Key as the where clause)
against the large table. The DELETE commands are enclosed in BEGIN TRAN -
COMIT TRAN batches of 20 each.
The deletion works extremely fast on the Publisher.
However, it takes very very long time to apply the very same commands on all
of the subscribers. The subscribers also have the PRIMARY KEY index that is
on the Publisher.
We need to delete a few million rows from the large table using this
technique. However, it makes the Subscribers very slow and unresponsive.
Any suggestions?
Thanks,
Arsen
Consider doing this in even smaller batches. If that is not possible, and
transactional consistency is not really of importance, then you could use a
parameter called MaxCmdsInTran for logreader (introduced in SQL Server 2000
SP1 I believe). This breaks huge transactions into smaller chunks of
specified size, while storing the commands in distribution database.
There is another approach, that I would recommend. Consider using 'stored
procedure execution' replication. With this method, you keep a copy of the
archiving stored procedure on all subscribers. When you run this procedure
on the publisher, the distribution agent will call this sp on all
subscribers, instead of sending millions of DELETEs over the network. See
SQL Server Books Online for more information on this.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Arsen V." <arsen@.community.nospam> wrote in message
news:%23hpi4tKoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a large table with over 20 million rows.
> It is replicated accross a 4 subscribers using Pull subscription. One of
the
> subscribers is also a Distributor.
> We are running some processed to "clean up" the data in this table by
> issuing a lot of DELETE commands (using Primary Key as the where clause)
> against the large table. The DELETE commands are enclosed in BEGIN TRAN -
> COMIT TRAN batches of 20 each.
> The deletion works extremely fast on the Publisher.
> However, it takes very very long time to apply the very same commands on
all
> of the subscribers. The subscribers also have the PRIMARY KEY index that
is
> on the Publisher.
> We need to delete a few million rows from the large table using this
> technique. However, it makes the Subscribers very slow and unresponsive.
> Any suggestions?
> Thanks,
> Arsen
>