deleting SQL Srv 2K5 publication with no Agents

I have the case where a SQL Srv 2K5 publication has no agents - agents were accidentally erased during backup/restore. I was abel to erase subscription.

The error is "The specified @.job_name <jab name> does not exist. (Microsft SQL Server, Error: 14262)

Is there any way to remove publication?

Help will be greatly appreciated!

Vladimir

Do you have trouble in dropping subscription/publication? Error looks like to happen when you drop a remote pull subscription.

Anyway, you may call SP directly to drop the subscription, then publication. Please refer to BOL.

Similar call will be like:

use [PubDB]
exec sp_dropmergesubscription

exec sp_mergesubscription_cleanup

exec sp_dropmergepublication

Hope it will be helpful.

Thanks

Yunjing

|||

Hello,

I am dropping xactional publication, not merge subscription. Normally, it is not a problem, but in this case all three agent jobs are gone.

When I try to delete it, I am getting message about absent snapshot agent, Error 20678 "|Could not find the regular snapshot job for the specified publication <publication name>. Supply either @.job_id or @.job name to idnetify the job."

But the jobs like I said is gone.

I have tried sp_droppublication. Got error: "Message 14013, This database is not enabled for publication", which does not make sense since publication is there already.

Thanks for trying.

Vladimir

|||

Hello, Vladimir,

I think meta data got messed up somehow. Could you check whether publications are still there?

please call the below two sql to see if you can get any thing.

On publisher server, select * from [PublicationDB]..syspublications

On distributor server, select * from [distributionDB]..MSpublications

Thanks

Yunjing

|||

Hi Yunjing,

The syspublications does not have any rows.

MSpublications does not list this "orphaned" publication, but it lists all other "healthy" publication I created.

Yesterday I backed up and restored the publisher database. There is the switch during restore 'keep_publication' which gave me hope that

if I will not set it during restore that will break link b/w db and publication. But after restore error still percists.

R-clicking on publication allows to create publication drop script. I ran it, and against every article in publication the message was "the publication does not exist'. This is while I see that publication in GUI.

Also, r-clicking, I tried to start Snapshot agent to create snapshot - the absent snapshot may be the root of the problem.

I guess it was erase together with erasure of snapshot agent.

It failed at the end with error "Cannot insert NULL into column "publisher_database_id", table "distribution.dbo.MSrepl_transactions".

Thanks Yunjing for keeping up.

Vladimir

sql

Deleting Pub Tables

I previously had a publication set up on my database. I've dropped it but
still have a number of system tables in my database of the form
conflict_dbnamePub_tablename. Is there a way I can delete these
--
Thanks
RonFTake a look at this article:
http://www.mssqlserver.com/replicat...on_cleanup.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"RonF" <RonF@.discussions.microsoft.com> wrote in message
news:E22685FE-F085-4B32-AF26-F2C5814FDD5C@.microsoft.com...
> I previously had a publication set up on my database. I've dropped it but
> still have a number of system tables in my database of the form
> conflict_dbnamePub_tablename. Is there a way I can delete these
> --
> Thanks
> RonF|||Thanks for the assistance!
Ron
"Dejan Sarka" wrote:

> Take a look at this article:
> http://www.mssqlserver.com/replicat...on_cleanup.asp.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "RonF" <RonF@.discussions.microsoft.com> wrote in message
> news:E22685FE-F085-4B32-AF26-F2C5814FDD5C@.microsoft.com...
>
>

Deleting Pub Tables

I previously had a publication set up on my database. I've dropped it but
still have a number of system tables in my database of the form
conflict_dbnamePub_tablename. Is there a way I can delete these
--
Thanks
RonFTake a look at this article:
http://www.mssqlserver.com/replication/bp_manual_replication_cleanup.asp.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"RonF" <RonF@.discussions.microsoft.com> wrote in message
news:E22685FE-F085-4B32-AF26-F2C5814FDD5C@.microsoft.com...
> I previously had a publication set up on my database. I've dropped it but
> still have a number of system tables in my database of the form
> conflict_dbnamePub_tablename. Is there a way I can delete these
> --
> Thanks
> RonF|||Thanks for the assistance!
Ron
"Dejan Sarka" wrote:
> Take a look at this article:
> http://www.mssqlserver.com/replication/bp_manual_replication_cleanup.asp.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "RonF" <RonF@.discussions.microsoft.com> wrote in message
> news:E22685FE-F085-4B32-AF26-F2C5814FDD5C@.microsoft.com...
> > I previously had a publication set up on my database. I've dropped it but
> > still have a number of system tables in my database of the form
> > conflict_dbnamePub_tablename. Is there a way I can delete these
> > --
> > Thanks
> > RonF
>
>

Deleting Pub Tables

I previously had a publication set up on my database. I've dropped it but
still have a number of system tables in my database of the form
conflict_dbnamePub_tablename. Is there a way I can delete these
Thanks
RonF
Take a look at this article:
http://www.mssqlserver.com/replicati...n_cleanup.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"RonF" <RonF@.discussions.microsoft.com> wrote in message
news:E22685FE-F085-4B32-AF26-F2C5814FDD5C@.microsoft.com...
> I previously had a publication set up on my database. I've dropped it but
> still have a number of system tables in my database of the form
> conflict_dbnamePub_tablename. Is there a way I can delete these
> --
> Thanks
> RonF
|||Thanks for the assistance!
Ron
"Dejan Sarka" wrote:

> Take a look at this article:
> http://www.mssqlserver.com/replicati...n_cleanup.asp.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "RonF" <RonF@.discussions.microsoft.com> wrote in message
> news:E22685FE-F085-4B32-AF26-F2C5814FDD5C@.microsoft.com...
>
>

deleting a publication

Hi,
I removed a publication using the enterprise manager, and it has gone from
the publications views, but i can make the db ofline, because it reports it
is still published.
What can i do to tidy this up?
Thanks
Duncan
Duncan,
try
USE master
EXEC sp_dboption 'your db name', 'published', 'FALSE'
EXEC sp_dboption 'your db name', 'merge publish', 'FALSE'
HTH,
Paul Ibison

Deleted Subscriptiption on Subscriber

Dear Friends
I have deleted the Publication from the publisher but
still the Subscription from the subscriber are not
deleted due to same i am getting error while creating new
Publication as the table can not be droped as the same
are being used by another publication.
Kindly suggest how i can delete the same so that my
replication will start working from the subscriber.
Your earlier reply would be a great help.
Best regards
Sharad
Sharad,
Running sp_removedbreplication can be used to remove all traces of
replication in the subscriber database, but obviously must only be done if
this database is not also configured as a publisher. For a more granular
approach, there is a stored procedure to do this called sp_MSunmarkreplinfo
which takes a tablename as a parameter (alternatively, setting replinfo to 0
in sysobjects for the particular table should do it).
HTH,
Paul Ibison

Delete Snapshot Replication

When I try to drop the publication in this order:

exec sp_dropsubscription @.publication = @.publicName, @.subscriber = @.servName, @.article = N'all'

exec sp_droppublication @.publication = @.publicName

I am getting the following error after dropping the publication

Msg 16943, Level 16, State 4, Procedure sp_MSrepl_changesubstatus, Line 1271

Could not complete cursor operation because the table schema changed after the cursor was declared.

Msg 16943, Level 16, State 4, Procedure sp_MSrepl_changesubstatus, Line 1271

Could not complete cursor operation because the table schema changed after the cursor was declared.

Could anyone give me a suggestion about how to avoid this error?

Thanks!

Which if the above two stored procs is raising the error, and what version of sql server are you running?|||

The error is raised when trying to delete the publication with:

exec sp_droppublication @.publication = @.publicName

I am working with SQL Server 2005, service pack 1.

I hope with this information you can help me, or else please let me know.

Thanks!

|||this is a dumb suggestion, rather a shot in the dark, but can you stop/start the sql server service, and then try it again? Something about transactions/cursors that may be stuck/lingering somewhere?|||

I tried that already. I finally got to work the deleting of the publication, but the error remains as reported earlier. I am thinking this is a 'MS bug' that has not been totally fixed since SQL Server 7... pretty sad, huh? If still anyone has any idea what to do, please let me know.

Thanks!

|||

If you can provide a full repro of the problem, it would help us tremendously. If you can reproduce it from scratch, please post the TSQL code, otherwise this issue has been intermitent throughout the years, nothing solid for us to fix.

Other questions - are you wrapping this in a transaction? What's the db compat level? How many subscriptions are there?

Delete Snapshot Replication

When I try to drop the publication in this order:

execsp_dropsubscription @.publication = @.publicName, @.subscriber = @.servName, @.article = N'all'

exec sp_droppublication @.publication = @.publicName

I am getting the following error after dropping the publication

Msg 16943, Level 16, State 4, Procedure sp_MSrepl_changesubstatus, Line 1271

Could not complete cursor operation because the table schema changed after the cursor was declared.

Msg 16943, Level 16, State 4, Procedure sp_MSrepl_changesubstatus, Line 1271

Could not complete cursor operation because the table schema changed after the cursor was declared.

Could anyone give me a suggestion about how to avoid this error?

Thanks!

Which if the above two stored procs is raising the error, and what version of sql server are you running?|||

The error is raised when trying to delete the publication with:

exec sp_droppublication @.publication = @.publicName

I am working with SQL Server 2005, service pack 1.

I hope with this information you can help me, or else please let me know.

Thanks!

|||this is a dumb suggestion, rather a shot in the dark, but can you stop/start the sql server service, and then try it again? Something about transactions/cursors that may be stuck/lingering somewhere?|||

I tried that already. I finally got to work the deleting of the publication, but the error remains as reported earlier. I am thinking this is a 'MS bug' that has not been totally fixed since SQL Server 7... pretty sad, huh? If still anyone has any idea what to do, please let me know.

Thanks!

|||

If you can provide a full repro of the problem, it would help us tremendously. If you can reproduce it from scratch, please post the TSQL code, otherwise this issue has been intermitent throughout the years, nothing solid for us to fix.

Other questions - are you wrapping this in a transaction? What's the db compat level? How many subscriptions are there?

Delete records from subscriber only

I have on distributed enviornment in which data is collected by remote clients and send to the central publisher using merge publication and filters. I need to purge old data from the subsriber and not in the server. How do I do this?
Thanks
Anand,
what if some of the old data is modified on the publisher in future, or
perhaps this is not possible? If it is then you'll have a problem. You could
leave all the data as it is on the subscriber and use views to effectively
remove it for the front-end application. If you need the name to be the same
on the subscriber as the publisher, you could replicate to a different
tablename on the subscriber and use a view name equal to the tablename on
the publisher. Of course this would require reinitializing though. Finally,
you could drop the subscription, add a filter then reinitialize.
HTH,
Paul Ibison
|||The data at the server won't change for those records, if it did and it is replicated to the subscriber occasionaly it won't be a problem. I was trying to find out whether there is a way not to replicate deletes in merge publication (Easily) without dropi
ing the subscription.
Thanks for input
Anand
|||Anand,
there is another solution - you could modify the merge replication triggers
so as to not register your deletes in MSmerge_tombstone, although this would
lead to an unsupported solution.
HTH,
Paul Ibison
|||the cleanest way to do this is using transactional replication with custom
stored procedures. Do you require tranasactions that require on the
subscriber to be replicated to the publisher?
"Anand" <anonymous@.discussions.microsoft.com> wrote in message
news:4FB33D2F-1310-4125-86A0-21EDB56E6388@.microsoft.com...
> I have on distributed enviornment in which data is collected by remote
clients and send to the central publisher using merge publication and
filters. I need to purge old data from the subsriber and not in the server.
How do I do this?
> Thanks
|||Yes. I have already setup a merge replication with 30 odd disconnected clients replicating to a central server. Most of the transactions actually happen at the clients. It is not possible for me change the replication model now. Is there a way to script a
nd tell the clients not to replicate deletes to the publisher.
Anand
|||Can we use alter table to disable all triggers on a table and delete the records and enable triggers. Will this work?
Thanks
Anand
|||Well I tried disabling the triggers, but you can not alter the tables which are replicated. I stoped the SQL server agent and deleted the msmerge_tombstone records after the delete on the subscriber and restarted the SQL server agent. It seems to work the
way I want it. Any caveats?
Thanks
Anand