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 data after replication.

I am looking for an opinion on the best way to delete all data from a table after it has been replicated.

The simple use case would be:

Data is replicated from production server to archive server. Data is deleted from production server

Thanks in advance...

If this is a one time or thing you could disable the delete trigger that replication uses to record the deletes while you remove the information then enable it when you are done.

Martin

|||

I assume this is transactional replication. if you want to delete data from the production server and not have it replicated to the subscriber, for example the subscriber is an archival, then one option is to do the deletes in a stored procedure. By replicating the execution of a proc, only the proc execution and parameters will be replicated. THe trick here is to make sure the proc at the subscriber is a dummy proc. You can create this dummy proc in a TSQL file and add this file to the post_snapshot parameter for sp_addpublication.

|||

Greg,

In this instance though wouldn't the table you're deleting from be one of the published articles in the replication?

So any changes to the data in the table would be propogated to the subscriber no matter how the data was deleted.

Is there a strong case for having replication used here? Would it not be possible to have some form of SSIS/DTS package to move the data across?

|||

if you replicate the execution of a stored procedure, then only the proc and parameters are replicated, not the underlying commands. THis is common for scenarios that want the subscriber to be an archival and allowing you to do massive deletes/cleanup at the publisher. It's also common for scenarios that do massive batch changes.

http://msdn2.microsoft.com/en-us/library/ms152754.aspx

Deleting data after replication.

I am looking for an opinion on the best way to delete all data from a table after it has been replicated.

The simple use case would be:

Data is replicated from production server to archive server. Data is deleted from production server

Thanks in advance...

If this is a one time or thing you could disable the delete trigger that replication uses to record the deletes while you remove the information then enable it when you are done.

Martin

|||

I assume this is transactional replication. if you want to delete data from the production server and not have it replicated to the subscriber, for example the subscriber is an archival, then one option is to do the deletes in a stored procedure. By replicating the execution of a proc, only the proc execution and parameters will be replicated. THe trick here is to make sure the proc at the subscriber is a dummy proc. You can create this dummy proc in a TSQL file and add this file to the post_snapshot parameter for sp_addpublication.

|||

Greg,

In this instance though wouldn't the table you're deleting from be one of the published articles in the replication?

So any changes to the data in the table would be propogated to the subscriber no matter how the data was deleted.

Is there a strong case for having replication used here? Would it not be possible to have some form of SSIS/DTS package to move the data across?

|||

if you replicate the execution of a stored procedure, then only the proc and parameters are replicated, not the underlying commands. THis is common for scenarios that want the subscriber to be an archival and allowing you to do massive deletes/cleanup at the publisher. It's also common for scenarios that do massive batch changes.

http://msdn2.microsoft.com/en-us/library/ms152754.aspx

Deleting data after replication.

I am looking for an opinion on the best way to delete all data from a table after it has been replicated.

The simple use case would be:

Data is replicated from production server to archive server. Data is deleted from production server

Thanks in advance...

If this is a one time or thing you could disable the delete trigger that replication uses to record the deletes while you remove the information then enable it when you are done.

Martin

|||

I assume this is transactional replication. if you want to delete data from the production server and not have it replicated to the subscriber, for example the subscriber is an archival, then one option is to do the deletes in a stored procedure. By replicating the execution of a proc, only the proc execution and parameters will be replicated. THe trick here is to make sure the proc at the subscriber is a dummy proc. You can create this dummy proc in a TSQL file and add this file to the post_snapshot parameter for sp_addpublication.

|||

Greg,

In this instance though wouldn't the table you're deleting from be one of the published articles in the replication?

So any changes to the data in the table would be propogated to the subscriber no matter how the data was deleted.

Is there a strong case for having replication used here? Would it not be possible to have some form of SSIS/DTS package to move the data across?

|||

if you replicate the execution of a stored procedure, then only the proc and parameters are replicated, not the underlying commands. THis is common for scenarios that want the subscriber to be an archival and allowing you to do massive deletes/cleanup at the publisher. It's also common for scenarios that do massive batch changes.

http://msdn2.microsoft.com/en-us/library/ms152754.aspx

sql

Deleting data after replication.

I am looking for an opinion on the best way to delete all data from a table after it has been replicated.

The simple use case would be:

Data is replicated from production server to archive server. Data is deleted from production server

Thanks in advance...

If this is a one time or thing you could disable the delete trigger that replication uses to record the deletes while you remove the information then enable it when you are done.

Martin

|||

I assume this is transactional replication. if you want to delete data from the production server and not have it replicated to the subscriber, for example the subscriber is an archival, then one option is to do the deletes in a stored procedure. By replicating the execution of a proc, only the proc execution and parameters will be replicated. THe trick here is to make sure the proc at the subscriber is a dummy proc. You can create this dummy proc in a TSQL file and add this file to the post_snapshot parameter for sp_addpublication.

|||

Greg,

In this instance though wouldn't the table you're deleting from be one of the published articles in the replication?

So any changes to the data in the table would be propogated to the subscriber no matter how the data was deleted.

Is there a strong case for having replication used here? Would it not be possible to have some form of SSIS/DTS package to move the data across?

|||

if you replicate the execution of a stored procedure, then only the proc and parameters are replicated, not the underlying commands. THis is common for scenarios that want the subscriber to be an archival and allowing you to do massive deletes/cleanup at the publisher. It's also common for scenarios that do massive batch changes.

http://msdn2.microsoft.com/en-us/library/ms152754.aspx

Deleting a replicated database..

I am new to SQL server...In case you couldn't tell.
Under Enterprise Manager, SQLGROUP, Server, Databases, I
show three databases that are "suspect" that I need to
delete. The databases were manually removed
(Permanentley) from the SQL directory in explorer. When I
try to delete the database from enterprise manager, I get
the error message, "Error 3724: Cannot drop the
database "db" because it is being used for replication. I
can find no place to cancel or remove the replication.
Please help.
Thanks,
Scott
can you issue a sp_resetstatus 'ProblemDatabaseName' to mark these databases
as 'normal' and then try to delete them?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Scott" <scott@.jabrousa.com> wrote in message
news:240001c47014$a9f83790$a301280a@.phx.gbl...
> I am new to SQL server...In case you couldn't tell.
> Under Enterprise Manager, SQLGROUP, Server, Databases, I
> show three databases that are "suspect" that I need to
> delete. The databases were manually removed
> (Permanentley) from the SQL directory in explorer. When I
> try to delete the database from enterprise manager, I get
> the error message, "Error 3724: Cannot drop the
> database "db" because it is being used for replication. I
> can find no place to cancel or remove the replication.
> Please help.
> Thanks,
> Scott
>

DELETE Procedure. How to return a value?

Hello,

I created a DELETE Stored Procedure in SQL 2005.
When calling this procedure from my server code (VB.NET in my case) I
need to know if the record was deleted or not.

How should I do this?

Should I make the procedure to return True or False? If yes, how can I
do this?

My Stored Procedure is as follows (I think it is ok):

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Content_DeleteContent]

-- Define the procedure parameters
@.ContentName NVARCHAR(100),
@.ContentCulture NVARCHAR(5)

AS

-- Prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;

-- Declare and define ContentId
DECLARE @.ContentId UNIQUEIDENTIFIER;
SELECT @.ContentId = ContentId FROM dbo.Content WHERE ContentName =
@.ContentName

-- Check if ContentId is Not Null
IF @.ContentId IS NOT NULL
BEGIN

-- Check if ContentId is Null
IF @.ContentCulture IS NULL
BEGIN

-- Delete all localized contents from dbo.ContentLocalized
DELETE
FROM dbo.ContentLocalized
WHERE ContentId = @.ContentId

-- Delete content from dbo.Content
DELETE
FROM dbo.Content
WHERE ContentName = @.ContentName;

END
ELSE

-- Delete localized content from dbo.ContentLocalized
DELETE
FROM dbo.ContentLocalized
WHERE (ContentID = @.ContentID AND ContentCulture = @.ContentCulture)

END

GO

Thanks,
Miguel

hi miguel.

below is the sp i modified. i've added an output parameter, which will be null if record(s) are deleted properly. so you can check in your code using output parameter.

SET ANSI_NULLSON
GO
SET QUOTED_IDENTIFIERON
GO

ALTER PROCEDURE [dbo].[Content_DeleteContent]

-- Define the procedure parameters
@.ContentNameNVARCHAR(100),
@.ContentCultureNVARCHAR(5) ,
@.nFlag tinyint output

AS

-- Prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;

-- Declare and define ContentId
DECLARE @.ContentIdUNIQUEIDENTIFIER;
SELECT @.ContentId = ContentIdFROM dbo.ContentWHERE ContentName =
@.ContentName

-- Check if ContentId is Not Null
IF @.ContentIdISNOT NULL
BEGIN

-- Check if ContentId is Null
IF @.ContentCultureISNULL
BEGIN

-- Delete all localized contents from dbo.ContentLocalized
DELETE
FROM dbo.ContentLocalized
WHERE ContentId = @.ContentId

-- Delete content from dbo.Content
DELETE
FROM dbo.Content
WHERE ContentName = @.ContentName;

Select @.nFlag = ContentIDfrom dbo.ContentLocalized
WHERE ContentId = @.ContentId
if @.nFlagisnull
Begin
Select @.nFlag = 1FROM dbo.Content
WHERE ContentName = @.ContentName;
End

END
ELSE

BEGIN
-- Delete localized content from dbo.ContentLocalized
DELETE
FROM dbo.ContentLocalized
WHERE (ContentID = @.ContentID AND ContentCulture = @.ContentCulture)

Select @.nFlage = ContentIDFROM dbo.ContentLocalized
WHERE (ContentID = @.ContentID AND ContentCulture = @.ContentCulture)

END

END

hope it helps.

regards,

satish.|||Rather than do SELECT's after the DELETE's checking for @.@.ROWCOUNT works faster since the number of records deleted is already stored in the function. You could just check if @.@.ROWCOUNT >0 and set your flag to 1 or 0 appropriately.|||

Hi Dinakar

miguel has usedSET NOCOUNT ON.

thanks,

satish.

|||

Hello,

I defined "Set NoCount On" because this is done by default in SQL Server 2005 Management Studio and because this is how I see in most code examples.

I am just starting with SQL. Should I change it?

Could somebody give me some info on this?

Thanks,

Miguel

|||

hi miguel,

sorry i m not aware of default in sql 2k5

set nocount on --in case you update/insert/delete record on a table it wont return number of affected records

set nocount off --in case you update/insert/delete record on a table it will return number of affected records

hope it clarifies

now if you set it to off in your sp then after delete you can check

if @.@.ROWCOUNT > 0

begin

end

@.@.ROWCOUNT contains no.of records affected due to preceeding operation.

regards,

satish.

|||

SET NOCOUNT will only suppress messages from being printed in the Messages tab which are returned to the calling application. It does not have any effect on @.@.ROWCOUNT. You can still do your T-SQL Queries and have the results of rows affected in @.@.ROWCOUNT, get the value into variable and return it via OUTPUT parameters. Having the SET NOCOUNT ON is a good thing, generally. It prevents unnecessary messages from being passed across to the application.

Satish,

You are probably checking the Messages tab by running a T-SQL statement between SET NOCOUNT commands.

Try this:

SET NOCOUNT ON

SELECT TOP 10 * FROM anyTable

SELECT @.@.ROWCOUNT as RowsAffected

SET NOCOUNT OFF

|||

errrrr silly of me.

thanks dinakar you removed my misunderstanding about @.@.ROWCOUNT and SET NOCOUNT

cheers,

satish.