error: Could not find SP upd.sp_MSrepl_xxx_xxxxxx_1

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?

Check your article properties, somehow the destination owner must have been changed to "upd" along the way. You can also query sysarticles in your published database to see if this owner exists anywhere. Please let me know if this is not the case.|||

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...)

|||sorry, i totally missed the updatable subscriber part, let me reread your thread and dig in some more.|||

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.

Error while setting up Replication

I am trying to set up replication from a remote server (YYYY) to a server
(XXXX) on my company's network. The remote server will be the publisher and
the local server will be the distributor and subscriber. It gives me the
following error when I configure the publisher and distributor :
SQL Server could not configure 'XXXX' as the Distributor for 'YYYY'.
Error 17: SQL Server does not exist or access denied.
I could log on as 'sa' to the remote server (YYYY) as well as the local
server (XXXX) via SQL Enterprise manager.
I know port #1433 is opened up. What other ports do I need to open up ? From
where ?
Please help
TIA
Mo
It might be a naming issue.
Try:
Use Master
go
Select @.@.Servername
This should return your current server name but if it
returns NULL then try:
Use Master
go
Sp_DropServer 'OldName'
GO
Use Master
go
Sp_Addserver 'NewName', 'local'
GO
Stop and Start SQL Services
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||You have to use the netbios names of the publisher and subscriber. Register
both in EM using the netbios names. Hide the fqdn (if you need to use it) by
using a server alias using Client Network Utility.
Have you set up an administrative link password? Disable this option to make
debugging simpler.
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
"MO" <MO@.discussions.microsoft.com> wrote in message
news:86384240-283C-42CA-A5A6-15647FDA34B8@.microsoft.com...
> I am trying to set up replication from a remote server (YYYY) to a server
> (XXXX) on my company's network. The remote server will be the publisher
and
> the local server will be the distributor and subscriber. It gives me the
> following error when I configure the publisher and distributor :
> SQL Server could not configure 'XXXX' as the Distributor for 'YYYY'.
> Error 17: SQL Server does not exist or access denied.
> I could log on as 'sa' to the remote server (YYYY) as well as the local
> server (XXXX) via SQL Enterprise manager.
> I know port #1433 is opened up. What other ports do I need to open up ?
From
> where ?
> Please help
> TIA
> Mo
>
|||Thanks Paul for the quick reply. The sql instance name is say 'AAAA' and when
I run @.@.servername it returns 'AAAA'. The windows server name is say 'WWWW'.
TIA
MO
"Paul Ibison" wrote:

> It might be a naming issue.
> Try:
> Use Master
> go
> Select @.@.Servername
> This should return your current server name but if it
> returns NULL then try:
> Use Master
> go
> Sp_DropServer 'OldName'
> GO
> Use Master
> go
> Sp_Addserver 'NewName', 'local'
> GO
> Stop and Start SQL Services
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Some of the things you mentioned are above my head.
When you say netbios names of the publisher and subscriber you mean the
windows server name ? How do I know the netbios name ?
Hide the fqdn... What is fqdn ? and how do I hide it ?
TIA
MO
"Hilary Cotter" wrote:

> You have to use the netbios names of the publisher and subscriber. Register
> both in EM using the netbios names. Hide the fqdn (if you need to use it) by
> using a server alias using Client Network Utility.
> Have you set up an administrative link password? Disable this option to make
> debugging simpler.
> --
> 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
> "MO" <MO@.discussions.microsoft.com> wrote in message
> news:86384240-283C-42CA-A5A6-15647FDA34B8@.microsoft.com...
> and
> From
>
>
|||do a master.dbo.xp_cmdshell 'echo %computername%' for the netbios name. This
should be the same as what you get when you issue a select @.@.servername
unless you have an instance.
Make sure that the results of the echo %computername% is the same as what
you registered your computer in EM as.
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
"MO" <MO@.discussions.microsoft.com> wrote in message
news:4F03827C-055A-4783-84D0-75FE44858D77@.microsoft.com...[vbcol=seagreen]
> Some of the things you mentioned are above my head.
> When you say netbios names of the publisher and subscriber you mean the
> windows server name ? How do I know the netbios name ?
> Hide the fqdn... What is fqdn ? and how do I hide it ?
> TIA
> MO
> "Hilary Cotter" wrote:
Register[vbcol=seagreen]
it) by[vbcol=seagreen]
make[vbcol=seagreen]
server[vbcol=seagreen]
publisher[vbcol=seagreen]
the[vbcol=seagreen]
local[vbcol=seagreen]
?[vbcol=seagreen]
|||Did you forget to run the rest of the script...
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Error while Replication

Hello,
1. I have made my database server as Publisher & Distributor for a
database. I have added another server into my network, and I have configured
that machine as the Subscriber using Wizard. So when I run the Publishing
Agent, it runs fine without any problem. But in the Subscriber machine, the
status shows 'Failed' and hence the data is not replicated. So, can anyone
suggest me, where I could have gone wrong during the process?
2. I had selected the 'Immediet Queuing' and 'Queue' methodology to
update the Subscriber. Is this enough to make the changes to replicate
immedietly on the Subscriber as soon as the data is changed in Publisher? I
want the subscriber to get updated as soon as the data in Publisher is
updated. Are the above options will help in doing this?
1) Double Click on the failed agent. What is the message in error details.
2) No, you need to change your polling interval on the log reader agent and
distribution agents to 1 s to get data updated on the Subcsriber near real
time. I think you will be looking at latencies under 20s
"Sheshadrinath R" <SheshadrinathR@.discussions.microsoft.com> wrote in
message news:6D5693DD-5ACC-4FDD-AB6B-1E46EF55B5E5@.microsoft.com...
> Hello,
> 1. I have made my database server as Publisher & Distributor for a
> database. I have added another server into my network, and I have
> configured
> that machine as the Subscriber using Wizard. So when I run the Publishing
> Agent, it runs fine without any problem. But in the Subscriber machine,
> the
> status shows 'Failed' and hence the data is not replicated. So, can anyone
> suggest me, where I could have gone wrong during the process?
> 2. I had selected the 'Immediet Queuing' and 'Queue' methodology to
> update the Subscriber. Is this enough to make the changes to replicate
> immedietly on the Subscriber as soon as the data is changed in Publisher?
> I
> want the subscriber to get updated as soon as the data in Publisher is
> updated. Are the above options will help in doing this?

Error while generating Snapshot

Hello,
I have a problem with a MergeReplication. I have installed the Publisher and
the Distributor. Then I created a MergePullSubscription.
But when I want to generate the Snapshot I get the error:
Message: Error in the Replication-Agent while creating the folder 'xyz'
Stack: in
Microsoft.SqlServer.Replication.Utilities.CreateDi rectoryWithExtendedErrorInformation(String directory)
in
Microsoft.SqlServer.Replication.Snapshot.SnapshotP rovider.CreateSnapshotFolders()
in
Microsoft.SqlServer.Replication.Snapshot.MergeSnap shotProvider.CreateSnapshotFolders()
in
Microsoft.SqlServer.Replication.Snapshot.SqlServer SnapshotProvider.GenerateSnapshot()
in Microsoft.SqlServer.Replication.SnapshotGeneration Agent.InternalRun()
in Microsoft.SqlServer.Replication.AgentCore.Run() (Quelle: MSSQL_REPL,
errornumber: MSSQL_REPL52026)
Can anyone help me?
John
John,
please check that the sql server agent (or proxy for the snapshot job if you
do it that way) has access to the distribution working folder (repldata
share usually). Also check that there is enough space in the folder to add
files.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Hi Paul,
can you tell me what I have to do, if I want to give access to the
repldata-folder? I gave all permissions to everyone to the folder, but I
still get the error...
"Paul Ibison" wrote:

> John,
> please check that the sql server agent (or proxy for the snapshot job if you
> do it that way) has access to the distribution working folder (repldata
> share usually). Also check that there is enough space in the folder to add
> files.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||John - double-check that the share has the same permissions. After that, log
on to the box as the same account that is used (probably sql server agent
but could be the proxy). Then try to create a folder using that account in
the snapshot share. BTW are you using a publisher/distributor (one box) or a
remote distributor?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||John, then you don't need to check this. The way you have set things up is
the same as I did - you've mimiced SQL Server 2000 methodology. The
recommendation is not to do it this way and to lock down the security a
little more by using proxies. So, that leads me to the other part of my
post - can you log on as the sql server agent's account and create a
directory in the snapshot share. If this works then I'm out of ideas!
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Paul, I don′t know, where I get the Username and Passwort of the ServerAgent
Account...
"Paul Ibison" wrote:

> John, then you don't need to check this. The way you have set things up is
> the same as I did - you've mimiced SQL Server 2000 methodology. The
> recommendation is not to do it this way and to lock down the security a
> little more by using proxies. So, that leads me to the other part of my
> post - can you log on as the sql server agent's account and create a
> directory in the snapshot share. If this works then I'm out of ideas!
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>
>
|||On the server, have a look in the Control Panel, Administrative Tools,
Services, SQL Server Agent - Log On properties.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||I found it... the Server Agent starts with an integrated account from the
local system. Should I change this to a specified account?
"Paul Ibison" wrote:

> On the server, have a look in the Control Panel, Administrative Tools,
> Services, SQL Server Agent - Log On properties.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||If you're replicating to the same box this is fine. If you're going to
another box then it'll need to be a domain account with the correct
priviliges on your pub/dist and the subscriber as long as you are
impersonating it in the sql server agent and not using a proxy (SQL Server
2000 style). However I don't think this is the issue here as the problem is
in the creation of the snapshot folder. My last hope is that the disk is
full and no account can create a folder in the snapshot share - can you just
create a folder there as a test. Also change the sql server account to be a
domain one which is a local administrator on the publisher just to test this
although I don't see that this should make any difference. Am hoping it's a
simple space issue!
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||So, now changed the Account of the ServerAgent to the Domain-Admin Account.
When I am logged in to the machine with this account, I can create a folder
in the repldata...
But still the same problem when I want to start the Snapshotagent...
But who knows what I do wrong when I create the distributor/publisher and
the publication...
"Paul Ibison" wrote:

> If you're replicating to the same box this is fine. If you're going to
> another box then it'll need to be a domain account with the correct
> priviliges on your pub/dist and the subscriber as long as you are
> impersonating it in the sql server agent and not using a proxy (SQL Server
> 2000 style). However I don't think this is the issue here as the problem is
> in the creation of the snapshot folder. My last hope is that the disk is
> full and no account can create a folder in the snapshot share - can you just
> create a folder there as a test. Also change the sql server account to be a
> domain one which is a local administrator on the publisher just to test this
> although I don't see that this should make any difference. Am hoping it's a
> simple space issue!
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>