0

Through the UI, I can see that I have 2 tables with filters on them. I need to be able to query this information but cannot find the table that contains the data. I did a search for any column in the Distribution database with a name like either "Filter" or "Where". The results are below. Unfortunately, none of these tables has the data I'm looking for in them.

 - SELECT * FROM IHarticles 
 - SELECT * FROM IHextendedArticleView 
 - SELECT * FROM MSarticles 
 - SELECT * FROM MSmerge_articlehistory 
 - SELECT * FROM MSmerge_articleresolver 
 - SELECT * FROM sysarticlecolumns 
 - SELECT * FROM sysarticles
 - SELECT * FROM sysextendedarticlesview
 - SELECT * FROM sysschemaarticles

Does anyone know where this data is held?

  • sysarticles does have 2 columns filter and filter_clause in distribution database. I have checked it on SQL Server 2012. What version are you on? – SQLPRODDBA May 13 '16 at 4:43
  • Thanks, I am using 2012 SP3 CU2 Enterprise; however, the values in the Filter and Filter_Clause fields in that table are all NULL. – PseudoToad May 14 '16 at 13:05
  • Are you sure filter is applied correctly on replication articles? If it is then it should definitely be present in sysarticles. – SQLPRODDBA May 17 '16 at 8:35
  • Sadly yes...we have 3 filters in place and that was one of the first things I checked. I was very surprised to find the fields empty. – PseudoToad May 20 '16 at 1:36
1

To research this, I configured transactional replication on my test server with 1 article having row filter. I did this for Adventureworks.Sales.Store table having [CustomerID] <10. Initially, I tried querying distribution..sysarticles to check the filter details. But, surprisingly it did not show any data in it.

Then I checked Adventureworks..sysarticles(i.e. in publication database) and that did the trick. See the below result:

enter image description here

| improve this answer | |
  • Thanks! I believe that is where I must have been looking as well. This helps a lot! – PseudoToad May 22 '16 at 3:22

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.