473,842 Members | 1,664 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Filter records during Transactional Replication.

Hi All,

I have a table with a column DeletedDate which stores a logical delete
of a record.

I need to set up transactional replication for reporting purposes that
this deleted records should not be replicated to the subscriber. That
is, if i see a value on the DeletedDate, I don't want that record to
be picked up for replication.

At the same time, when someone marks the record for deletion (by
putting a date on the DeleteDate column), I want that record to be
deleted on the subscriber database. (I can also set up a job to do the
deletes on the subscriber but i'd rather let the replication take care
of it).

Can this scenario be implemented in Microsoft SQL 2000? I would
appreciate any ideas / thoughts in this matter.

Thanks in advance,
Aravin Rajendra.

Apr 12 '07 #1
3 3074
Basically it looks like you are doing soft deletes.

In this case you want a filter on your table that looks like this where
deleteddate is null

Then you want to modify your modify replication proc so that if the update
for this table involves a value for the deleteddate column that it does not
update this row but rather deletes it.

But I'm a little confused - you say " if i see a value on the DeletedDate, I
don't want that record to be picked up for replication."

But then in the next statement you say "when someone marks the record for
deletion (by putting a date on the DeleteDate column), I want that record to
be
deleted on the subscriber database"

These sound like mutually exclusive statements.

--
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

"Query Builder" <qu**********@g mail.comwrote in message
news:11******** **************@ e65g2000hsc.goo glegroups.com.. .
Hi All,

I have a table with a column DeletedDate which stores a logical delete
of a record.

I need to set up transactional replication for reporting purposes that
this deleted records should not be replicated to the subscriber. That
is, if i see a value on the DeletedDate, I don't want that record to
be picked up for replication.

At the same time, when someone marks the record for deletion (by
putting a date on the DeleteDate column), I want that record to be
deleted on the subscriber database. (I can also set up a job to do the
deletes on the subscriber but i'd rather let the replication take care
of it).

Can this scenario be implemented in Microsoft SQL 2000? I would
appreciate any ideas / thoughts in this matter.

Thanks in advance,
Aravin Rajendra.

Apr 13 '07 #2
Hilary Cotter wrote:
But I'm a little confused - you say " if i see a value on the DeletedDate, I
don't want that record to be picked up for replication."

But then in the next statement you say "when someone marks the record for
deletion (by putting a date on the DeleteDate column), I want that record to
be
deleted on the subscriber database"

These sound like mutually exclusive statements.
I interpreted the statements as "if DeleteDate is already non-null then
don't replicate" and "if DeleteDate becomes non-null then delete from
subscriber". In particular, a row with DeleteDate non-null might have
some other column changed (in which case it shouldn't be replicated), or
might have DeleteDate set back to null (in which case it should be
replicated).
Apr 13 '07 #3
First, Thanks for you input.

I apologize for the confusion. Ed Murphy is correct.

Here is a clear explanation of the situation.

Lets say the table has 10 records.
2 of them have a DeleteDate value in the column.

So, I want the 8 records to be replicated to the subscriber.

If a user goes and updates another record with a value in the
deleteDate, I want that record to be deleted in the subscriber. (Now
out of 10, three of them are marked with a value in the DeleteDate).

Also, if a user goes and removes the DeleteDate value on one of the 3
records, I want that record to be inserted into the subscriber table.

I am going to try the way Hilary mentioned and let everyone know the
results.

Thanks again for the help....

Regards,
Aravin

On Apr 13, 1:00 pm, Ed Murphy <emurph...@soca l.rr.comwrote:
Hilary Cotter wrote:
But I'm a little confused - you say " if i see a value on the DeletedDate, I
don't want that record to be picked up for replication."
But then in the next statement you say "when someone marks the record for
deletion (by putting a date on the DeleteDate column), I want that record to
be
deleted on the subscriber database"
These sound like mutually exclusive statements.

I interpreted the statements as "if DeleteDate is already non-null then
don't replicate" and "if DeleteDate becomes non-null then delete from
subscriber". In particular, a row with DeleteDate non-null might have
some other column changed (in which case it shouldn't be replicated), or
might have DeleteDate set back to null (in which case it should be
replicated).

Apr 20 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1281
by: Edward | last post by:
Here is the architecture for the Transactional Replication: SERVER1 - PUBLISHER - Also acts as back-end database for Enterprise-wide application written in VB. Uses ~430 stored procedures. SERVER2 - SUBSCRIBER - Also acts as back-end database for Business Objects. Replication is set for immediate update.
3
1728
by: Louis Frolio | last post by:
Greetings All, I was hoping that a replication sage might be able to answer a question for me. I want to have one subscriber subscribing to N publishers. I essentially have a company that has a main headquarters and three satellite offices. I want each of the satellite offices to push their data up to the master database. From what I have read it seems like this should not be a problem. Some questions that come to mind are: ...
1
1831
by: tgru | last post by:
I have taken over a transactional replication setup that is being used for fault tolerance (I know, I know...). The scenario I am concerned with is where the publisher goes down due to failure, so we need to point our application at the subscriber and start updating the data there. We are not using the immediate updating option. How do I go about re-syncing the publisher with the data that been added to, or changed on the subscriber,...
2
2065
by: DickChristoph | last post by:
Hi I tried posting this query in microsoft.public.sqlserver.programming but got no response. I am new to replication but I am trying to setup up a transactional replication of tables from one database to another in MSSQL 2000 (SP2). My target tables have primary keys defined. Under publication properties I go to the snapshot tab and for each table I clear the check box that says
0
1473
by: laststubborn | last post by:
Hi all, I am trying to create a transactional replication, however the subscriber is in the Internet, physically they are not at the same domain with main database server. My question is how am I gonna create the replication to the other machine which is in the Internet not at the same domain server with the database server? Any help please Thanks
1
2209
by: laststubborn | last post by:
Hi All, I would like to set up transactional replication with failover cluster. Did anybody tried this? Does it work together? Is there anything I should be coutios when I set up these together? or Is there any glitches? If somebody helps me I will really appreciate it Thanks
0
2061
by: apilz | last post by:
I am trying to set up two Dbs for transactional replication through the wizard. Every time i try to complete the process i receive the same error message. "Property LoginSecure cannot be changed or read after a connection string has been set" I have tried to modify the connection properties to every available possibility without success. I have modified the replication folder to be accessible to everyone, again no success. Any...
0
1626
by: Query Builder | last post by:
Hi All, I have a table with a column DeletedDate which stores a logical delete of a record. I need to set up transactional replication for reporting purposes that this deleted records should not be replicated to the subscriber. That is, if i see a value on the DeletedDate, I don't want that record to be picked up for replication.
2
4269
by: Query Builder | last post by:
Hi, I have transactional replication set up on on of our MS SQL 2000 (SP4) Std Edition database server Because of an unfortunate scenario, I had to restore one of the publication databases. I scripted the replication module and dropped the publication first. Then did a full restore. When I try to set up the replication thru the script, it created the
0
9872
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10945
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10612
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10672
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10314
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9453
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7858
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
4499
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4089
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.