Re: Replication errors - vicious circle

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I'm not sure what is wrong here - this command should enable this

sp_configure 'allow updates', 1
go
reconfigure with override
go


-- 
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

"TahoePete" <TahoePete@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message 
news:793A5914-F3E9-4195-9F70-BA7C544E4427@xxxxxxxxxxxxxxxx
>I receive the error "ad hoc updates to system catalogues are not allowed"
> from the update command.
>
> "Hilary Cotter" wrote:
>
>> Issue the following statements
>>
>> sp_MSunmarkreplinfo 'ProblemTableName'
>>
>> Then you need to update the colstat column
>>
>> sp_configure 'allow updates', 1
>> go
>> reconfigure with override
>> go
>> UPDATE syscolumns
>> SET colstat = colstat & ~4096
>> WHERE colstat & 4096 <>0
>> go
>> sp_configure 'allow updates', 2
>> go
>> reconfigure with override
>> go
>>
>>
>> -- 
>> Hilary Cotter
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>>
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>>
>> "TahoePete" <TahoePete@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:346CE295-5031-45F3-90DA-9CA7A25424A5@xxxxxxxxxxxxxxxx
>> > When I try to create a transactional publication, when I select the 
>> > table
>> > I
>> > want to replicate, I get the error "This table cannot be published 
>> > because
>> > it
>> > does not have a primary key column. Primary key columns are required 
>> > for
>> > all
>> > tables in transactional publications".
>> >
>> > Then When I try to make the necessary changes to the table, such as 
>> > adding
>> > a
>> > column for the primary key, I get the error "Unable to modify table.
>> > Cannot
>> > drop the table "tablename" because it is being used for replication."
>> >
>> > I have never successfully replicated this table or any other. I can 
>> > find
>> > no
>> > evidence of any publications to remove.
>> >
>> > I'm stuck!
>> >
>>
>>
>> 


.

Relevant Pages

  • Re: sp_configure (RECONFIGURE)
    ... Sounds like you have the option "allow updates" enabled and are on SQL 2005. ... "with override". ... You should now be able to use reconfigure without encountering errors. ... system administrator. ...
    (microsoft.public.sqlserver.clustering)
  • Re: Identity columns
    ... reconfigure with override ... sp_configure 'allow updates', 0 ... this will fix your identity keys on all of your tables for the not for ...
    (microsoft.public.sqlserver.replication)
  • Re: Database : I/O error (torn page) detected
    ... Status still suspect. ... >> RECONFIGURE WITH OVERRIDE ... >> It won't work without allowing updates. ...
    (microsoft.public.sqlserver.server)
  • Re: Recover SQL Database from suspect status
    ... Sp_configure "allow updates", 1 ... Reconfigure with override ... > must reconfigure SQL Server to allow this. ...
    (microsoft.public.sqlserver.server)
  • GP cumulative effect and SUS
    ... another and still have one scheduling. ... >weekly reboots and updates occurring without incedent so ... >The question is - would one policy override the other, ...
    (microsoft.public.win2000.group_policy)