Very Computer

I started replication on a few of my SQL servers.  I ran
into a few issues and I turned replication off.

Is there a way to get rid of the replication fields that
was added when I turned on replication?

Thanks,
Joe

Hello Joe,

Try using sp_dropmergearticle for the DB from which the
replication fields need to be dropped, if you have already
dropped the subscriptions. If not try
sp_dropmergesubscription on subscriber DB and
sp_droppublication on publisher DB. This shall
automatically take care of the replication fields.

-Rajesh

Quote:>-----Original Message-----
>I started replication on a few of my SQL servers.  I ran
>into a few issues and I turned replication off.

>Is there a way to get rid of the replication fields that
>was added when I turned on replication?

>Thanks,
>Joe

>.

Since I disabled replication thru Enterprise manger, I
can't run any of the SP's you sugessted, since I have no
publications.

Can I drop dtproperties from the database and
MSreplication_options from master?
Then alter every table to remove the replication columns?

Would that mess anything up?

Thanks,
Joe

Hello Joe,

Dropping dtproperties from the database won't be a
problem, but can't say so for MSreplication_options from
master. (No idea)

Since you have already dropped the publication and
subscription, may be SQL Server did not complete some
clean up work. Try out sp_cleanupdbreplication. This would
clean up the sysmergesubscriptions and
sysmergepublications table left behind. This should solve
the issue.

SP that usually works for me is sp_removedbreplication -
for the database that needs to be cleaned up.

In case this does not work in your case, try out
sp_removesrvreplication. This is the last resort for all
the replication related issues. Note that this needs the
database to be in Single user mode. This shall remove all
the system tables related to replication, so be careful.

HTH

-Rajesh

Quote:>-----Original Message-----
>Since I disabled replication thru Enterprise manger, I
>can't run any of the SP's you sugessted, since I have no
>publications.

>Can I drop dtproperties from the database and
>MSreplication_options from master?
>Then alter every table to remove the replication columns?

>Would that mess anything up?

>Thanks,
>Joe

>.

Hi Rajesh,
I appreciate all your help with my situation here.

I tried all your SP's and they completed successfully but
they did nothing to solve my issue.
I still have the msrepl_tran_version columns in my
tables.  I tried to alter the tables and remove those
fields but I get an error "DF_tblSuppor_msrep_50ef592" is
dependant on column mrsrepl_tran_version.

I ran a trace and it tries to run a procedure in the MSDB
database called sp_sqlagent_get_perf_counters.

Now let me backtrack to the begining of my problem:
I have 5 SQLserver 2000 servers that I turned replication
on.  I have an access based front end program that hits
the database.  On these 5 servers, when I go into a field
and backspace to the first character it freezes the client
up for 5 minutes.  I restored a replicated DB and a non
replicated DB on a new server, the non replicated DB works
fine the replicated still freezes.  when the replicated DB
freezes it is also running the
sp_sqlagent_get_perf_counters.  the non replicated DB on
the same Server does not run the proc.

Now that I probably completely confuesed you do you have
any ideas about this situation?

again...
THANKS FOR YOUR HELP!

Joe

Hello Joe,

Here is the script that will have your task minimized. It
shall probably run as it is, but might need to be
customized. This script will drop all the dependant
indexes and defaults on the column and then finally the
column.

-- Drops indexes dependant on mrsrepl_tran_version columns
in complete database

     DECLARE d_indexes_cursor CURSOR LOCAL FAST_FORWARD FOR
          SELECT so.name + '.' + si.name FROM sysindexes
si, sysobjects so, syscolumns sc WHERE si.id=so.id and
so.id=sc.id AND sc.name LIKE '%mrsrepl_tran_version%' and
si.name like '%msrep%' ORDER BY si.name
     OPEN d_indexes_cursor


     BEGIN

          BEGIN

          END

     END
     DEALLOCATE d_indexes_cursor
     GO

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
id=OBJECT_ID('tempdb..#defaults')) DROP TABLE #defaults
GO
-- Finds defaults dependant on mrsrepl_tran_version
columns in complete database
     select object_name(s.id) as table_name, sc.name as
column_name, object_name(t.id) as constraint_name,
  case when (0 & 1 <> 0 or not exists (select * from
dbo.syscomments where id = t.id and colid = 2))
    then t.text else null end as constraint_definition  
into #defaults
 from dbo.syscomments t, dbo.sysconstraints c, sysobjects
s, syscolumns sc
 where t.id = c.constid and  c.status & 0x0f <> 4
  and (0 & 1 <> 0 or t.colid = 1)
          and c.id = s.id
          and sc.id = s.id
          and sc.colid = c.colid and sc.name
= 'mrsrepl_tran_version'
 order by object_name(t.id), t.colid
GO

-- Drops defaults dependant on mrsrepl_tran_version
columns in complete database


     declare delete_check_constraint_cursor cursor for
          select table_name, column_name, constraint_name,
constraint_definition from #defaults
     open delete_check_constraint_cursor


          begin

+ ']'









     end

     close delete_check_constraint_cursor
     deallocate delete_check_constraint_cursor
GO
HTH

-Rajesh

Quote:>-----Original Message-----
>Hi Rajesh,
>I appreciate all your help with my situation here.

>I tried all your SP's and they completed successfully but
>they did nothing to solve my issue.
>I still have the msrepl_tran_version columns in my
>tables.  I tried to alter the tables and remove those
>fields but I get an error "DF_tblSuppor_msrep_50ef592" is
>dependant on column mrsrepl_tran_version.

>I ran a trace and it tries to run a procedure in the MSDB
>database called sp_sqlagent_get_perf_counters.

>Now let me backtrack to the begining of my problem:
>I have 5 SQLserver 2000 servers that I turned replication
>on.  I have an access based front end program that hits
>the database.  On these 5 servers, when I go into a field
>and backspace to the first character it freezes the
client
>up for 5 minutes.  I restored a replicated DB and a non
>replicated DB on a new server, the non replicated DB
works
>fine the replicated still freezes.  when the replicated
DB
>freezes it is also running the
>sp_sqlagent_get_perf_counters.  the non replicated DB on
>the same Server does not run the proc.

>Now that I probably completely confuesed you do you have
>any ideas about this situation?

>again...
>THANKS FOR YOUR HELP!

>Joe

>.

Hi Joe,
I'm afraid you are going to have to script a small script to manually drop
these objects. The DF_.... is a default constraint. Write a cursor based
script which will operate on all objects from sysobjects which are of type
'D' and have a name like DF%msrepl% and are of type 'D' (default.) Inside
the cursor scroll, DROP the objects corresponding to the following columns
in the SELECT:

name --- for the default
object_name(parent_obj) --- for the table

HTH,

Arvind.
--
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
This posting is provided "AS IS" with no warranties, and confers no rights.
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-

Quote:> Hi Rajesh,
> I appreciate all your help with my situation here.

> I tried all your SP's and they completed successfully but
> they did nothing to solve my issue.
> I still have the msrepl_tran_version columns in my
> tables.  I tried to alter the tables and remove those
> fields but I get an error "DF_tblSuppor_msrep_50ef592" is
> dependant on column mrsrepl_tran_version.

> I ran a trace and it tries to run a procedure in the MSDB
> database called sp_sqlagent_get_perf_counters.

> Now let me backtrack to the begining of my problem:
> I have 5 SQLserver 2000 servers that I turned replication
> on.  I have an access based front end program that hits
> the database.  On these 5 servers, when I go into a field
> and backspace to the first character it freezes the client
> up for 5 minutes.  I restored a replicated DB and a non
> replicated DB on a new server, the non replicated DB works
> fine the replicated still freezes.  when the replicated DB
> freezes it is also running the
> sp_sqlagent_get_perf_counters.  the non replicated DB on
> the same Server does not run the proc.

> Now that I probably completely confuesed you do you have
> any ideas about this situation?

> again...
> THANKS FOR YOUR HELP!

> Joe

Thanks for all the help.

The script removed all the indexes and replication
columns.  But there is still something very screwy with
these databases.  
I can't understand why 5 out of 20 Databases are trying to
run msdb.dbo.sp_sqlagent_get_perf_counters when I
backspace on one field in a search field in my front end
Program.  Very very strange.

I have a good DB and a Bad DB on the same server now and
no matter what I do to the bad one It freezes my machine
but the good DB using the same Frontend works fine.

Oh well,
Thanks again for your help!
Joe

Dear Joe,
sp_sqlagent_get_perf_counters is related to alerts. Have you setup alerts on
this machine?

Secondly, what exactly do you mean by "good DB" and "bad DB"? is the DB
suspect? Did the bad DB "become bad" after some specific actions taken?

Regards,

Arvind.

--
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
This posting is provided "AS IS" with no warranties, and confers no rights.
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-

Quote:> Thanks for all the help.

> The script removed all the indexes and replication
> columns.  But there is still something very screwy with
> these databases.
> I can't understand why 5 out of 20 Databases are trying to
> run msdb.dbo.sp_sqlagent_get_perf_counters when I
> backspace on one field in a search field in my front end
> Program.  Very very strange.

> I have a good DB and a Bad DB on the same server now and
> no matter what I do to the bad one It freezes my machine
> but the good DB using the same Frontend works fine.

> Oh well,
> Thanks again for your help!
> Joe

No I have not set up alerts (or at least not on purpose)

I setup a new server and restored two different company
databases on it.

One database (good DB) did not freeze my application when
backspacing to the begining of the search field.

the other Database (bad DB) was restored from a different
server that did freeze my client staion.

Thanks,
Joe

I am also not sure where to look for the alerts

Thanks again,
joe