Hi,
>From a publisher I replicate 3 databases to a distributor/subscriber
machine. On all of them i have MS SQL 2005.
When i delete one of the replications by running
publisher => sp_dropsubscription
subscriber/distributor=>sp_removedbreplication
publisher => sp_removedbreplication
subscriber/distributor =>sp_subscription_cleanup
On the publisher and subscriptions databases all replication things
are removed.
However when looking with the replication monitor the deleted
replication is visible with a big red cross. Also the jobs are still
in the job list of the distributor!
When removing everything by dropping the distribution database all is
cleared. I know that in SQL 2000 the distributor would also be
cleared. Has this changed?
Is this a bug in SQL2005 or am i forgotting something (i have searched
a lot already, but cannot find anything)?
Hopefully someone can help me?
Marcel
I'd recommend using sp_droppublication which'll clean up the jobs as well.
sp_removedbreplication is something I run only occasionally to clean up any
remaining orphaned objects.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||On 1 feb, 10:28, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I'd recommend using sp_droppublication which'll clean up the jobs as well.
> sp_removedbreplication is something I run only occasionally to clean up any
> remaining orphaned objects.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com.
Thanks Paul,
This looks much better! I finalized with
EXEC sp_replicationdboption
@.dbname = @.publicationDB,
@.optname = N'publish',
@.value = N'false';
This should remove the publication objects...
Marcel
Showing posts with label replicate. Show all posts
Showing posts with label replicate. Show all posts
Thursday, March 29, 2012
Sunday, March 11, 2012
Deletes through Replication
I have an order header we'll call HEADER and detail we'll call DETAIL
(catchy names !!)
When I replicate new orders, I use filters:
The HEADER article filter is like:
Select * from HEADER
where ordr_dte >= '1998-01-01'
then
The DETAIL filter is like:
Select * from DETAIL
where exists (SELECT * FROM HEADER WHERE DETAIL.ord_num = HEADER.ord_num
and (HEADER.ordr_dte >= '1998-01-01' ))
This makes sure I only get DETAILS that match filtered HEADERS.
Everything's fine so far. Subscriber matches Publisher
Now, our order processing application sometimes deletes order records. Not
very often, but if a transaction is backed out before being posted or
something.
Anyway, the problem is that on the Publisher, the application deletes the
HEADER record, which gets deleted on the Subscriber .... FINE
Then the DETAIL gets deleted on the Publisher .... BUT ... since my
filter only deals with DETAILS that have an associated HEADER, and the HEADER
doesn't exist any more, I'm left with an orphan DETAIL record on the
subscriber.
What should I do ? Is there a better way to set this up to start with ?
if you are using merge replication have a look at join filters.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HomeBrew" <HomeBrew@.discussions.microsoft.com> wrote in message
news:29A8C1EA-42EC-40DE-873F-5F01C2FEBAFE@.microsoft.com...
> I have an order header we'll call HEADER and detail we'll call DETAIL
> (catchy names !!)
> When I replicate new orders, I use filters:
> The HEADER article filter is like:
> Select * from HEADER
> where ordr_dte >= '1998-01-01'
> then
> The DETAIL filter is like:
> Select * from DETAIL
> where exists (SELECT * FROM HEADER WHERE DETAIL.ord_num =
HEADER.ord_num
> and (HEADER.ordr_dte >= '1998-01-01' ))
> This makes sure I only get DETAILS that match filtered HEADERS.
> Everything's fine so far. Subscriber matches Publisher
> Now, our order processing application sometimes deletes order records. Not
> very often, but if a transaction is backed out before being posted or
> something.
> Anyway, the problem is that on the Publisher, the application deletes the
> HEADER record, which gets deleted on the Subscriber .... FINE
> Then the DETAIL gets deleted on the Publisher .... BUT ... since my
> filter only deals with DETAILS that have an associated HEADER, and the
HEADER
> doesn't exist any more, I'm left with an orphan DETAIL record on the
> subscriber.
> What should I do ? Is there a better way to set this up to start with ?
|||I'm just using regular Transactional Replication.
Is there some better filter logic I can use to avoid orphans ?
"Hilary Cotter" wrote:
> if you are using merge replication have a look at join filters.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "HomeBrew" <HomeBrew@.discussions.microsoft.com> wrote in message
> news:29A8C1EA-42EC-40DE-873F-5F01C2FEBAFE@.microsoft.com...
> HEADER.ord_num
> HEADER
>
>
|||Yes, but could you post your schema for both tables?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HomeBrew" <HomeBrew@.discussions.microsoft.com> wrote in message
news:582163AF-EEC9-4633-B723-70D2B72C9E1A@.microsoft.com...[vbcol=seagreen]
> I'm just using regular Transactional Replication.
> Is there some better filter logic I can use to avoid orphans ?
> "Hilary Cotter" wrote:
Not[vbcol=seagreen]
the[vbcol=seagreen]
my[vbcol=seagreen]
with ?[vbcol=seagreen]
|||Here are the schemas for the "HEADER" and "DETAIL" tables. (1 Header to
Many Details)
Below them are the snippets from my Replication Row Filters. These tables
are part of a package, so I can't make the Detail get deleted before the
Header.
Schema for PROORD_M ("HEADER" Table, I removed many irrelevant fields in
the interest of space)
CREATE TABLE [dbo].[PROORD_M] (
[ORD_NUM] [char] (8) NOT NULL ,
[CTG_DTE] [datetime] NOT NULL ,
[ORD_CTG] [char] (6) NOT NULL ,
[NUM_OPS] [char] (8) NOT NULL ,
[CTM_NBR] [char] (12) NOT NULL ,
[CTG_DTE2] [decimal](8, 0) NOT NULL ,
[ORD_CTG2] [char] (6) NOT NULL ,
[ORD_STA] [char] (1) NOT NULL ,
[ORD_NUM2] [char] (8) NOT NULL ,
[PO_NUM] [char] (20) NULL ,
[ORDR_DTE] [datetime] NULL ,
[SHP_DTE] [datetime] NULL ,
[ORD_TYPE] [char] (1) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PROORD_M] ADD
CONSTRAINT [PROORD_M_K1] PRIMARY KEY CLUSTERED
([ORD_NUM]) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [PROORD_M_K2] UNIQUE NONCLUSTERED
([CTG_DTE],
[ORD_CTG],
[NUM_OPS]) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [PROORD_M_K4] UNIQUE NONCLUSTERED
([ORD_STA],
[ORD_NUM2]) WITH FILLFACTOR = 90 ON [PRIMARY]
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++
Schema for PROOLN_M ("DETAIL" Table, I removed many irrelevant fields in
the interest of space)
CREATE TABLE [dbo].[PROOLN_M] (
[ORD_NUM] [char] (8) NOT NULL ,
[ORD_SPSQ] [char] (6) NOT NULL ,
[ORD_SEQ] [char] (5) NOT NULL ,
[SHP_CTM] [char] (12) NOT NULL ,
[ITM_NUM2] [char] (10) NOT NULL ,
[ITM_NUM3] [char] (10) NOT NULL ,
[ORD_NUM3] [char] (8) NOT NULL ,
[OLN_STA] [char] (2) NULL ,
[ITM_NUM] [char] (10) NULL
[QTY_ORD] [int] NULL ,
[QTY_SHP] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PROOLN_M] ADD
CONSTRAINT [PROOLN_M_K1] PRIMARY KEY CLUSTERED
([ORD_NUM],
[ORD_SPSQ],
[ORD_SEQ]) WITH FILLFACTOR = 90 ON [PRIMARY]
++++++++++++++++++++++++++++
Row filter for PROORD ("HEADER" Table)
SELECT <published_columns> FROM <<PROORD_M>>
WHERE ordr_dte >= '1998-01-01' or ordr_dte is null
++++++++++++++++++++++++++++
Row filter for PROOLN ("DETAIL" Table)
SELECT <published_columns> FROM <<PROOLN_M>>
WHERE exists
(SELECT * FROM AdvDbPrd.dbo.proord_m WHERE
prooln_m.ord_num = proord_m.ord_num
and (ordr_dte >= '1998-01-01' or ordr_dte is null))
(catchy names !!)
When I replicate new orders, I use filters:
The HEADER article filter is like:
Select * from HEADER
where ordr_dte >= '1998-01-01'
then
The DETAIL filter is like:
Select * from DETAIL
where exists (SELECT * FROM HEADER WHERE DETAIL.ord_num = HEADER.ord_num
and (HEADER.ordr_dte >= '1998-01-01' ))
This makes sure I only get DETAILS that match filtered HEADERS.
Everything's fine so far. Subscriber matches Publisher
Now, our order processing application sometimes deletes order records. Not
very often, but if a transaction is backed out before being posted or
something.
Anyway, the problem is that on the Publisher, the application deletes the
HEADER record, which gets deleted on the Subscriber .... FINE
Then the DETAIL gets deleted on the Publisher .... BUT ... since my
filter only deals with DETAILS that have an associated HEADER, and the HEADER
doesn't exist any more, I'm left with an orphan DETAIL record on the
subscriber.
What should I do ? Is there a better way to set this up to start with ?
if you are using merge replication have a look at join filters.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HomeBrew" <HomeBrew@.discussions.microsoft.com> wrote in message
news:29A8C1EA-42EC-40DE-873F-5F01C2FEBAFE@.microsoft.com...
> I have an order header we'll call HEADER and detail we'll call DETAIL
> (catchy names !!)
> When I replicate new orders, I use filters:
> The HEADER article filter is like:
> Select * from HEADER
> where ordr_dte >= '1998-01-01'
> then
> The DETAIL filter is like:
> Select * from DETAIL
> where exists (SELECT * FROM HEADER WHERE DETAIL.ord_num =
HEADER.ord_num
> and (HEADER.ordr_dte >= '1998-01-01' ))
> This makes sure I only get DETAILS that match filtered HEADERS.
> Everything's fine so far. Subscriber matches Publisher
> Now, our order processing application sometimes deletes order records. Not
> very often, but if a transaction is backed out before being posted or
> something.
> Anyway, the problem is that on the Publisher, the application deletes the
> HEADER record, which gets deleted on the Subscriber .... FINE
> Then the DETAIL gets deleted on the Publisher .... BUT ... since my
> filter only deals with DETAILS that have an associated HEADER, and the
HEADER
> doesn't exist any more, I'm left with an orphan DETAIL record on the
> subscriber.
> What should I do ? Is there a better way to set this up to start with ?
|||I'm just using regular Transactional Replication.
Is there some better filter logic I can use to avoid orphans ?
"Hilary Cotter" wrote:
> if you are using merge replication have a look at join filters.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "HomeBrew" <HomeBrew@.discussions.microsoft.com> wrote in message
> news:29A8C1EA-42EC-40DE-873F-5F01C2FEBAFE@.microsoft.com...
> HEADER.ord_num
> HEADER
>
>
|||Yes, but could you post your schema for both tables?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HomeBrew" <HomeBrew@.discussions.microsoft.com> wrote in message
news:582163AF-EEC9-4633-B723-70D2B72C9E1A@.microsoft.com...[vbcol=seagreen]
> I'm just using regular Transactional Replication.
> Is there some better filter logic I can use to avoid orphans ?
> "Hilary Cotter" wrote:
Not[vbcol=seagreen]
the[vbcol=seagreen]
my[vbcol=seagreen]
with ?[vbcol=seagreen]
|||Here are the schemas for the "HEADER" and "DETAIL" tables. (1 Header to
Many Details)
Below them are the snippets from my Replication Row Filters. These tables
are part of a package, so I can't make the Detail get deleted before the
Header.
Schema for PROORD_M ("HEADER" Table, I removed many irrelevant fields in
the interest of space)
CREATE TABLE [dbo].[PROORD_M] (
[ORD_NUM] [char] (8) NOT NULL ,
[CTG_DTE] [datetime] NOT NULL ,
[ORD_CTG] [char] (6) NOT NULL ,
[NUM_OPS] [char] (8) NOT NULL ,
[CTM_NBR] [char] (12) NOT NULL ,
[CTG_DTE2] [decimal](8, 0) NOT NULL ,
[ORD_CTG2] [char] (6) NOT NULL ,
[ORD_STA] [char] (1) NOT NULL ,
[ORD_NUM2] [char] (8) NOT NULL ,
[PO_NUM] [char] (20) NULL ,
[ORDR_DTE] [datetime] NULL ,
[SHP_DTE] [datetime] NULL ,
[ORD_TYPE] [char] (1) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PROORD_M] ADD
CONSTRAINT [PROORD_M_K1] PRIMARY KEY CLUSTERED
([ORD_NUM]) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [PROORD_M_K2] UNIQUE NONCLUSTERED
([CTG_DTE],
[ORD_CTG],
[NUM_OPS]) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [PROORD_M_K4] UNIQUE NONCLUSTERED
([ORD_STA],
[ORD_NUM2]) WITH FILLFACTOR = 90 ON [PRIMARY]
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++
Schema for PROOLN_M ("DETAIL" Table, I removed many irrelevant fields in
the interest of space)
CREATE TABLE [dbo].[PROOLN_M] (
[ORD_NUM] [char] (8) NOT NULL ,
[ORD_SPSQ] [char] (6) NOT NULL ,
[ORD_SEQ] [char] (5) NOT NULL ,
[SHP_CTM] [char] (12) NOT NULL ,
[ITM_NUM2] [char] (10) NOT NULL ,
[ITM_NUM3] [char] (10) NOT NULL ,
[ORD_NUM3] [char] (8) NOT NULL ,
[OLN_STA] [char] (2) NULL ,
[ITM_NUM] [char] (10) NULL
[QTY_ORD] [int] NULL ,
[QTY_SHP] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PROOLN_M] ADD
CONSTRAINT [PROOLN_M_K1] PRIMARY KEY CLUSTERED
([ORD_NUM],
[ORD_SPSQ],
[ORD_SEQ]) WITH FILLFACTOR = 90 ON [PRIMARY]
++++++++++++++++++++++++++++
Row filter for PROORD ("HEADER" Table)
SELECT <published_columns> FROM <<PROORD_M>>
WHERE ordr_dte >= '1998-01-01' or ordr_dte is null
++++++++++++++++++++++++++++
Row filter for PROOLN ("DETAIL" Table)
SELECT <published_columns> FROM <<PROOLN_M>>
WHERE exists
(SELECT * FROM AdvDbPrd.dbo.proord_m WHERE
prooln_m.ord_num = proord_m.ord_num
and (ordr_dte >= '1998-01-01' or ordr_dte is null))
Friday, March 9, 2012
Deleted Records Re-Appearing
If I delete a record in the client database and then replicate it will
reappear on the client. It seems that i have to delete it 2 or 3 times
before it finally "sticks". Can anyone explain what's going on here?
kind regards
Spike
Can you check the conflict viewer and see if perhaps the deletes are
conflicting with an update on the publisher?
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
"Spike" <Spike@.discussions.microsoft.com> wrote in message
news:7DD82CF7-487F-4390-B298-131F1157B6E3@.microsoft.com...
> If I delete a record in the client database and then replicate it will
> reappear on the client. It seems that i have to delete it 2 or 3 times
> before it finally "sticks". Can anyone explain what's going on here?
> kind regards
> Spike
reappear on the client. It seems that i have to delete it 2 or 3 times
before it finally "sticks". Can anyone explain what's going on here?
kind regards
Spike
Can you check the conflict viewer and see if perhaps the deletes are
conflicting with an update on the publisher?
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
"Spike" <Spike@.discussions.microsoft.com> wrote in message
news:7DD82CF7-487F-4390-B298-131F1157B6E3@.microsoft.com...
> If I delete a record in the client database and then replicate it will
> reappear on the client. It seems that i have to delete it 2 or 3 times
> before it finally "sticks". Can anyone explain what's going on here?
> kind regards
> Spike
Subscribe to:
Posts (Atom)