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 error after installing SP3a

After installation of SP3a on the publication server, clients are getting
errors during replication.
The error detail is : "Select permission denied on object
'MSDistributor_access', database 'tempdb', owner dbo
does this table exist in tempdb?
"Nol Thoelen" <noel.thoelen@.itomni.be> wrote in message
news:407bdf20$0$876$a0ced6e1@.news.skynet.be...
> After installation of SP3a on the publication server, clients are getting
> errors during replication.
> The error detail is : "Select permission denied on object
> 'MSDistributor_access', database 'tempdb', owner dbo
>
>
|||Yes, the table does exist in the tempdb database
"Hilary Cotter" <hilaryk@.att.net> schreef in bericht
news:erRWAPXIEHA.2596@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> does this table exist in tempdb?
> "Nol Thoelen" <noel.thoelen@.itomni.be> wrote in message
> news:407bdf20$0$876$a0ced6e1@.news.skynet.be...
getting
>

replication error

i have a problem

when i select distributor and click next

"SQL server agent on 'PC-...' currently uses the system account, which causes replication between servers to fail. In te following dialog box, specify another account for the Service startup account"

and

"SQL server enterprise manager couldn not configure 'PC-...' as the distributor for 'PC-...'

Error 18483: Could not connect to server 'PC-...' because 'distributor admin' is not defined as a remote login at the server."

what can i do?I would start by setting up the sql server agent to use a domain user account. I am no replication expert. Not yet anyways. That project is coming soon.|||My suggestions are:

1. Create a domain user specifically for SQL replication, and no other purpose
2. Before you get a new machine involved with replication, make that domain user a local administrator on the new machine.
3. Start the SQL Agent on the machine that is the distributor using the domain accout from step 1.

Depending on how far you got with the replication installation, you may want to reinstall Replication. I know that I would!

-PatP

Replication error

Hi,
I get the following error when I select Replication --> Configure Publisher...
from EM
"the passed ordinal is out of range of the specified collection"
didnt find any article on KBHave you thought of trying this Op from a different Client & poss SQL Box

Please advise Version in use etc

All I get when I do this on my home box is SQL Server Replication does not allow nicknames, but I'm not bothered - I've no one to replicate with @. the Mo anyway ;-(

GW|||version is
SQL Server 2000 Evaluation Edition..

i had a testdb configured for repln ..
wanted to delete it...couldnt do tat cos it said 'db set for repln' bla bla..
learnt from a forum tat if u update sysdatabases's category to 0 it wud set it...
did tat...
now when i use EM for repln it gives me the error...

not sure if the above actions are responsible for it..

cos when i use a difft server i dont get that error...
same version...|||Please choose one or more

a) Format the Server and reinstall SqlServer and restore Backups ?

Good for Practice - but B Careful !!

b) Please stop F**ing about willy nilly with the System Tables

c) Change the sysdatabases's category back to what it used to be and if neccesary recreate the DB that you had marked for replication replicate it then unmount it properly.

Are you in a Live Production Environment ?

GW|||I re-installed SQL Server on the test m/c.
Now I have a snapshot repln set up.
I get the following error on the Subscriber.

'The license for this installation of SQL Server does not permit Log Reader Agent to run at the Distributor.'.|||OOooopps.

Your struck with Luck - I have no easy answer to this

GW|||Other than installing SP3 will probably sort it :-)

GW|||any document/article to support it??|||I would have serviced pack'd it up before doing anything with it first anyway

http://www.developmentnow.com/g/114_2004_1_0_0_443902/Strange-Licensing-error.htm

GW|||Thanks a lot..
Snapshot repln on track :)|||U Welcome

Can we assume that the Service Pack Sorted it ?

GW|||yeah SP3 did the trick...
i guess SP4 is out too..but few bugs been reported against it so applied SP3

Replication Deadlock...I think

I am running a select cursor on spid:61 and an update statement (on a table common with the select in spid:61) on spid:51. While running, I experience a deadlock. The Enterprise Manager reports that spid:61 (Blocking) and spid:51 (Blocked by 61). SP_LOCK reports the following (with object_name resolved):

object_name spid mode status
------- ---- ---- --
NULL 51 S GRANT
sp_MSaddpub_snapshot 51 IX WAIT
sp_replflush 61 IS GRANT
sp_replcounters 61 IS GRANT
sp_changesubscriptio 61 IS GRANT
sp_MSscript_sync_del 61 IS GRANT
sp_MSscript_sync_ins 61 IS GRANT
sp_replpostschema 61 IS GRANT
sp_articleview 61 IS GRANT
sp_MSaddschemaarticl 61 IS GRANT
sp_changemergepublic 61 IS GRANT
sp_MSmakeconflicttab 61 IS GRANT
sp_MScopyscriptfile 61 IS GRANT
sp_MSuniquecolname 61 IS GRANT
sp_MSget_synctran_co 61 IS GRANT
sp_MShelptranconflic 61 IS GRANT
sp_replsetsyncstatus 61 IS GRANT
sp_MSaddpub_snapshot 61 S GRANT
NULL 61 S GRANT

(19 row(s) affected)

I see that the two SPIDs have the sp_MSaddpub_snapshot in common. This stored procedure attempts to update the syspublications table however I fail to see why this would generate a deadlock. I have rebooted the server, disabled and re-enabled replication. Any help would be appreciated.

--Other info--

Microsoft SQL Server 2000 Enterprise sp3 (active-passive cluster)
4 x 1.4ghz Xeon, 8gig (/3gb /pae)
Microsoft Windows 2000 Advanced Server sp3 (Identical active-passive nodes)

Database connection thru Trifox's "Vortex" DBlib linked client-server driver.What type of replication are you using ? Can you post what you are doing with the select cursor/update ?|||Also, does the deadlock occur immediately ?|||>>What kind of replication?
The server was initially set up with trans replication. I have since rebooted and removed replication alltogether and selectivly readded components, reproducing the deadlock behavior at every step.

>>does it happen immediately?
There are a number of other SELECT statements between the (Blocking) SELECT statement and when the UPDATE fires (approx 60-90 sec later). I can see the SQL:STMTStarting in the Profiler for the UPDATE statement however it never completes before the one hour connection timeout from the Vortex driver. I can KILL the SPID for the (Blocking) SELECT statement and then the (Blocked) UPDATE will fire. . . however I am not done fetching records from the SELECT recordset and the "fetch next row" from that cursor will fail (ConnectionWrite(Send())) or something like that -- typical MS message when trying to work with a connection killed at the server level. The SP_LOCK information indicates that the initial SELECT does not have any table locks and only low level child REPLICATION stored procedures are holding any locks.

>>What are the statements?
The initial SELECT statement is about 3 pages long with a number of sub-selects and derived tables from about 12 different tables. I really hope the solution does not depend on the nature of the SELECT statement; however I will post it if you feel it will help. I can run the SELECT statement in the QA without any problems. It returns the thousand or so expected records in about 30 seconds.
The UPDATE statement is trival e.g.

UPDATE owner.table
set column_a = 1
where column_b = 'value'

I appreciate your time.|||Have you tried adding readuncommitted/nolock to your select statement (because this sounds like nested transactions that are attempting to lock the same resource) ? Realize that this will give you dirty records - but test it to see if you still have the problem.|||I see now... The profiler reveals Lock Escalations (after a bit) which then cause the blocking. The NOLOCK seems to supress the lock escalation. Thank you.|||Originally posted by Makallus
I see now... The profiler reveals Lock Escalations (after a bit) which then cause the blocking. The NOLOCK seems to supress the lock escalation. Thank you.

Yeah, and let's you read uncommited transactions...

Expect miracles to start happening...|||Originally posted by Makallus
>>>>What are the statements?
The initial SELECT statement is about 3 pages long with a number of sub-selects and derived tables from about 12 different tables. I really hope the solution does not depend on the nature of the SELECT statement; however I will post it if you feel it will help. I can run the SELECT statement in the QA without any problems. It returns the thousand or so expected records in about 30 seconds.


What do you do with the result set? ARe youdragging thousands of rows to the client?

Is it done in a sproc?

Are you placing the results in table variables?

How does your update coorelate to the thousands of rows?

Why do the SELECT at all? Why not make the UPDATE a JOIN?|||I would look at your logic and modify it to prevent the lock escalation. Depending on your database/applications, reading dirty records could cause some major problems.|||All excellent replies. The reasons we (the company) do things this way come directly from Dilbert comic strips.

Brett, ever code in Dibol? What's better than coding in Dibol with database access is the fantasy we will be database independent. The idea being we can slap in any database the customer likes with no impact on the application. Shall I go on? However I understand your questions and I believe in time the application will behave more like it was written for a database than an ISAM file.

Rnealejr, this problem only occurs at a site where replication is enabled . . . given that there is no replication I do not appear to exceed the lock threshold.

As for doing things right. . . all good things in time.|||The lock escalation issue may appear in non-replication scenarios as well - but be intermittent. I just wanted to make sure that you understood the ramifications.sql

Replication conflict resolver

Hi Everyone!
Here another post on replication.

When conflict occur, I want the user to be able to select witch row to keep and witch to delete.

I've look for system stored procedure, that could help me do the resolution but I've founded nothing.

So I've thought that I could do it by hand, with UPDATE/DELETE query
Take the row in the %TABLE_NAME%_Conflict table and copy it into the real table, than delete the row.

I this the good way of doing it?

Is there any other way?

Thanks !No one, here have a custom conflict resolver ?|||

Hi Zakary,

It sounds like you are wanting to replicate - excuse the pun - the interactive conflict resolver.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/repref9/html/56cb122d-9b1e-4f79-81de-0118023d1004.htm

and ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/172c60c7-f605-4eb5-b185-54ae9e9d3c60.htm

|||

Thanks For your reply!

Those help topic, is about SQL 2005 and I’m using MSDE(SQL 2000).

I don't want to use SQL Conflict resolver, but a custom VB .NET Front-end, to resolve conflict. And I was wondering if my approach is appropriate or not.

Thanks

|||

Here is a sql 2000 link.

http://msdn2.microsoft.com/en-us/library/aa179421(SQL.80).aspx

I was thinking that although this might not give you exactly what you want you might be able to copy how it works functionally.

Cheers, James

Replication and Linked Server Problemes

Hi @.ll,
we have right now big problemes with our replication and linked servers to
oracle.
has anybody an idea what wrong?
if we use a select statement everything works fine, only if we use a update
statement on a table on the SQL Server we see the error.
Just for your info. we use JOIN over 2 tbl on the Linked Server Side.
the error message is :
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'MSDAORA'
was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8000d048
thanks
klaus
Klaus,
apart from the obvious check - is MSDTC started - have a look at these
articles which are designed to troubleshoot this error:
http://support.microsoft.com/default.aspx?kbid=306212
http://support.microsoft.com/kb/280106/EN-US/
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||http://support.microsoft.com/default...;en-us;Q280106
does this help.
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

replication - log shipping

Hi,
how to tell which is better Replication or log shipping?, what are the advantages / disadvantages using each.
if I want to select one of these what all I need to consider?
Please reply.
RamRefer to books online for those 2 topics as a first hand information.

It depends on your environment, if you explain bit about it can give suggestion.

Replication

Hi,
Am trying to configure replication in SQL server 6.5. But when I to configure replication menu and select Install publishing, it gives the following error
Replication cannot be installed as @.@.SERVERNAME is NUL
Has anybody faced this kind of problem? BTW, my server name has Hyphen, but I guess that shouldn't be an issue as I had read in one of the article that in 6.5, this problem is taken care of. Could there be any other issue
Thanks very much in advanc
GYKDo you get a NULL if you run the following command?
SELECT @.@.SERVERNAME
GO
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:78C27465-53D5-4DDF-8A9C-1E8A8C436364@.microsoft.com...
Hi,
Am trying to configure replication in SQL server 6.5. But when I to
configure replication menu and select Install publishing, it gives the
following error;
Replication cannot be installed as @.@.SERVERNAME is NULL
Has anybody faced this kind of problem? BTW, my server name has Hyphen, but
I guess that shouldn't be an issue as I had read in one of the article that
in 6.5, this problem is taken care of. Could there be any other issue?
Thanks very much in advance
GYK|||YES
I get NULL when I run SELECT @.@.SERVERNAME
Thanks
GYK