Cannot drop table because it is being used for replication

Last Post 01 Jun 2004 04:29 AM by madhouse92. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
madhouse92
New Member
New Member

--
01 Jun 2004 04:00 AM
ok, probably a daft question this, but I'll ask anyway......

I've the following running on my SQL Server:

1) A stored procedure is called to executed a DTS Package.
2) The DTS Package drops some tables, recreates them and then imports in the latest data from our AS/400.
3) A scheduled Merge Replication runs to merge the tables from the SQ Server and the versions of the tables running on my local laptop.

However, when the stored procedure is called to execute the DTS Package I get the following error in the DTS Package Logs:


Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:Cannot drop the table 'emtdaily.dbo.EMTCONTROL' because it is being used for replication.
Step Error code: 80040E14
Step Error Help File:
Step Error Help Context ID:0

Now I know the message says it all - "Cannot drop the table 'emtdaily.dbo.EMTCONTROL' because it is being used for replication" - but is there anyway round this? Or is my only solution to simply delete all records from the tables, rather than deleting the table itself, and then import the data from the AS/400?
Mattbing
New Member
New Member

--
01 Jun 2004 04:10 AM
I think you will just want to delete all the data instead of dropping the entire table. I guess if you wanted to go thru the trouble you could script out your replication and have your SP disable replication on the table, and then re-create it when the table is created.

Matt
madhouse92
New Member
New Member

--
01 Jun 2004 04:29 AM
Thanks for the reply.

Just changed my DTS Package so that it only deletes the data before importing the new stuff and it works fine. Maybe I'll look at your suggestion of disabling replication on the table at a latter date.
You are not authorized to post a reply.

Acceptable Use Policy