locked
invalid column name 'rowguid' RRS feed

  • Question

  • Hi all,

    I'm new to replication and database management so I will like some understanding and guiding as this is a new task for me.

    I was given a copy of a database with merge replication on it and doing allot of reading and using some scripts I  cleaned replication (or so Im thinking) and tryed to use it again on the ms-access front end. When I try to update some data I get "invalid column name 'rowguid'" , but no such column exist in my database as it was dropped.

    Any help on this?

    thanks George.

    Wednesday, November 29, 2006 12:03 PM

Answers

  • The table does not have the rowguid column.

    Your front end is still thinking that the column exists and is either trying to insert/update/delete or access it. Check your front end code.

    For eg: originally table was: Employee (Name varchar(20), id int, rowguid uniqueidentifier rowguidcol).

    and your front end was doing: insert into Employee (Name, id, rowguid) values ('Bill', 1, newid())

    or doing: select * from Employee

    After you removed replication: table is Employee (Name varchar(20), id int)

    Now with this schema, if you try to insert or access the missing column, you will get the above error.

    Thursday, November 30, 2006 5:45 PM

All replies

  • You refer to rowguid column either with its real column name or ROWGUIDCOL identifier. ie:

    create table myTable ( pkid uniqueidentifier rowguid, othercolumn int )

    select pkid, othercolumn from myTable

    select ROWGUIDCOL, othercolumn from mytable

    Wednesday, November 29, 2006 1:05 PM
  • When you setup merge replication, it adds a guid column to your table article. You scripts may have dropped the column when you clean up the replication and your front end may still have dependency to that column. Perhap, add a dummy column with same name and type to see if that would get your access front end to work.

    Regards,

    Gary

    Wednesday, November 29, 2006 5:31 PM
  • As Gary mentioned most likely the column is removed when you cleaned up replication.

    It is better to update your front end to not reference this non-existant column.

    Wednesday, November 29, 2006 10:09 PM
  • Thanks for the replies.

    The database I was given had a pull subscription on it. With the scripts it was cleared. In my access code using breakpoints I did not find any references to this columns in places where the code steps into.

    The code I found and used to clean all replication (indexes, rowguid column etc.)  in the database follows:

    ************************************

    UPDATE sysobjects set replinfo=0
    GO

    DECLARE @name nvarchar(129)
    DECLARE list_views  CURSOR FOR
    SELECT name FROM sysobjects WHERE  type='V' and (name
    like 'syncobj_%' or name like 'ctsv_%' or name
    like 'tsvw_%')

    OPEN list_views  

    FETCH NEXT FROM list_views INTO @name
    WHILE @@FETCH_STATUS = 0
    BEGIN
            PRINT 'dropping View ' +@name
            select  @name='drop View ' + @name
            EXEC sp_executesql @name
            FETCH NEXT FROM list_views INTO @name
    END

    CLOSE list_views
    DEALLOCATE list_views

    GO

    DECLARE @name nvarchar(129)
    DECLARE list_procs CURSOR FOR
    SELECT name FROM sysobjects WHERE  type='p' and (name
    like 'sp_ins_%' or name like 'sp_MSdel_%' or name
    like 'sp_MSins_%'or name like 'sp_MSupd_%' or name
    like 'sp_sel_%' or name like 'sp_upd_%')

    OPEN list_procs

    FETCH NEXT FROM list_procs INTO @name
    WHILE @@FETCH_STATUS = 0
    BEGIN
            PRINT 'dropping procs ' +@name
            select  @name='drop procedure ' + @name
            EXEC sp_executesql @name
            FETCH NEXT FROM list_procs INTO @name
    END

    CLOSE list_procs
    DEALLOCATE list_procs

    GO

    DECLARE @name nvarchar(129)
    DECLARE list_conflict_tables CURSOR FOR
    SELECT name From sysobjects WHERE  type='u' and name
    like '_onflict%'

    OPEN list_conflict_tables

    FETCH NEXT FROM list_conflict_tables INTO @name
    WHILE @@FETCH_STATUS = 0
    BEGIN
            PRINT 'dropping conflict_tables  ' +@name
            select  @name='drop Table ' + @name
            EXEC sp_executesql @name
            FETCH NEXT FROM list_conflict_tables INTO @name
    END

    CLOSE list_conflict_tables
    DEALLOCATE list_conflict_tables

    GO

    Declare @name nvarchar(129), @constraint nvarchar(129)
    DECLARE list_rowguid_constraints CURSOR FOR
    select object_name(sysobjects.parent_obj), sysobjects.name
    from sysobjects, syscolumns where sysobjects.type ='d'  
    and syscolumns.id=sysobjects.parent_obj
    and syscolumns.name='rowguid'

    OPEN list_rowguid_constraints

    FETCH NEXT FROM list_rowguid_constraints INTO @name,
    @constraint
    WHILE @@FETCH_STATUS = 0
    BEGIN
            PRINT 'dropping rowguid constraints  ' +@name
            select  @name='ALTER TABLE ' + rtrim(@name ) + '
    DROP CONSTRAINT ' +@constraint
            EXEC sp_executesql @name
            FETCH NEXT FROM list_rowguid_constraints INTO
    @name, @constraint
    END

    CLOSE list_rowguid_constraints
    DEALLOCATE list_rowguid_constraints

    GO

    Declare @name nvarchar(129), @constraint nvarchar(129)
    DECLARE list_rowguid_indexes CURSOR FOR
    select object_name(id), name from sysindexes where name
    like 'index%'

    OPEN list_rowguid_indexes

    FETCH NEXT FROM list_rowguid_indexes INTO @name,
    @constraint
    WHILE @@FETCH_STATUS = 0
    BEGIN
            PRINT 'dropping rowguid indexes ' +@name
            select  @name='drop index ' + rtrim(@name ) + '.'
    +@constraint
            EXEC sp_executesql @name
            FETCH NEXT FROM list_rowguid_indexes INTO @name,
    @constraint
    END

    CLOSE list_rowguid_indexes
    DEALLOCATE list_rowguid_indexes

    GO
    Declare @name nvarchar(129)
    DECLARE list_rowguid_columns CURSOR FOR
    select object_name(syscolumns.id) from syscolumns,
    sysobjects where syscolumns.name like 'rowguid'
    and sysobjects.id=syscolumns.id
    and sysobjects.type='u' order by 1

    OPEN list_rowguid_columns

    FETCH NEXT FROM list_rowguid_columns INTO @name
    WHILE @@FETCH_STATUS = 0
    BEGIN
            PRINT 'dropping rowguid columns ' +@name
            select  @name='Alter Table ' + rtrim(@name ) + '
    drop column rowguid'
            print @name
            EXEC sp_executesql @name
            FETCH NEXT FROM list_rowguid_columns INTO @name
    END

    CLOSE list_rowguid_columns
    DEALLOCATE list_rowguid_columns


    DELETE FROM sysmergepublications
    GO
    DELETE FROM sysmergesubscriptions
    GO
    DELETE FROM syssubscriptions
    GO
    DELETE FROM sysarticleupdates
    GO
    DELETE FROM systranschemas
    GO
    DELETE FROM sysmergearticles
    GO
    DELETE FROM sysmergeschemaarticles
    GO
    DELETE FROM sysmergesubscriptions
    GO
    DELETE FROM sysarticles
    GO
    DELETE FROM sysschemaarticles
    GO
    DELETE FROM syspublications
    GO
    DELETE FROM sysmergeschemachange
    GO
    DELETE FROM sysmergesubsetfilters
    GO
    DELETE FROM MSdynamicsnapshotjobs
    GO
    DELETE FROM MSdynamicsnapshotviews
    GO
    DELETE FROM MSmerge_altsyncpartners
    GO
    DELETE FROM MSmerge_contents
    GO
    DELETE FROM MSmerge_delete_conflicts
    GO
    DELETE FROM MSmerge_errorlineage
    GO
    DELETE FROM MSmerge_genhistory
    GO
    DELETE FROM MSmerge_replinfo
    GO
    DELETE FROM MSmerge_tombstone
    GO
    DELETE FROM MSpub_identity_range
    GO
    DELETE FROM MSrepl_identity_range
    GO
    DELETE FROM MSreplication_subscriptions
    GO
    DELETE FROM MSsubscription_agents
    GO


    ****************************************************************************************

    DECLARE @name varchar(129)
    DECLARE list_pubs CURSOR FOR
    SELECT name FROM sysmergepublications

    OPEN list_pubs

    FETCH NEXT FROM list_pubs INTO @name
    WHILE @@FETCH_STATUS = 0
    BEGIN
    /*
    sp_dropmergesubscription [ [ @publication = ] 'publication' ]
        [ , [ @subscriber = ] 'subscriber'
        [ , [ @subscriber_db = ] 'subscriber_db' ]
        [ , [ @subscription_type = ] 'subscription_type' ]
        [ , [ @ignore_distributor = ] ignore_distributor ]
        [ , [ @reserved = ] reserved
    */
            PRINT 'dropping publication  ' +@name
            EXEC sp_dropmergesubscription @publication=@name,
      @subscriber ='all'
            EXEC sp_dropmergepublication @name
            FETCH NEXT FROM list_pubs INTO @name
    END

    CLOSE list_pubs
    DEALLOCATE list_pubs
    GO

    DECLARE @name varchar(129)
    DECLARE list_replicated_tables CURSOR FOR
    SELECT name FROM sysobjects WHERE  replinfo <>0
    UNION
    SELECT name FROM sysmergearticles

    OPEN list_replicated_tables

    FETCH NEXT FROM list_replicated_tables INTO @name
    WHILE @@FETCH_STATUS = 0
    BEGIN
            PRINT 'unmarking replicated table ' +@name
            --select        @name='drop Table ' + @name
            EXEC sp_msunmarkreplinfo @name
            FETCH NEXT FROM list_replicated_tables INTO @name
    END

    CLOSE list_replicated_tables
    DEALLOCATE list_replicated_tables

    GO

    UPDATE syscolumns set colstat = colstat & ~4096 WHERE  
    colstat &4096 <>0
    GO
    UPDATE sysobjects set replinfo=0
    GO

    DECLARE @name nvarchar(129)
    DECLARE list_views  CURSOR FOR
    SELECT name FROM sysobjects WHERE  type='V' and (name
    like 'syncobj_%' or name like 'ctsv_%' or name
    like 'tsvw_%')

    OPEN list_views  

    FETCH NEXT FROM list_views INTO @name
    WHILE @@FETCH_STATUS = 0
    BEGIN
            PRINT 'dropping View ' +@name
            select  @name='drop View ' + @name
            EXEC sp_executesql @name
            FETCH NEXT FROM list_views INTO @name
    END

    CLOSE list_views
    DEALLOCATE list_views

    GO

    DECLARE @name nvarchar(129)
    DECLARE list_procs CURSOR FOR
    SELECT name FROM sysobjects WHERE  type='p' and (name
    like 'sp_ins_%' or name like 'sp_MSdel_%' or name
    like 'sp_MSins_%'or name like 'sp_MSupd_%' or name
    like 'sp_sel_%' or name like 'sp_upd_%')


    OPEN list_procs


    FETCH NEXT FROM list_procs INTO @name
    WHILE @@FETCH_STATUS = 0
    BEGIN
            PRINT 'dropping procs ' +@name
            select  @name='drop procedure ' + @name
            EXEC sp_executesql @name
            FETCH NEXT FROM list_procs INTO @name
    END

    CLOSE list_procs
    DEALLOCATE list_procs

    GO

    DECLARE @name nvarchar(129)
    DECLARE list_conflict_tables CURSOR FOR
    SELECT name From sysobjects WHERE  type='u' and name
    like '_onflict%'

    OPEN list_conflict_tables

    FETCH NEXT FROM list_conflict_tables INTO @name
    WHILE @@FETCH_STATUS = 0
    BEGIN
            PRINT 'dropping conflict_tables  ' +@name
            select  @name='drop Table ' + @name
            EXEC sp_executesql @name
            FETCH NEXT FROM list_conflict_tables INTO @name
    END

    CLOSE list_conflict_tables
    DEALLOCATE list_conflict_tables

    GO

    UPDATE syscolumns set colstat=2 WHERE  name='rowguid'

    GO

    Declare @name nvarchar(129), @constraint nvarchar(129)
    DECLARE list_rowguid_constraints CURSOR FOR
    select object_name(sysobjects.parent_obj), sysobjects.name
    from sysobjects, syscolumns where sysobjects.type ='d'  
    and syscolumns.id=sysobjects.parent_obj
    and syscolumns.name='rowguid'

    OPEN list_rowguid_constraints

    FETCH NEXT FROM list_rowguid_constraints INTO @name,
    @constraint
    WHILE @@FETCH_STATUS = 0
    BEGIN
            PRINT 'dropping rowguid constraints  ' +@name
            select  @name='ALTER TABLE ' + rtrim(@name ) + '
    DROP CONSTRAINT ' +@constraint
            EXEC sp_executesql @name
            FETCH NEXT FROM list_rowguid_constraints INTO
    @name, @constraint
    END

    CLOSE list_rowguid_constraints
    DEALLOCATE list_rowguid_constraints

    GO

    Declare @name nvarchar(129), @constraint nvarchar(129)
    DECLARE list_rowguid_indexes CURSOR FOR
    select object_name(id), name from sysindexes where name
    like 'index%'

    OPEN list_rowguid_indexes

    FETCH NEXT FROM list_rowguid_indexes INTO @name,
    @constraint
    WHILE @@FETCH_STATUS = 0
    BEGIN
            PRINT 'dropping rowguid indexes ' +@name
            select  @name='drop index ' + rtrim(@name ) + '.'
    +@constraint
            EXEC sp_executesql @name
            FETCH NEXT FROM list_rowguid_indexes INTO @name,
    @constraint
    END

    CLOSE list_rowguid_indexes
    DEALLOCATE list_rowguid_indexes

    GO
    Declare @name nvarchar(129)
    DECLARE list_rowguid_columns CURSOR FOR
    select object_name(syscolumns.id) from syscolumns,
    sysobjects where syscolumns.name like 'rowguid' and
    object_Name(sysobjects.id) not like 'msmerge%'
    and sysobjects.id=syscolumns.id
    and sysobjects.type='u' order by 1

    OPEN list_rowguid_columns

    FETCH NEXT FROM list_rowguid_columns INTO @name
    WHILE @@FETCH_STATUS = 0
    BEGIN
            PRINT 'dropping rowguid columns ' +@name
            select  @name='Alter Table ' + rtrim(@name ) + '
    drop column rowguid'
            print @name
            EXEC sp_executesql @name
            FETCH NEXT FROM list_rowguid_columns INTO @name
    END

    CLOSE list_rowguid_columns
    DEALLOCATE list_rowguid_columns

    DELETE FROM sysmergepublications
    GO
    DELETE FROM sysmergesubscriptions
    GO

    DELETE FROM sysmergearticles
    GO
    DELETE FROM sysmergeschemaarticles
    GO
    DELETE FROM sysmergeschemachange
    GO

    DELETE ...

    *************************************************************************

    These are the two scripts

    I'm willing to repeat this with new directions to clean up the database if someone has an understantable way to do this.

    My version:

    /*
    Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)
      May  3 2005 23:18:38   Copyright (c) 1988-2003
    Microsoft Corporation  Personal Edition on Windows NT 5.1
    (Build 2600: Service Pack 2)
    SP4

    */

    This 2 scripts were run on my database.

    Thanks allot

    Thursday, November 30, 2006 7:10 AM
  • The table does not have the rowguid column.

    Your front end is still thinking that the column exists and is either trying to insert/update/delete or access it. Check your front end code.

    For eg: originally table was: Employee (Name varchar(20), id int, rowguid uniqueidentifier rowguidcol).

    and your front end was doing: insert into Employee (Name, id, rowguid) values ('Bill', 1, newid())

    or doing: select * from Employee

    After you removed replication: table is Employee (Name varchar(20), id int)

    Now with this schema, if you try to insert or access the missing column, you will get the above error.

    Thursday, November 30, 2006 5:45 PM
  • The issue has nothing to do with the front end.  I replicated the issue as follows:

    Take a database replicated with a merge publication and a push subscription, and (with DTS) copy the database to a SQL Server that does not have Replication configured.

    Run the script listed in a previous reply to this posting.

    Run any valid SQL that alters the data and makes no reference to the rowguid column.

     

    This produces the error in question. 

     

    The solution lies in the Triggers.  It appears that Replication either adds or modifies triggers that the scripts fail to remove or fix. 

    I came up with this code to add to the script, but the catch is that if you've got your own triggers that start "ins_", "del_" and "upd_", this will delete them as well.  So you may want to change the name of some of your triggers, first, if you use this naming convention:


    DECLARE @name nvarchar(129)
    DECLARE list_procs CURSOR FOR
    SELECT name FROM sysobjects WHERE  type='tr' and (name
    like 'ins_%' or name
    like 'del_%' or name 
    like 'upd_%')

    OPEN list_procs

    FETCH NEXT FROM list_procs INTO @name
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'dropping Triggers ' +@name
     select @name='drop trigger ' + @name
     EXEC sp_executesql @name
     FETCH NEXT FROM list_procs INTO @name
    END

    CLOSE list_procs
    DEALLOCATE list_procs
    go

    • Proposed as answer by Chigrboy Thursday, July 28, 2011 6:20 PM
    Monday, January 8, 2007 5:06 PM
  • So essentially the scripts did not cleanup replication from the database. I believe this is SQL 2000. There is a system stored procedure sp_removedbreplication that will help remove traces of replication from a database. However it is also know that in SQL 2000, this sp may not be completely removing the trace in some cases. It has been improved significantly in SQL 2005. Not that this posting will help resolve the issue, but posting it just for information.
    Tuesday, January 9, 2007 7:09 AM
  • Thanks to all.

    I managed to go over that problem (it had nothing to do with the front end!) I found some scripts at http://www.replicationanswers.com that were very helpfull.

     

     

    Thursday, January 25, 2007 3:09 PM
  • I know it's been a few years since this post was created but I just wanted to thank Russ Judge for his script. Just saved my @$$!

    I was pulling my hair out all morning with this exact problem.

    Thank you Russ~!

    Also, there are tons of orphaned "conflict" tables and views are of the type "system" which can't be deleted manually.

    Found lots of online articles but nothing has worked and I'd love to get the DB cleaned up. I know this probably should be posted

    in a new post but seems to be related to this issue. If any one knows how to get rid of them I'd love to hear it~!!

    Thursday, July 28, 2011 6:27 PM