Can't Drop table in Replication

Dear friends

I restore one database in two database servers which is running on SQL server 2000.I replicated these two through snapshot relication.Snapshot agent is creating snapshot.But when I am starting to synchronize it's telling can't drop table because that table is in replication. Backup I taken from a replicated database.so it's having rowguid both the servers.Another thing the table which it is telling not able to drop it's having primary key.please tell me what may be the problem.In replication why it is going to drop table it's only what to transport data na

Filson

The database that's restored at the subscriber probably still has replication bits set. You can clean up replication at the subscriber database by calling proc sp_removedbreplication.|||

Greg Y wrote:

The database that's restored at the subscriber probably still has replication bits set. You can clean up replication at the subscriber database by calling proc sp_removedbreplication.

I done like that.But I got a problem .I am using backup from replicated database .This backup only i restored on my publisher & subscriber.When I am subscribing three system stored procedure should be generate na for Del,Ins.Upd operations .This is not happening.I tried to delete this system generated procedures in the restored database .But it's not allowing.Then I applied replication Pubs database which is coming along with SQL Server .Then what I seen I published all Tables(10).But Stored procedure only for 5 tables is generated .in these tables whatever changes i am making it's affecting through Replication,Example publisher table in pubs database is not getting any stored procedure after subscription.So it's not getting any change through replication.Then I am getting error message'procedure Sp_MsIns_Publisher not found'.So please tell me what to do remove all old Stored procedures for replication in restored database and how to create the stored procedures for all the tables published

Thanks in Advance

Filson

|||

You need to restore the database at the subscriber and remove all replication components. When using the wizard to set up the subscription, specify the option that the subscriber has the data. if you're doing it via TSQL, then specify 'nosync' for paramter @.sync_type in sp_addsubscription. After the first sync, you can then run sp_scriptpublicationcustomprocs at the subscriber to create the necessary procs for the distribution agent.

|||

Greg Y wrote:

You need to restore the database at the subscriber and remove all replication components. When using the wizard to set up the subscription, specify the option that the subscriber has the data. if you're doing it via TSQL, then specify 'nosync' for paramter @.sync_type in sp_addsubscription. After the first sync, you can then run sp_scriptpublicationcustomprocs at the subscriber to create the necessary procs for the distribution agent.

Greg thanks for suggestion.Previously itself I tried these ways to remove replication components.But still System stored procedures for table Ins,Del,Upd is not dropping.I want a specific way to drop it out.I think i mentioned this in earlier post.So kindly suggest me a better way for that

Filson

|||Sorry, it's not clear to me what the problem is. Are you trying to drop or create the sp_MSins/upd/del stored procedures? And where - at the publisher or subscriber? I'm not too familiar with SQL 2000, maybe you have to manually delete these stored procedures.|||

Greg Y wrote:

Sorry, it's not clear to me what the problem is. Are you trying to drop or create the sp_MSins/upd/del stored procedures? And where - at the publisher or subscriber? I'm not too familiar with SQL 2000, maybe you have to manually delete these stored procedures.

I am describing my problem below .I taken a backup of replicated database.That I restored on another server.I made that one as a publisher.Another instance I made as a subscriber.I restored same database on this subscriber.Then I tried for transactional replication.While synchronizing I got error on my subscriber 'Sp_MsIns_AgentCode is not found'.This I got while Inserting records to AgentCode Table in publisher.Then I come to know for each table article published in Transactional replication will have three system generated stored procedures 1)insertion 2)updation 3)Deletion.This is not generating when i subscribing to my publisher..Here when I am pulling the subscription I am specifying 'No Shema & data transfer'.So I am not able to transport this System generated Procedures.If I am selecting 'Schema & data Tansfer'.It won't able to initialize in subscriber due to Foreign Key criterias. after completing Subscription through Wizard,Can I trnsfer UPD,INS,DEL Procedure Schema through any Sp_procedure call? Give me some better way

Filson

|||

I mentioned above to run stored procedure sp_scriptpublicationcustomprocs at the subscriber database after applying the snapshot, did you do that? That proc is supposed to generate the missing sp_MSins/upd/del procs that the distribution agent is trying to execute.

cant drop rowguid or msrepl_tran_version

sql2k sp3
Ive got these columns on a previously replicated table and
I cant get rid of them. I went so far as to disable
Publisheing from the box and still no luck. I also removed
the default values and the indexes from them. I also tried
sp_repldropcolumn. Any ideas?
TIA, ChrisR
Chris,
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. Finally, 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.
HTH,
Paul Ibison
|||I appreciate the help. I had already run the
sp_MSunmarkreplinfo. I have dropped the Subscription db's.
And I ran sp_removedbreplication on the Publisher. I still
cant drop them. Any other ideas?

>--Original Message--
>Chris,
>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.
Finally, 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.
>HTH,
>Paul Ibison
>
>.
>

Can't Drop Column or Shrink tran log

I am having problem getting the transaction log of a db
that is being replicated to shrink even after performing
a backup. There is about 500 MB of data in the database
and the trans log is almost 4GB. Dbcc checkdb returned no
error but dbcc opentran returned the following message:
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (305:22434:1)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
Also on this particular db, I can't add/drop column using
EM on the published db but can add column using
sp_repladdcolumn. When I tried to drop a column using
sp_repldropcolumn, I get the following message: "ALTER
TABLE DROP COLUMN failed because 'FieldName' is currently
replicated."
The column that I can't drop is not a pk or part of a pk
and there are no constraints. There is a relationship
between this table and another table on another column.
Thanks
Emma
Emma,
I remember this thread from a while back!
It seems you have some transactions (1 or more) in the log which have not
been read by a log reader. Run your log reader agent for all publications in
this database, then turn them off and make sure no-one else can modify the
data. Run sp_replcmds to see if this returns anything else. If it does then
consider running sp_repldone. This will mark the log as having been read
completely, and you'll then be able to back it up and shrink it.
First, have a look in BOL for the 2 sps above to familiarize yourself with
them. Also, make sure you have a backup before attempting this fix.
HTH,
Paul Ibison
|||Paul,
According to BOL, sp_replcmds and sp_repldone are for
transactional replication. I am running a merge
replication. Does this make any difference?
Thanks
Emma

>--Original Message--
>Emma,
>I remember this thread from a while back!
>It seems you have some transactions (1 or more) in the
log which have not
>been read by a log reader. Run your log reader agent for
all publications in
>this database, then turn them off and make sure no-one
else can modify the
>data. Run sp_replcmds to see if this returns anything
else. If it does then
>consider running sp_repldone. This will mark the log as
having been read
>completely, and you'll then be able to back it up and
shrink it.
>First, have a look in BOL for the 2 sps above to
familiarize yourself with
>them. Also, make sure you have a backup before
attempting this fix.
>HTH,
>Paul Ibison
>
>.
>
|||Emma,
Yes! I assumed it was transactional, as the output of dbcc opentran looks
like that of transactional. Have you perhaps ever set up transactional
previously then disabled it? If so, please try running sp_repldone then see
if dbcc opentran reports anything different - hopefully no transactions will
be reported. Only do this if you don't have any current transactional
publications based on this database. As a last resort (backup your databases
before trying this), you could detach the database, delete the transaction
log, then reattach the database and a new empty log should be created for
you (I have had cause to do this previously and it worked).
HTH,
Paul Ibison
|||Paul,
Thanks for all your help. I have tried everything and
nothing is working. sp_repldone tells me that the
database is not published. I could not detach the
database because it is being replicated. I even stopped
the SQL services and deleted the log file, but this
generated an error when I restarted the database so I had
to restore the file. I disabled publication on the
database and tried to delete a column, and it tells me
that I can't delete because the table is being
replicated. Is my database corrupt? How can I get around
these problems? The two things I am trying to do is drop
a column from a published table and shrink my log file.
Any suggestions as to what I can do?
Thanks
Emma

>--Original Message--
>Emma,
>Yes! I assumed it was transactional, as the output of
dbcc opentran looks
>like that of transactional. Have you perhaps ever set up
transactional
>previously then disabled it? If so, please try running
sp_repldone then see
>if dbcc opentran reports anything different - hopefully
no transactions will
>be reported. Only do this if you don't have any current
transactional
>publications based on this database. As a last resort
(backup your databases
>before trying this), you could detach the database,
delete the transaction
>log, then reattach the database and a new empty log
should be created for
>you (I have had cause to do this previously and it
worked).
>HTH,
>Paul Ibison
>
>.
>
|||Emma,
try running
exec sp_dboption - to see the current settings
exec sp_dboption 'pubs','published',false
exec sp_dboption 'pubs','merge publish',false
This should allow you to detach the database and remove the log.
For the table, if it is still a problem, 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. Finally, running sp_removedbreplication can be used to remove
all traces of replication in the database, but obviously must only be done
if this database is not also configured as a publisher.
Regards,
Paul Ibison
|||Paul,
Thanks again for your help. I will try what you suggested
and hope for the best. The db is also configured as a
publisher and here is the result of sp_dboption.
ANSI null default
ANSI nulls
ANSI padding
ANSI warnings
arithabort
auto create statistics
auto update statistics
autoclose
autoshrink
concat null yields null
cursor close on commit
db chaining
dbo use only
default to local cursor
merge publish
numeric roundabort
offline
published
quoted identifier
read only
recursive triggers
select into/bulkcopy
single user
subscribed
torn page detection
trunc. log on chkpt.

>--Original Message--
>Emma,
>try running
>exec sp_dboption - to see the current settings
>exec sp_dboption 'pubs','published',false
>exec sp_dboption 'pubs','merge publish',false
>This should allow you to detach the database and remove
the log.
>For the table, if it is still a problem, 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. Finally, running sp_removedbreplication
can be used to remove
>all traces of replication in the database, but obviously
must only be done
>if this database is not also configured as a publisher.
>Regards,
>Paul Ibison
>
>.
>

Can't drop a table

I have a table that used to be in a merge replication. How ever it is not
being replicated any more, I can't drop it because it thinks it is been
replicated. How can I drop it?
Thanks a lot, Lina
If you don't replicate anything else from your database run the
sp_removedbreplication procedure. It'll remove all traces of replication.
Then you'll be able to drop the table.
If the first option is not available then you need to reset the replication
status bit in sysobjects for your table. Although you need to know that
manual updating of the sysobjects table is not recommend.
Yury
"Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
news:52C9D2D2-61D0-4D7B-9D5A-1950AFA1E8E5@.microsoft.com...
>I have a table that used to be in a merge replication. How ever it is not
> being replicated any more, I can't drop it because it thinks it is been
> replicated. How can I drop it?
> Thanks a lot, Lina
|||issue the following
EXEC sp_configure 'allow',1
go
reconfigure with override
go
use DataBaseName
go
update sysobjects set replinfo = 0 where name = 'TableName'
go
EXEC sp_configure 'allow',0
go
reconfigure with override
go
sp_MSunmarkreplinfo 'TableName'
go
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
news:52C9D2D2-61D0-4D7B-9D5A-1950AFA1E8E5@.microsoft.com...
> I have a table that used to be in a merge replication. How ever it is not
> being replicated any more, I can't drop it because it thinks it is been
> replicated. How can I drop it?
> Thanks a lot, Lina

Can't delete Replicated Database

I was just experimenting with something in replication and created a test
database called "ReplicationTest"
This is on a server that is currently a subscriber for other databases, but I
thought I'd also be able to set up a publication from "ReplicationTest" Db.
I tried to create a publication and got the error:
Error 14294 supply either @.Job_id or @.Job_name to identify the job.
What does that mean ?
So I said "to heck with it" (or something like that) and decided to just
delete ReplicationTest database since I got stuck & had real work to do and
no publication had been created. But now it won't let me delete
ReplicationTest database because it says it's being used for replication ....
.... So, what should I try ?
Before you can delete it.
Right click on the replication folder on your enterprise manager treeview,
click on the configure publishers, subscribers, distributions on the context
menu that pops up, go to the publication database tabs and make sure you
untick the boxes next to Replication Test.
Once that is done you can delete the db.
"Gabriel D via droptable.com" wrote:

> I was just experimenting with something in replication and created a test
> database called "ReplicationTest"
> This is on a server that is currently a subscriber for other databases, but I
> thought I'd also be able to set up a publication from "ReplicationTest" Db.
>
> I tried to create a publication and got the error:
> Error 14294 supply either @.Job_id or @.Job_name to identify the job.
> What does that mean ?
>
> So I said "to heck with it" (or something like that) and decided to just
> delete ReplicationTest database since I got stuck & had real work to do and
> no publication had been created. But now it won't let me delete
> ReplicationTest database because it says it's being used for replication ....
> .... So, what should I try ?
>