data will not be replicated. (i.e. replication triggers will not be fired
while using DTS!)
This works fine for INSERTS!
Is there any option in DTS which will NOT replicate the DELETES!
Ravi,
you'll need to deselect the 'use fast load' checkbox. Have a look at the
graphic on this screen: http://www.replicationanswers.com/Merge.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
If I DESELECT 'use fast load' data will be replicated. I don't want this !!!!!
By default in DTS 'use fast load' is selected. And the data doesn't get
replicated if DTS is used for data transfer. When ever i don't want data to
be replicated to subscriber I use DTS.
Now my question is,
Can I use DTS to DELETE the records which I DON'T want to replicate?
|||Ravi,
the reason this works for deletes is that there is a FIRE_TRIGGERS flag when
doing a bulk insert. So, you could likewise disable the triggers manually
before doing a delete. There is no option to do this in DTS, so it would
have to be done in TSQL. However, having data unsynchronized in this way
without the use of filters can lead to a complicated (unmaintainable) setup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
1. Where can I see FIRE_TRIGGERS flag ?
2. I can not alter a REPLICATION trigger from QA. (I can do it from EM)
Or am I missing something here?
3. I got the following code from your site.
(http://www.replicationanswers.com/Merge.asp)
IF Not UPDATE(extra column name)
BEGIN
existing trigger code
END
Can I use the same for DELETES...something like....
IF Not DELETE(extra column name)
BEGIN
existing trigger code
END
I have a time stamp column (not timestamp data type) on a table which no
user have access. If I fire a DELETE with this column, with the above code
replication trigger will not be fired. Can I do this? What do you feel?
"Paul Ibison" wrote:
> Ravi,
> the reason this works for deletes is that there is a FIRE_TRIGGERS flag when
> doing a bulk insert. So, you could likewise disable the triggers manually
> before doing a delete. There is no option to do this in DTS, so it would
> have to be done in TSQL. However, having data unsynchronized in this way
> without the use of filters can lead to a complicated (unmaintainable) setup.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Ravi,
(1) the FIRE_TRIGGERS flag is a part of BULK INSERT.
(2) please can you post up your TSQL and the error, as I can do this OK.
(3) there is no corresponding DELETE function. Actually this makes no sense,
as regardless of the column, it is always deleted
If you want to prevent the delete from DTS, what task is initiating the
delete? Is it the Data Driven Query task? And you want these deleted to not
fire the replication triggers? In this case it looks like you'll need a
switch in the trigger. This needs to distinguish between your DTS and
anything else. Perhaps this could be on HOST_NAME(), suser_sname() or
something else like that. Alternatively you could alter the triggers before
executing the DTS. This is now a bit more dodgy, because you'll lose merge
replicating genuine deletes, so you have to ensure this doesn't occur by
preventing 'normal' access to the table.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks a lot for your input. Solved my problem!!
(1) What are BULK INSERT operations?
I know the following
a. BULK INSERT
b. DTS
c. is SELECT INTO a bulk insert command?
What other BULK INSERT operations do we have in SQL2K?
(2) I tried once again to alter, and it is OK. I was wrong here. I think
when ever i want to delete i will dynamically alter the trigger. I have a
green zone. So I can alter the trigger in the green zone.
(3) Have you ever seen a more dumber question?!!!! I think it was work
stress.
May I was dreaming ;-)
Thanks a lot for your help.
"Paul Ibison" wrote:
> Ravi,
> (1) the FIRE_TRIGGERS flag is a part of BULK INSERT.
> (2) please can you post up your TSQL and the error, as I can do this OK.
> (3) there is no corresponding DELETE function. Actually this makes no sense,
> as regardless of the column, it is always deleted
> If you want to prevent the delete from DTS, what task is initiating the
> delete? Is it the Data Driven Query task? And you want these deleted to not
> fire the replication triggers? In this case it looks like you'll need a
> switch in the trigger. This needs to distinguish between your DTS and
> anything else. Perhaps this could be on HOST_NAME(), suser_sname() or
> something else like that. Alternatively you could alter the triggers before
> executing the DTS. This is now a bit more dodgy, because you'll lose merge
> replicating genuine deletes, so you have to ensure this doesn't occur by
> preventing 'normal' access to the table.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Ravi,
for (1) you can't select into an existing table, so the triggers won't
exist.
Cheers.
Paul
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:EB7B753C-71E7-4F86-8093-551EFA44B85A@.microsoft.com...[vbcol=seagreen]
> Paul,
> Thanks a lot for your input. Solved my problem!!
> (1) What are BULK INSERT operations?
> I know the following
> a. BULK INSERT
> b. DTS
> c. is SELECT INTO a bulk insert command?
> What other BULK INSERT operations do we have in SQL2K?
> (2) I tried once again to alter, and it is OK. I was wrong here. I think
> when ever i want to delete i will dynamically alter the trigger. I have a
> green zone. So I can alter the trigger in the green zone.
> (3) Have you ever seen a more dumber question?!!!! I think it was work
> stress.
> May I was dreaming ;-)
> Thanks a lot for your help.
> "Paul Ibison" wrote:
No comments:
Post a Comment