Deleting replication, leaves data in distribution database

Hi,

>From a publisher I replicate 3 databases to a distributor/subscriber
machine. On all of them i have MS SQL 2005.
When i delete one of the replications by running
publisher => sp_dropsubscription
subscriber/distributor=>sp_removedbreplication
publisher => sp_removedbreplication
subscriber/distributor =>sp_subscription_cleanup
On the publisher and subscriptions databases all replication things
are removed.
However when looking with the replication monitor the deleted
replication is visible with a big red cross. Also the jobs are still
in the job list of the distributor!
When removing everything by dropping the distribution database all is
cleared. I know that in SQL 2000 the distributor would also be
cleared. Has this changed?
Is this a bug in SQL2005 or am i forgotting something (i have searched
a lot already, but cannot find anything)?
Hopefully someone can help me?
Marcel
I'd recommend using sp_droppublication which'll clean up the jobs as well.
sp_removedbreplication is something I run only occasionally to clean up any
remaining orphaned objects.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||On 1 feb, 10:28, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I'd recommend using sp_droppublication which'll clean up the jobs as well.
> sp_removedbreplication is something I run only occasionally to clean up any
> remaining orphaned objects.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com.
Thanks Paul,
This looks much better! I finalized with
EXEC sp_replicationdboption
@.dbname = @.publicationDB,
@.optname = N'publish',
@.value = N'false';
This should remove the publication objects...
Marcel

DeleteSubscription leaves job in SQL Server

Has anyone else noticed that if you create a timed subscription using

the web service and then delete it using the DeleteSubscription method,

there is a job (id'ed by a guid) left in SQL Server Jobs?

Consequently we now have hundres of "orphaned" jobs in our database :-(

A bit more investigation appears to reveal this:

I use data driven subscriptions and create them using CreateDataDrivenSubscription. Therefore I retrieve them using GetDataDrivenSubscriptionProperties. The matchData returned does not contain a scheduleId. Is that correct? Well calling ListSchedules returns an empty row, indicating that the schedules created are not shared, so cannot be deleted using DeleteSchedule anyway. However, and this is where the bug appears to be, the schedules are in the ReportSchedule table and the jobs are in SQL Server. Calling DeleteSubscription does not delete the SQL Server Job, nor does it delete the row from ReportSchedule. Is this a bug? Is there a fix or do I have to delete the row myself and delete the job from SQL Server using DMO or some such hack.

|||

The SQL Agent jobs should be deleted when the associated subscription is deleted. There appears to be a problem with your system.

Can you take a peek at Event Viewer and SQLAgent log files and see if there is any incriminating evidence?

Deleteing Duplicates

Hi,
Does anyone have a useful way of deleting duplicates so that it leaves 1 in
the table and removes the other. Currently I use a #temp table but was just
wondering if there is something slicker.
Thanks
Steve LloydDepends, assuming you have a primary key:
DELETE FROM YourTable
WHERE EXISTS
(SELECT *
FROM YourTable AS T
WHERE col1 = YourTable.col1
AND col2 = YourTable.col2
AND ... etc
AND key_col < YourTable.key_col)
If you don't have a key at all then SELECT DISTINCT into a new table
and add the key... and don't create tables without keys in future!
David Portas
SQL Server MVP
--|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
How to Identify and Delete Duplicate SQL Server Records
http://www.sql-server-performance.c..._duplicates.asp
AMB
"Steve Lloyd" wrote:

> Hi,
> Does anyone have a useful way of deleting duplicates so that it leaves 1 i
n
> the table and removes the other. Currently I use a #temp table but was ju
st
> wondering if there is something slicker.
> Thanks
> Steve Lloyd
>
>