1

Having in mind that I can add computed columns to the subscription tables according to this link:

SQL Server : Transactional Replication Computed Column

That I am already doing, and also adding indexes to replicated tables (in the subscriber server)

However,

While I am doing it, I have to face too many locks and blocks to/from the replication as you can see on the picture below:

enter image description here

this is the replication command I was blocking while creating my index:

create procedure [sp_MSupd_dbotblBGiftVoucher]
        @c1 binary(24) = NULL,
        @c2 smallint = NULL,
        @c3 varchar(20) = NULL,
        @c4 int = NULL,
        @c5 varchar(20) = NULL,
        @c6 int = NULL,
        @c7 bit = NULL,
        @c8 char(1) = NULL,
        @pkc1 binary(24) = NULL,
        @bitmap binary(1)
as
begin  
if (substring(@bitmap,1,1) & 1 = 1)
begin 

update [dbo].[tblBGiftVoucher] set
        [strVoucherNumber] = case substring(@bitmap,1,1) & 1 when 1 then @c1 else [strVoucherNumber] end,
        [sintMarketID] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [sintMarketID] end,
        [strIssuedBxOrderNo] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [strIssuedBxOrderNo] end,
        [lngIssuedUserID] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [lngIssuedUserID] end,
        [strReedemedBxOrderNo] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [strReedemedBxOrderNo] end,
        [lngRedeemedUserID] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [lngRedeemedUserID] end,
        [blnVoid] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [blnVoid] end,
        [strCheckDigit] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [strCheckDigit] end
    where [strVoucherNumber] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end  
else
begin 

update [dbo].[tblBGiftVoucher] set
        [sintMarketID] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [sintMarketID] end,
        [strIssuedBxOrderNo] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [strIssuedBxOrderNo] end,
        [lngIssuedUserID] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [lngIssuedUserID] end,
        [strReedemedBxOrderNo] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [strReedemedBxOrderNo] end,
        [lngRedeemedUserID] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [lngRedeemedUserID] end,
        [blnVoid] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [blnVoid] end,
        [strCheckDigit] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [strCheckDigit] end
    where [strVoucherNumber] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end 
end 

there are too many servers, and I am not sure of which server is the publisher, where is the distributor database, and if there is a distributor server, which one is it?

Is there a way I can find the name of the publisher and distributor (server and database), from the subscriber?

some information is here: How to restart the distributor agent of transactional replication?

but I am still struggling to pause the replication for at least one article, but I could pause it for the whole publication.

What I want to achieve is to pause the replication for my current subscription (the database where I am on)...

3

Is there a way I can find the name of the publisher and distributor (server and database), from the subscriber?

For push subscriptions from the subscriber you can query MSsubscription_agents to get the publisher and publisher database.

For pull subscriptions from the subscriber you can query MSsubscription_properties to get the publisher and distributor. Have a look at the columns publisher, publisher_db, and distributor. The distribution database name will likely be distribution.

  • 1
    For push you can also query MSsubscription_agents to get the publisher and publisher_db. – Brandon Williams Sep 29 '15 at 23:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.