Conditional Deletion in Replication

I am developing a transactional replication plan. I have one table
that most of the time I do not want to replicate deletions. There is
an exception to this rule. If the status_id of another table is false,
then the deletion should be replicated.
I have been having difficulty trying to put this condition in place. I
have tried modifying the sp_MSdel_dboTable stored procedure. I have
removed the if @.@.rowcount check and replication errors with the
messages:
-- Error executing a batch of commands. Retrying individual commands.
Eventually, it will fail with the message: if @.@.trancount > 0 rollback
tran
Any ideas on how to get around this issue?
Publisher: SQL 2000
Distributor: On a separate server
Subscriber: SQL 2005
Thanks for any help I can get.
You need to replicate an indexed view as a table on the subscriber. The
indexed view would only "show" rows which match the status_id in the other
table.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"emreeves" <em_reeves@.lycos.com> wrote in message
news:1143754411.192208.291820@.i40g2000cwc.googlegr oups.com...
>I am developing a transactional replication plan. I have one table
> that most of the time I do not want to replicate deletions. There is
> an exception to this rule. If the status_id of another table is false,
> then the deletion should be replicated.
> I have been having difficulty trying to put this condition in place. I
> have tried modifying the sp_MSdel_dboTable stored procedure. I have
> removed the if @.@.rowcount check and replication errors with the
> messages:
> -- Error executing a batch of commands. Retrying individual commands.
> Eventually, it will fail with the message: if @.@.trancount > 0 rollback
> tran
> Any ideas on how to get around this issue?
>
> Publisher: SQL 2000
> Distributor: On a separate server
> Subscriber: SQL 2005
> Thanks for any help I can get.
>
|||Thanks! This sounds better than the next approach I was going to
try... creating a 1st Insert/Update trans replication, and a 2nd Delete
only with row filtering.
BTW - Thanks very much for your replication book. It has helped loads
this past few weeks.

Concatenation tweaking

I'm trying to create an exception report that compares the 4 amt fields
between 2 tables and then list the column(s) where the amts don't match. I
hard coded the comma from case 2 thru 4 and the resulting text starts with a
comma when PROJ_V_FEE_AMT amts matched. Can someone help me tweak the code?
Thanks.
SELECT PROJ,
PROJ_NAME,
...,
EXCEPTIONS =
CASE
WHEN (p.PROJ_V_FEE_AMT <> PROJ_V_FEE_AMT_PM_SUM) THEN 'PROJ_V_FEE_AMT'
ELSE ''
END
+
CASE
WHEN (p.PROJ_V_CST_AMT <> PROJ_V_CST_AMT_PM_SUM) THEN ', PROJ_V_CST_AMT'
ELSE ''
END
+
CASE
WHEN (p.PROJ_F_FEE_AMT <> PROJ_F_FEE_AMT_PM_SUM) THEN ', PROJ_F_FEE_AMT'
ELSE ''
END
+
CASE
WHEN (p.PROJ_F_CST_AMT <> PROJ_F_CST_AMT_PM_SUM) THEN ', PROJ_F_CST_AMT'
ELSE ''
ENDTry this too:
EXCEPTIONS =
Case When PROJ_V_FEE_AMT_PM_SUM <> Any
(Select p.PROJ_V_FEE_AMT Union Select p.PROJ_V_CST_AMT Union
Select p.PROJ_F_FEE_AMT Union Select p.PROJ_F_CST_AMT)
Then Reverse(Substring(Reverse(
IsNull(CASE WHEN p.PROJ_V_FEE_AMT <> PROJ_V_FEE_AMT_PM_SUM
THEN 'PROJ_V_FEE_AMT' End + ', ', '') +
IsNull(CASE WHEN p.PROJ_V_CST_AMT <> PROJ_V_CST_AMT_PM_SUM
THEN 'PROJ_V_CST_AMT' End + ', ', '') +
IsNull(CASE WHEN p.PROJ_F_FEE_AMT <> PROJ_F_FEE_AMT_PM_SUM
THEN 'PROJ_F_FEE_AMT' End + ', ', '') +
IsNull(CASE WHEN p.PROJ_F_CST_AMT <> PROJ_F_CST_AMT_PM_SUM
THEN 'PROJ_F_CST_AMT'End + ', ', '')), 3, 100))
Else Null End
"danlin" wrote:

> I'm trying to create an exception report that compares the 4 amt fields
> between 2 tables and then list the column(s) where the amts don't match.
I
> hard coded the comma from case 2 thru 4 and the resulting text starts with
a
> comma when PROJ_V_FEE_AMT amts matched. Can someone help me tweak the cod
e?
> Thanks.
> SELECT PROJ,
> PROJ_NAME,
> ...,
> EXCEPTIONS =
> CASE
> WHEN (p.PROJ_V_FEE_AMT <> PROJ_V_FEE_AMT_PM_SUM) THEN 'PROJ_V_FEE_AMT'
> ELSE ''
> END
> +
> CASE
> WHEN (p.PROJ_V_CST_AMT <> PROJ_V_CST_AMT_PM_SUM) THEN ', PROJ_V_CST_AMT'
> ELSE ''
> END
> +
> CASE
> WHEN (p.PROJ_F_FEE_AMT <> PROJ_F_FEE_AMT_PM_SUM) THEN ', PROJ_F_FEE_AMT'
> ELSE ''
> END
> +
> CASE
> WHEN (p.PROJ_F_CST_AMT <> PROJ_F_CST_AMT_PM_SUM) THEN ', PROJ_F_CST_AMT'
> ELSE ''
> END