Replication errors - vicious circle

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!
Just answered my own question after reading thru a ton of posts on this forum.
"run sp_removedbreplication on each database..."
Thanks Paul Ibison!
"TahoePete" wrote:

> 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!
>
|||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@.discussions.microsoft.com> wrote in message
news:346CE295-5031-45F3-90DA-9CA7A25424A5@.microsoft.com...
> 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!
>
|||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@.discussions.microsoft.com> wrote in message
> news:346CE295-5031-45F3-90DA-9CA7A25424A5@.microsoft.com...
>
>
|||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@.discussions.microsoft.com> wrote in message
news:793A5914-F3E9-4195-9F70-BA7C544E4427@.microsoft.com...[vbcol=seagreen]
>I receive the error "ad hoc updates to system catalogues are not allowed"
> from the update command.
> "Hilary Cotter" wrote:

Replication does not work properly

Hi,
I have a problem with rows deleted at subscriber could not get deleted at
publisher in particular case where article published say table1 has row
filter like
"SELECT <published_columns> FROM [dbo].[TABLE1]
WHERE ([FILED1]='XYZ ' OR [FIELD4]= 'XYZ ') and FIELD3 in(select
[FIELD3] from [TABLE2] A where A.[FIELD1]=[FIELD1] and A.[FIELD2]=[FIELD2] )"
Rest of the Articles published with simpler row filter works perfectly.
Please Reply,
Thanking You
fatiya
are both Table1 and Table2 published? are there any constraints between teh
two tables? Are you enforcing these constraints for replication? Cascading
Updates/Deletes?
Have you used the conflict viewer to see if there are any conflicts?
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
"Fatiya" <Fatiya@.discussions.microsoft.com> wrote in message
news:6A20CE4B-7D05-4202-BCEE-79AC26E28326@.microsoft.com...
> Hi,
> I have a problem with rows deleted at subscriber could not get deleted
at
> publisher in particular case where article published say table1 has row
> filter like
> "SELECT <published_columns> FROM [dbo].[TABLE1]
> WHERE ([FILED1]='XYZ ' OR [FIELD4]= 'XYZ ') and FIELD3 in(select
> [FIELD3] from [TABLE2] A where A.[FIELD1]=[FIELD1] and
A.[FIELD2]=[FIELD2] )"
> Rest of the Articles published with simpler row filter works perfectly.
> Please Reply,
> Thanking You
> fatiya
>
>
|||Dear Hilary
Thanks for reply
Yes both table 1 and table2 are published No there are no contrainst between
them
we have not used any cascading
We are getting replicaion conflicts that the row was inserted at subscriber
but
at publisher we are getting primary key constrains.
Please advice
Fatiya
"Hilary Cotter" wrote:

> are both Table1 and Table2 published? are there any constraints between teh
> two tables? Are you enforcing these constraints for replication? Cascading
> Updates/Deletes?
> Have you used the conflict viewer to see if there are any conflicts?
> --
> 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
> "Fatiya" <Fatiya@.discussions.microsoft.com> wrote in message
> news:6A20CE4B-7D05-4202-BCEE-79AC26E28326@.microsoft.com...
> at
> A.[FIELD2]=[FIELD2] )"
>
>
sql

Replicating new table

Is it possible to have a newly-created table automatically
added to the list of published articles? If so, how?
Jim,
you'd have to manually run sp_addarticle (for snapshot and transactional,
followed by running the snapshot agent and synchronizing). Adding a table
involves inserts into syscolumns, sysobjects and various other tables in the
database catalog, but we can't add triggers onto such tables, so there is no
'automatic' process - it has to be done manually.
HTH
Paul Ibison
|||such a method would involve constantly polling the sysobjects and sysindexes
tables looking for a new table with a PK.
Then you would have to run sp_addarticle to add this table to your
publication.
Note that Log Shipping will do what you are looking for.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jim" <jim_beale@.hotmail.com> wrote in message
news:02e801c47a90$7c17a460$a301280a@.phx.gbl...
> Is it possible to have a newly-created table automatically
> added to the list of published articles? If so, how?
>