sql server replication: can not remove rowguid currently replicated -- sql server replication

RE: can not remove rowguid currently replicated

mj
7/30/2007 10:04:04 AM

Hi Paul;

This is what I have done so far

Problem:
Open EM
Open Agency Table
Delete Column
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
ALTER TABLE DROP COLUMN failed because 'rowguid' is currently replicated.

Approaches:
Drop replicated column - sp_repldropcolumn
Drop subscription - sp_dropsubscription
drop publication - sp_droppublication
disable publishing and distributor - sp_removedbreplication

exec sp_repldropcolumn @source_object = 'Agency'
, @column = 'rowguid'
, @force_reinit_subscription = 1

Server: Msg 21246, Level 16, State 1, Procedure sp_repldropcolumn, Line 212
This step failed because table 'Agency' is not part of any publication.

sp_dropsubscription @publication = 'IsoprepArchive'
, @subscriber = 'SQLDEV'
, @destination_db = 'Archivedisopreps'

sp_droppublication @publication = 'IsoprepArchive'

exec sp_removedbreplication 'Isoprep'

-- Unmark table for replication

SELECT 'exec sp_MSUnmarkReplInfo ' + '''' + Name + '''' + Char(13)
+ ' GO ' + Char(13)
+ 'ALTER TABLE ' + Name + CHAR(13)
+ 'DROP CONSTRAINT DF_' + Name + '_rowguid' + Char(13)
+ ' GO ' + CHAR(13)
+ 'ALTER TABLE ' + Name + CHAR(13)
+ 'DROP COLUMN ROWGUID' + Char(13)
+ ' GO ' + Char(13) + Char(13)
FROM sysobjects
WHERE xtype = 'U'

-- return replinfo flag
SELECT 'Print ' + '''' + Name + '''' + Char(13) + ' GO '
+ Char(13)
+ 'SELECT replinfo' + Char(13)
+ 'FROM sysobjects' + Char(13)
+ ' WHERE name = ' + '''' + name + '''' + Char(13)
FROM sysobjects
WHERE xtype = 'U'

------------------------------------------------------------------------------------------

exec sp_MSUnmarkReplInfo 'ErrorLog'
GO
ALTER TABLE ErrorLog
DROP CONSTRAINT DF_ErrorLog_rowguid
GO
ALTER TABLE ErrorLog
DROP COLUMN ROWGUID
GO

Warning: The table 'ErrorLog' has been created but its maximum row size
(9416)
exceeds the maximum number of bytes per row (8060).
INSERT or UPDATE of a row in this table will fail if the resulting row
length exceeds 8060 bytes.
Server: Msg 4932, Level 16, State 1, Line 1

ALTER TABLE DROP COLUMN failed because 'ROWGUID' is currently replicated.
Warning: The table 'ErrorLog' has been created but its maximum row size
(9416)
exceeds the maximum number of bytes per row (8060).
INSERT or UPDATE of a row in this table will fail if the resulting row
length exceeds 8060 bytes.

Thanks
MJS

--------------------------------------------------------------------------------------------

[quoted text, click to view]

RE: can not remove rowguid currently replicated

mj
7/30/2007 10:42:00 AM

Quite By Accident, I found the following:
open EM
Open Design view of table
open constraint tab
Look for replication constraints
I found a constraint that looked like:
"repl_identity_range_sub_B0BB3703_3C1D_4648_9DCA_BF47DE69E485"

execute the following to build an alter table statement that will
remove the constraints.

SELECT 'ALTER TABLE ' + U.name + CHAR(13)
+ 'DROP CONSTRAINT ' + C.name + CHAR(13)
+ CHAR(13) + 'GO' + CHAR(13)
FROM sysobjects C
, sysobjects U
WHERE C.parent_Obj = U.Id
AND C.xtype = 'C'
AND C.name like 'repl_identity_range_sub_%'

Thanks
MJ

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server replication group.