Mateen
asked on
What is "syspublications"
Sql Server 2000:
I am trying to delete an article from a publication.
My code is
exec sp_droparticle @publication = N'prod_b29', @article = N'order_from_det', @force_invalidate_snapshot
I am receiving this error.
[ Invalid object name 'syspublications'. ]
I am trying to delete an article from a publication.
My code is
exec sp_droparticle @publication = N'prod_b29', @article = N'order_from_det', @force_invalidate_snapshot
I am receiving this error.
[ Invalid object name 'syspublications'. ]
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
in the installation folders of sql server, you should find a .sql file that is to (re)create the replication tables (search for syspublications in the files), and run that .sql file using query analyser
ASKER
Problem solved.
Now writing.
Now writing.
ASKER
1) You must be in a publishing database
2) Must drop subscriber first.
3) Then drop article.
use [prod_b29]
exec sp_dropsubscription @publication = N'prod_b29', @article = N'order_from_mst', @subscriber = N'all', @destination_db = N'all'
exec sp_droparticle @publication = N'prod_b29', @article = N'order_from_mst', @force_invalidate_snapshot
Done.
2) Must drop subscriber first.
3) Then drop article.
use [prod_b29]
exec sp_dropsubscription @publication = N'prod_b29', @article = N'order_from_mst', @subscriber = N'all', @destination_db = N'all'
exec sp_droparticle @publication = N'prod_b29', @article = N'order_from_mst', @force_invalidate_snapshot
Done.
I had tried running the .sql queries and now stuck while initialising the publisher; which prompted me the errors msg as follows:
TITLE: New Publication Wizard
--------------------------
SQL Server could not create publication 'test2'.
--------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Conne
--------------------------
Invalid column name 'allow_initialize_from_bac
Invalid column name 'min_autonosync_lsn'.
Invalid column name 'replicate_ddl'.
Invalid column name 'options'.
Object 'test2' does not exist or is not a valid object for this operation.
Changed database context to 'DB'. (Microsoft SQL Server, Error: 207)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2153&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
--------------------------
BUTTONS:
OK
--------------------------
TITLE: New Publication Wizard
--------------------------
SQL Server could not create publication 'test2'.
--------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Conne
--------------------------
Invalid column name 'allow_initialize_from_bac
Invalid column name 'min_autonosync_lsn'.
Invalid column name 'replicate_ddl'.
Invalid column name 'options'.
Object 'test2' does not exist or is not a valid object for this operation.
Changed database context to 'DB'. (Microsoft SQL Server, Error: 207)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2153&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
--------------------------
BUTTONS:
OK
--------------------------
ASKER
<< One option is to run the replication
script that comes in the INSTALL directory to recreate the replication system
objects. This way the syspublications will be recreated.
>>
I completely did not understand.