can not remove rowguid currently replicated

After losing my publishing server, I re-built on a new server.
The databases had replication remnants so I ran cleanup scripts
(Replication system Objects, indexes and rowguids)
I then re-built replication
I find that I have a column (rowguid) left over that I can not remove.
The error message is "Can not Alter table, ... rowguid currently replicated"
how do I remove this column?
Haven't seen this error personally, but please have a look at the solutions
proposed in this thread for some ideas to take advantage of:
http://www.webservertalk.com/showthread.php?t=902849
HTH,
Paul Ibison
|||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
"Paul Ibison" wrote:

> Haven't seen this error personally, but please have a look at the solutions
> proposed in this thread for some ideas to take advantage of:
> http://www.webservertalk.com/showthread.php?t=902849
> HTH,
> Paul Ibison
>
|||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_B F47DE69E485"
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
"mj" wrote:
[vbcol=seagreen]
> 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
> ----
> "Paul Ibison" wrote: