2

TL;DR: This question refer to sql server 2016, transactional replication

I've got a table that shows up in the subscription but not in the publication.

Here are the details:


In one of my servers in the USA I have a publication called ORCA_Repl_Pub.

In one of my servers in Europe I have a subscription (you guessed right: Orca_Repl_Sub) to the ORCA_Repl_Pub publication.

My issue is with table dbo.repl_application.

On the publisher I have a look at the articles and it is not there, you can see it is unchecked on the picture below:

enter image description here

or the same when I go to my publication_db and run the following:

sp_helpsubscription

or the following query has the same effect:

select  
db_name() PublisherDB 
, sp.name as PublisherName 
, sa.name as TableName 
, UPPER(srv.srvname) as SubscriberServerName 
from dbo.syspublications sp  
join dbo.sysarticles sa on sp.pubid = sa.pubid 
join dbo.syssubscriptions s on sa.artid = s.artid 
join master.dbo.sysservers srv on s.srvid = srv.srvid 

enter image description here

But then when I check on the subscriber_db side, on the subscriber server, by running the following query, to find out everything possible about the table dbo.repl_application I get:

SELECT *
FROM 
    dbo.MSreplication_objects R
        INNER JOIN sys.objects so 
                ON r.object_name = so.name 
               AND so.type = 'P' --stored procedures

        INNER JOIN sys.sql_expression_dependencies dp 
                ON so.object_id = dp.referencing_id

        INNER JOIN sys.objects ot 
                ON dp.referenced_id = ot.object_id  
               AND r.article = ot.name
where r.article = 'repl_application'

I have broken the picture down in 3 pieces for it to fit here:

enter image description here

enter image description here

enter image description here

Basically everything seems to be correct!!!

and using some magic I can see that the table in question has been used, as you can see on the picture below (the date of posting is 14-aug-2019:

enter image description here

So the question is:

how and why this repl_application is in the subscription but not in the publication?

I think it might be:

  1. because on a previous version of that publication it was there, and as we deployed a new system, that table was no longer included.

But what about the table creation date and the last table update time, and last time the table was read?

  1. the database was not wiped out completely or the subscription was not 100% dropped and that table remained with a reference on dbo.MSreplication_objects

Either way, all is working fine, I just would love to know if I could find out on the subscription side, if there is any indication anywhere, that that table should not be showing up.

so, if you have any ideas regarding that, please let me know and I will dig around it.

Your Answer

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

Browse other questions tagged or ask your own question.