Trying to set up replication as follows:
SQL2000 publisher , acting as it's own distributor (v8.0.760)
Transactional Replication with updateable subscriptions
SQL2005 subscriber (v9.00.3042)
Push subscription with immediate updating from distributor to subscriber and queued updating from subscriber to distributor
The set-up went smoothly and changes in the publication db flow down to the subscriber as expected. However, changes at the subscriber fail to flow up to the publisher.
The Queue Reader agent reports the error "Server MyServer, Database publisher_db : ODBC Error: Could not find stored procedure "upd.sp_MSrepl_ins_mytable_1"
The SP 'sp_MSrepl_ins_mytable_1' exists in the publisher db but the owner is 'dbo' not 'upd' (there is no such login as 'upd' on the server)
The only useful article I have found on the net suggests that this could be caused by a bug (jn SP3?) but the situation described in that article and the fix/workaround for it don't seem to apply for me.
The problem and fix in that article was: table MSsubscription agents in the subscriber db had a queue_id value of 'mssqlqueue' and the fix was to set it to 'mssqlqueuev2'.
However, it is already ''mssqlqueuev2' in my subscription db so I'm at a loss to explain why it's still generating calls looking for SPs owned by 'upd'.
other info:
Just to see if it would work around my immediate peoblem I tried creating a user called 'upd' and duplicating the SPs.
This worked up to a point. Changes replicated up to my publisher ok. However, when I created a conflict (changed same row on publisher and subscriber at same time)
the Queue Reader fell over with error: "Server MyServer, Database publisher_db : ODBC Error: Could not find stored procedure "upd.1".
-
Investigated the stored procdure sys.sp_replqueuemonitor in my subscription database and found statements like this:
select publisher, publisher_db, publication, queue_server, queue_id
from dbo.MSsubscription_agents
where
publisher = case when @.publisher is NULL then publisher else UPPER(@.publisher) end AND
publisher_db = case when @.publisherdb is NULL then publisher_db else @.publisherdb end AND
publication = case when @.publication is NULL then publication else @.publication end AND
update_mode IN (2,3) AND
queue_id != N'mssqlqueue'
note that the queue_id is is looking for is 'mssqlqueue'.
If I manually run sys.sp_replqueuemonitor I get no results (unsurprising since the data in dbo.MSsubscription_agents has queue_id='mssqlqueuev2'.
It looks to me as if some of the SPs created when the publication and/or subscription was set up are incorrect...?
--
Any advice or suggestions on how to approach this problem?
The owner for each article is set to 'dbo'. When it first happened there wasn't a login (or schema) called 'upd' in existence on the either of the publication or subscriber servers.
I'm just trialling this with a very small test db (2 tables) so no one else would be playing around with it. I've also recreated the database and publication from scratch several times with the same result.
I'm thinking it may be a bug or an incorrectly applied service pack (the servers are not under our control, I don't even know who maintains them) .
It's bound to be probably related to the fact that I'm trying to replicate between SQL2000 and SQL2005 servers. I know that merge replication with a SQL2000 publisher and a SQL2005 subscriber is not supported but transactional replication is supported (though I'm not 100% sure that encompases transactional replication with updateable subscribers...
)
The plot thickens...
If I create a subscriber on the same box as the publisher (i.e. publisher & subscriber are both SQL2000) it all works fine.
If the subscriber is SQL2005 it errors looking for SPs owned by 'upd'...
|||it's most likely a bug then. You're going to have to call customer support and open a case on this one if you want to get this type of scenario to work.|||Thanks for the help. We've abandoned replication for now (we were trying to retrofit it into an existing legacy tas a temporary workaround which is really more trouble than it's worth)
If we do need to get it working I'll raise it with customer support...
Cheers
Tony
|||My opinion would be to upgrade the SQL 2000 node to SQL 2005 and look into merge replication or transactional replication using Peer to Peer.