none
Error 208 Invalid Object Name 'dbo.sysextendedarticlesview': Deleting Publication/Distribution

    Question

  • I have an issue when trying to delete the replication that I've set up. I'm using SQL 2008 SP3. Seem like last week the replication failed and I have to set a new one again. I've been doing the instructions in the link below. It worked last time when I tried to redo the replication using Management Studio but right now it gives me the error: Error 208 Invalid Object Name 'dbo.sysextendedarticlesview'.

    http://technet.microsoft.com/en-us/library/ms151759.aspx#Prerequisites

    I also tried to perform the sp_removedbreplication, and it also gave the same error. Also, the view still existed in the distributor db but it's empty when I ran it. I don't have this view in the other subscriber so I can't restore it.

    Can anyone suggest what to do next? All I need is to start a new replication and I can't move forward due to this error. 

    Thanks.

    Friday, April 13, 2012 5:12 PM

Answers

  • Does the view exist in the publication database?

    Try creating the missing view - then try to drop the publication:

    create view dbo.sysextendedarticlesview            
    as                 
    select 
    	artid, 
    	creation_script, 
    	del_cmd, 
    	description, 
    	dest_table, 
    	filter, 
    	filter_clause, 
    	ins_cmd, 
    	name, 
    	objid, 
    	pubid, 
    	pre_creation_cmd, 
    	[status], 
    	sync_objid, 
    	type, 
    	upd_cmd,
    	schema_option, 
    	dest_owner, 
    	ins_scripting_proc, 
    	del_scripting_proc,
    	upd_scripting_proc, 
    	custom_script, 
    	fire_triggers_on_snapshot
    from sysarticles
    union ALL
    select 
    	artid, 
    	creation_script, 
    	NULL, 
    	description,
    	dest_object, 
    	NULL, 
    	NULL, 
    	NULL, 
    	name, 
    	objid, 
    	pubid,
    	pre_creation_cmd,
    	status, 
    	NULL, 
    	type, 
    	NULL,
    	schema_option, 
    	dest_owner, 
    	NULL, 
    	NULL, 
    	NULL, 
    	NULL, 
    	0
    from sysschemaarticles
    go

    Does that help?


    Brandon Williams (blog | linkedin)

    • Marked as answer by deafish00 Monday, April 16, 2012 11:50 PM
    Friday, April 13, 2012 5:28 PM

All replies

  • Does the view exist in the publication database?

    Try creating the missing view - then try to drop the publication:

    create view dbo.sysextendedarticlesview            
    as                 
    select 
    	artid, 
    	creation_script, 
    	del_cmd, 
    	description, 
    	dest_table, 
    	filter, 
    	filter_clause, 
    	ins_cmd, 
    	name, 
    	objid, 
    	pubid, 
    	pre_creation_cmd, 
    	[status], 
    	sync_objid, 
    	type, 
    	upd_cmd,
    	schema_option, 
    	dest_owner, 
    	ins_scripting_proc, 
    	del_scripting_proc,
    	upd_scripting_proc, 
    	custom_script, 
    	fire_triggers_on_snapshot
    from sysarticles
    union ALL
    select 
    	artid, 
    	creation_script, 
    	NULL, 
    	description,
    	dest_object, 
    	NULL, 
    	NULL, 
    	NULL, 
    	name, 
    	objid, 
    	pubid,
    	pre_creation_cmd,
    	status, 
    	NULL, 
    	type, 
    	NULL,
    	schema_option, 
    	dest_owner, 
    	NULL, 
    	NULL, 
    	NULL, 
    	NULL, 
    	0
    from sysschemaarticles
    go

    Does that help?


    Brandon Williams (blog | linkedin)

    • Marked as answer by deafish00 Monday, April 16, 2012 11:50 PM
    Friday, April 13, 2012 5:28 PM
  • Thanks! That fixes my issue with the invalid object. That was weird because I thought this view is located in my Distributor database in the System Databases but it was on my actual database itself. Now I have a different issue but I will try to resolve it first before I ask for assistance.

    Thank you once again.

    Monday, April 16, 2012 11:52 PM