Hassan,
I suppose the safest way is to use READ COMMITTED SNAPSHOT. If a transaction
fails on the publisher and is rolled back, this will still occur on the
subscriber and NOLOCK will cause dirty reads.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Yes - but are dirty reads ever a problem for you? Rollbacks might cause yiu
to read incorrect data.
Cheers,
Paul Ibison

You lost me Paul..

I thought replication starts propogating until the transaction is committed
on the publisher ...am i not right ?

Say if i do a

Begin Tran

Insert tableA values(1)
Insert tableA values(2)
Insert tableA values(3)
....
....
Insert tableA values(1000000)

Commit Tran

So are you saying that even before it completes inserting the 1 million
records, data will start propagating to the subscribers ?

If true, I was not aware of it. I thought it would commit the million rows
and then start replicating.


[quoted text, click to view]

Hassan,

I don't see a problem in the scenario you mentioned. I am thinking
differently - NOLOCK could cause inconsistant data when a transaction on the
subscriber has been partially complete, before the rest of the transaction
completes, in the case of a successful transaction.

Begin Tran

Insert tableA values(1)
Insert tableB values(2)
Point A: Insert tableC values(3)
...
...
Insert tableZ values(1000000)

Commit Tran

Doing a read at point A might be problematic for reports - it all depends on
how the TSQL in the reports go.

The real case I had in mind is a stored procedure that fails and gets rolled
back in a stored procedure - you'll get more problematic dirty reads
occurring this way.

Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)