Orphaned Entries in sys.objects after Deleting Merge Replication - How to change sys.tables?

[Stefan Rosenthal]

After Removing Merge Replication on SQL2005 (Publisher) I wasn't able to
rename Tables, Columns...
(Error: ... is used by replication) ... I applied Scripts found here (Thx!)
... no success

Digging deeper into the problem I found out that there is a flag in the
system view sys.tables called "is_merge_published" which has a orphaned
boolean 1 for each table which had been replicated.

But in SQL2005 I found no way to change system catalogs (I know there is a
hidden Ressource DB...)! Simple T-SQL does not work (tried update sys.tables
set is_merge_published=0)

How can I update the system view?

Thx for any hints!!!

Stefan Rosenthal

[Paul Ibison]

Stefan,
I don't have sql 2005 here, but one way to do this in sql 2000 is using
sp_MSunmarkreplinfo which takes a tablename as a parameter. Please check out
if this is a possibility in 2005.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

[Stefan Rosenthal]

Hi Paul,

thx a lot for the moment - I will see/try and give feedback...

Stefan

Post by Paul Ibison
Stefan,
I don't have sql 2005 here, but one way to do this in sql 2000 is using
sp_MSunmarkreplinfo which takes a tablename as a parameter. Please check
out if this is a possibility in 2005.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
http://www.nwsu.com/0974973602p.html)

[Stefan Rosenthal]

... and it works!!! Thx Paul!

The table is unmarked and I can change columns again.

Regards
Stefan Rosenthal

Post by Paul Ibison
Stefan,
I don't have sql 2005 here, but one way to do this in sql 2000 is using
sp_MSunmarkreplinfo which takes a tablename as a parameter. Please check
out if this is a possibility in 2005.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
http://www.nwsu.com/0974973602p.html)

[Stefan Rosenthal]

The following Script removes the Replication Flag for all Tables a Database:

-- Removes Replication Flag for all Tables in the Database
-- using sp_MSunmarkreplinfo
SET NOCOUNT ON
DECLARE @tablename NVARCHAR(128)
DECLARE @RC INT
DECLARE curTable CURSOR FOR
SELECT [name] AS tbl
FROM sys.tables
OPEN curTable
FETCH NEXT FROM curTable
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE @RC = dbo.sp_MSunmarkreplinfo @tablename
FETCH NEXT FROM curTable
INTO @tablename
END
CLOSE curTable
DEALLOCATE curTable
GO

Post by Stefan Rosenthal
After Removing Merge Replication on SQL2005 (Publisher) I wasn't able to
rename Tables, Columns...
(Error: ... is used by replication) ... I applied Scripts found here
(Thx!) ... no success
Digging deeper into the problem I found out that there is a flag in the
system view sys.tables called "is_merge_published" which has a orphaned
boolean 1 for each table which had been replicated.
But in SQL2005 I found no way to change system catalogs (I know there is a
hidden Ressource DB...)! Simple T-SQL does not work (tried update
sys.tables set is_merge_published=0)
How can I update the system view?
Thx for any hints!!!
Stefan Rosenthal

[m***@salgacorp.com]

Tank's Stefan and Paul, i have same problem and anyone inte all www have the solution. Hugs;