Invalid object name 'sysarticles' and Availability Groups

Question

Post reply

Invalid object name 'sysarticles' and Availability Groups

GBeezy

SSCarpal Tunnel

Points: 4723
More actions
July 30, 2016 at 9:39 am

#315754

I have a Availibility Groups set up between two SQL 2014 boxes, the primary is used for PROD and the secondary is used for Read Only purposes.. I am attempting to ALTER a table on the primary and add a column to it, but when I run the ALTER TABLE command, I get the following error:
Msg 208, Level 16, State 1, Procedure sysextendedarticlesview, Line 870
Invalid object name 'sysarticles'.
Googling the error it speaks that I would need to break replication and add the column. The problem with that is, we are not running replication, we are running Availibility groups and to the best of my knowledge schema changes should flow thru to the secondary just like any data changes would.
Is that a correct assumption? What can I do to get past this error?
Thank you in advance for any help and information!!!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GBeezy SSCarpal Tunnel Points: 4723 More actions · Answer 1

Let me add that this database used to be replicated. About 6 months ago we changed it to use Availability Groups.

Drilling into the Replication portion of Object Explorer, replication is not set up at all.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

IIRC, prefixing it with sys. did the trick => sys.sysarticles

Could be wrong, just have this recollection.

😎

GBeezy SSCarpal Tunnel Points: 4723 More actions · Answer 3

Thanks for the reply, but looking at another customer that we have set up for replication, I see sysarticles (not sys.articles) system view and it is in the distribution database. With Availibility Groups, there is no distribution database.

It seems as if this database still thinks its being replicated when it is not.

Any and all help is greatly appreciated

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Try publishing the DB. Create a single publication and drop it immediately, then remove replication from the DB again. May clean up the leftovers.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GBeezy SSCarpal Tunnel Points: 4723 More actions · Answer 5

GilaMonster, will that affect AG in anyway?

Thank you

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Shouldn't, but I've never replicated an AG DB before. Test it first.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GBeezy SSCarpal Tunnel Points: 4723 More actions · Answer 7

Thank You GilaMonster for the replies. I did get this figured out. There was in fact remnants of replication still on the database that was set up in AG. There were 4 database triggers related to replication that still exist and they were what was holding up the schema changes. Once they were disabled, we were able to run the build scripts without issue.

Thank You!!