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 and the identity column

I have been dealing with transactional replication a little bit and I am a
bit confused with the identity column and its affect on replication.
It seems like SS doesn't want to replicate the identity the subscriber. The
Dialogue box directed me to set the property to "not for replication", when I
did that the identify is blank in the subscriber db.
If I want to use the subscriber DB as a DR database, and the app needs the
identity column to be the same, isn't there a way around this limitation.
can someone point me to a resource that explains this concept?
thanks
Hi,
I understand that you would like to replicate the identity column to your
subscriber, however the process failed. You would like to know why.
If I have misunderstood, please let me know.
This is a known by design limitation. Please refer to this article:
Managing Identity Values
http://msdn2.microsoft.com/en-us/library/aa237098(SQL.80).aspx
You may perform a test to check if the third method using other columns as
primary keys helps. If it could not fit your requirement, I am afraid that
you may consider to use an application to implement the replication by
yourself.
Please feel free to let me know if you have any other questions or
concerns.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||The simplest solution is to set up the subscriber as a queued updating
subscriber, and allocate a large range for the identity ranges. This will
ensure the identity column is retained, as well as retaining the PKs.
HTH,
Paul Ibison
|||I normally use bi-directional transactional replication with odd values on
one side and even on the other. IE an odd seed on the publisher , and an
even on the subscriber. Set the increment to 2.
Put the schema in place on the subscriber and put the data there. Then do a
nosync and ensure that the identities are the correct values.
Use DBCC checkident to verify that the next assigned value on the Publisher
is odd and even on the subscriber.
While you can use queued updating for this, queued is designed for
situations where the majority of your dml occurs on your publisher not your
subscriber and it uses triggers to track changes occurring on the
subscriber. This will add latency to every DML occurring on the subscriber.
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
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:9445707D-EB52-4214-81E4-1694BFCEC4AE@.microsoft.com...
>I have been dealing with transactional replication a little bit and I am a
> bit confused with the identity column and its affect on replication.
> It seems like SS doesn't want to replicate the identity the subscriber.
> The
> Dialogue box directed me to set the property to "not for replication",
> when I
> did that the identify is blank in the subscriber db.
> If I want to use the subscriber DB as a DR database, and the app needs the
> identity column to be the same, isn't there a way around this limitation.
> can someone point me to a resource that explains this concept?
>
> thanks
|||thanks, so this is someting that would be set up when creating the subsciber?
i don't see this option anywhere on my test replication scenario i have
running.
"Paul Ibison" wrote:

> The simplest solution is to set up the subscriber as a queued updating
> subscriber, and allocate a large range for the identity ranges. This will
> ensure the identity column is retained, as well as retaining the PKs.
> HTH,
> Paul Ibison
>
|||thanks for the reply. in this scenario would the identity values be the same
on the subscriber side then? doesn't read that way.
"Hilary Cotter" wrote:

> I normally use bi-directional transactional replication with odd values on
> one side and even on the other. IE an odd seed on the publisher , and an
> even on the subscriber. Set the increment to 2.
> Put the schema in place on the subscriber and put the data there. Then do a
> nosync and ensure that the identities are the correct values.
> Use DBCC checkident to verify that the next assigned value on the Publisher
> is odd and even on the subscriber.
> While you can use queued updating for this, queued is designed for
> situations where the majority of your dml occurs on your publisher not your
> subscriber and it uses triggers to track changes occurring on the
> subscriber. This will add latency to every DML occurring on the subscriber.
> --
> 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
> "Jason" <Jason@.discussions.microsoft.com> wrote in message
> news:9445707D-EB52-4214-81E4-1694BFCEC4AE@.microsoft.com...
>
>
|||They would be the same. Rows entered on the publisher will be odd, rows
entered on the subscriber will be even.
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
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:DB5F2603-41BF-4C98-B1C9-D69C2BFCC184@.microsoft.com...[vbcol=seagreen]
> thanks for the reply. in this scenario would the identity values be the
> same
> on the subscriber side then? doesn't read that way.
> "Hilary Cotter" wrote:
|||Hi Jason,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Replicating User Defined Data Types

Hey guys,
Thanks so much for this great newsgroup btw, helped me hugely through doing
replication via handheld units etc.
I'm running into a bit of a problem between server 2 server replication.
Both databases have user defined data types. All the tables using them are
fine, but once the snapshot is applied to the subscriber, and the replication
is hooked up to the publisher, all the data types are converted to their base
types (char 10 etc)
Is there any way to stop this? The subscriber needs to keep the user defined
data types.
I noticed in the publication wizard there is an option that says "Keep the
existing table unchanged" but the default value for this is "DROP the
existing table and re-create it" this is all under the Snapshot tab of the
article properties.
If I set it to "Keep the existing table unchanged" will that fix my problems
or is that not the proper fix for this issue?
Thanks and take care!
Simon,
on the article propertuies at the bottom there is the option to change user
defined datatypes to base types and by default this is enabled. Also, you
can add the user defined types themselves to a prescript.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)