HELP! Non-Replicated DB thinks its a Publisher

I hosed myself, please help me somebody.

We have a development server (SQL7) where the database is a restored copy of
production (where we use replication, but not in the development
environment).

While we rebuild the server recently (so we could move off of NT onto 2000),
everything looked to be working fine.

But I recently, messed up and did a massive update filling the transaction
log of the development server. Now I can't update data in development.

I tried to do my usual:

BACKUP LOG LAMS WITH Truncate_only

only to get a message:

The log was not truncated because records at the beginning of the log are
pending replication. Ensure the Log Reader Agent is running or use
sp_repldone to mark transactions as distributed.

Did a little research into BOL and tried to call the following two procs:

exec sp_repldone
@.xactid = null ,
@.xact_seqno = null ,
@.reset = 1

exec sp_repltrans

Both of which gave me an error of :
The database is not published.

Which it isn't as we don't run replication in the development environment.

I stopped and restarted the SQL Service (as that has cleared issues in the
past), but didn't reboot the server.

How can I make the database not think It's replicated (or that its caught
up?).

I was thinking, would I be even be able to do another restore of a more
recent copy of Production?

Sincerely ...
Depressed in DelawareNever Mind, I restored development from a more recent copy of production.

Is there a command to fix a SQL 7 DB when it gets confused about
replication?

"David Rawheiser" <rawhide58@.hotmail.com> wrote in message
news:DD3Bd.1194718$Gx4.69619@.bgtnsc04-news.ops.worldnet.att.net...
>I hosed myself, please help me somebody.
> We have a development server (SQL7) where the database is a restored copy
> of production (where we use replication, but not in the development
> environment).
> While we rebuild the server recently (so we could move off of NT onto
> 2000), everything looked to be working fine.
> But I recently, messed up and did a massive update filling the transaction
> log of the development server. Now I can't update data in development.
> I tried to do my usual:
> BACKUP LOG LAMS WITH Truncate_only
> only to get a message:
> The log was not truncated because records at the beginning of the log are
> pending replication. Ensure the Log Reader Agent is running or use
> sp_repldone to mark transactions as distributed.
> Did a little research into BOL and tried to call the following two procs:
> exec sp_repldone
> @.xactid = null ,
> @.xact_seqno = null ,
> @.reset = 1
> exec sp_repltrans
> Both of which gave me an error of :
> The database is not published.
> Which it isn't as we don't run replication in the development environment.
> I stopped and restarted the SQL Service (as that has cleared issues in the
> past), but didn't reboot the server.
> How can I make the database not think It's replicated (or that its caught
> up?).
> I was thinking, would I be even be able to do another restore of a more
> recent copy of Production?
> Sincerely ...
> Depressed in Delaware

Help! cannot remove some of the replication remnants

Hope somebody can help because this is rather fustrating. I have recently
been doing some replication configurations between two servers. I started to
get a warning symbol on the replication monitor so I decided to uninstall
replication and start again. Unfortunately when I reinstall replication the
warning symbol returns.
How can I remove ALL history of replication on this server (I have tried
dropping distribution also). Although functinally replication still works
(regardless of icon) it is extremely annoying and does not relate to any
publications in existance.
Also I cannot remember any db names or publication names of those that were
published because they were dropped during cleanup.
Thanks in advance,
Mark
Mark,
if you've disabled publishing, this should take case of most things.
sp_removedbreplication can also be useful on individual databases.
Resrarting the services should remove error info on the replication monitor,
while sp_MSload_replication_status achieves the same but is less drastic.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||These commands may help:
sp_removedbreplication 'pubs' --replicated database on publisher
sp_dropdistpublisher @.publisher = '<sql server name>'
--, @.no_checks = no_checks
sp_dropdistributiondb distribution --name of dist db
Patrick
"Mark Broadbent" wrote:

> Hope somebody can help because this is rather fustrating. I have recently
> been doing some replication configurations between two servers. I started to
> get a warning symbol on the replication monitor so I decided to uninstall
> replication and start again. Unfortunately when I reinstall replication the
> warning symbol returns.
> How can I remove ALL history of replication on this server (I have tried
> dropping distribution also). Although functinally replication still works
> (regardless of icon) it is extremely annoying and does not relate to any
> publications in existance.
> Also I cannot remember any db names or publication names of those that were
> published because they were dropped during cleanup.
> Thanks in advance,
> Mark
|||disable replication. Then go through the databases looking for tables called
mssubscriptions or mspublications - the names elude me right now and I don't
have SQL installed on this machine.
Delete the content of these rows. Make sure mind you that you don't have any
active subscriptions on this database - other wise they will get hosed;)
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
"Mark Broadbent" <Mark Broadbent@.discussions.microsoft.com> wrote in message
news:7CD45485-9352-449C-9170-618823D3856D@.microsoft.com...
> Hope somebody can help because this is rather fustrating. I have recently
> been doing some replication configurations between two servers. I started
> to
> get a warning symbol on the replication monitor so I decided to uninstall
> replication and start again. Unfortunately when I reinstall replication
> the
> warning symbol returns.
> How can I remove ALL history of replication on this server (I have tried
> dropping distribution also). Although functinally replication still works
> (regardless of icon) it is extremely annoying and does not relate to any
> publications in existance.
> Also I cannot remember any db names or publication names of those that
> were
> published because they were dropped during cleanup.
> Thanks in advance,
> Mark
|||Thanks Paul that stored proc did the trick. You have saved me a lot of grief,
thanks a million!
Thanks also to everyone else for their contributions.
"Paul Ibison" wrote:

> Mark,
> if you've disabled publishing, this should take case of most things.
> sp_removedbreplication can also be useful on individual databases.
> Resrarting the services should remove error info on the replication monitor,
> while sp_MSload_replication_status achieves the same but is less drastic.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Help with update trigger

Hi all,
I know squat about triggers so was hoping somebody could point me in the
right direction. I wanted to copy an email address field from a salesman
table to a note field in a customer table. Seems easy enough for a one time
update. But I would like to add a trigger to auto-update the customer table
anytime an email address changes in the saleman table or a new salesman
record is added.

Here's my update script (this copies the salesman email address to each of
his customers)
UPDATE CUSTOMERS
SET NOTE_5 = SALESMAN.EMAIL_ADDR
FROM CUSTOMERS INNER JOIN
SALESMAN ON CUSTOMERS.SLSPSN_NO = SALESMAN.SLSPSN_NO

How can I turn this into a trigger for automatic updates?

Thanks for any help.rdraider (rdraider@.sbcglobal.net) writes:
> I know squat about triggers so was hoping somebody could point me in the
> right direction. I wanted to copy an email address field from a
> salesman table to a note field in a customer table. Seems easy enough
> for a one time update. But I would like to add a trigger to auto-update
> the customer table anytime an email address changes in the saleman table
> or a new salesman record is added.
> Here's my update script (this copies the salesman email address to each of
> his customers)
> UPDATE CUSTOMERS
> SET NOTE_5 = SALESMAN.EMAIL_ADDR
> FROM CUSTOMERS INNER JOIN
> SALESMAN ON CUSTOMERS.SLSPSN_NO = SALESMAN.SLSPSN_NO
>
> How can I turn this into a trigger for automatic updates?

CREATE TRIGGER salesman_tri FOR INSERT, UPDATE ON SALESMAN AS
UPDATE CUSTOMERS
SET NOTE_5 = i.EMAIL_ADDR
FROM CUSTOMERS c
JOIN inserted c.SLSPSN_NO = i.SLSPSN_NO

"inserted" is a virtual table that holds the row that were inserted, or
the after-image of the updated rows.

"deleted" is a sister table that holds deleted rows, or the before-image
of the updated rows.

Note that triggers fires once per statement, so these tables can include
many rows.

You can only access these tables directly in a trigger.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx