when we add the subscriptions

exec sp_addsubscription

@publication = N'trans_journal_b'

, @subscriber = N'SERVER\SUBSCRIBER'

, @destination_db = N'mydb'

, @subscription_type = N'Push'

, @sync_type = N'None' -- 'Automatic', 'replication support only'

,...

I checked what you said, and we are upped to build 3161 even though it did not address the problem exactly we have. And it did not help.

But, amazingly, if...

Yes, they are separate publications.

We can reproduce the whole thing in our lab, so we might file this to Microsoft.

In the meantime I think we are going back to...

Yes, sorry, t11, t12,..., t18 is pub1

and t21, t22, ..., t28 is pub2

They are separate.

This is the puzzling thing. While I am turning off and on pub1, pub2 looses...

Ok, t11, t12, t13, ..., t18 is collecting transactions throughout today. I am replicating them to the subscriber to t11, t12, ..., t18 tables.

T21, T22, ..., T28 are "passive tables",...

I am having problems with other publications. They "loose" transactions.

There is one more thing. The 8 tables I am truncating is in a view with another 8 tables. So, imagine...

Deletes are expensive for us. The combined rowcount of the 8 tables can reach 2 million rows per day. I am not replicating deletes.

I was thinking of maybe I am switching off the entire log reader, But i don't.

I hope this helps.

exec sp_dropsubscription

@publication = N'mypublication'

, @article = N'all'

,

Oh, okay, it does not support the (nolock) hint.

If I take it out, it works!

For example:

declare @remoteserver nvarchar(50)

declare @cmd nvarchar(255)

declare @rc int

declare @nCount int

select @remoteserver = 'SYED_ALI,1533'

set @cmd = 'select @nCount = count(*) from ['+@remoteserver+'].chartwellgame.dbo.CountPlaceholder (nolock)'

exec sp_executesql @cmd, N'@nCount int out', @nCount...