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: 4813 Expected the text length in data stream for bulk copy

Hi,
I am getting this error with Transactional replication for a table which has
column data type text, and has no data in it.
"Error: 4813,Expected the text length in data stream for bulk copy of text,
ntext, or image data."
Any idea why I am getting this error.
Thanks
Shan
Have a look at this
http://groups-beta.google.com/group/...e=source&hl=en
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
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:D8A5F5BC-9BFE-4CC0-A12F-1362DB8FAC77@.microsoft.com...
> Hi,
> I am getting this error with Transactional replication for a table which
has
> column data type text, and has no data in it.
> "Error: 4813,Expected the text length in data stream for bulk copy of
text,
> ntext, or image data."
> Any idea why I am getting this error.
> Thanks
> Shan

Error: 14151, Severity: 18, State: 1. Error when reinitialising a publication

Hi,

I have three publications on a dev server. All are sql 2005 merge replication (push) with replication running to subscribers on the same instance of sql server (note this is just dev testing).

However, I have just marked one for re-initialisation / create new snapshot / don't upload changes but when I kick off the synchronisation it says it has insufficient permission to run the snapshot.

When I do this for the other two there is no problem.

SQLAgent is running (using LocalSystem) so I don't know why my reinitialisation won't work.

Any help would be greatly appreciated.

I think I have found the source of my problem!

My parameterised filter referred to a view that wasn't in the publication. Doh!

Just wondering if this is something that should be validated in the wizard. Views referenced in the list of objects are flagged up to remind the user that the underlying objects must also be in the publication but this caution/validation isn't performed when setting up the filters.

Interestingly when I did have this set up incorrectly it did manage to create the subscriber but I don't think the partition was correctly created. However, when I attempted to re-initialize the subscription that's when I got the error.

Cheers, James

Error while synchronizing merge replication for first time

I am trying to run the replication merge agent for the first time for a
replication. Several servers already replicate to the same server without
problems. I have replicated all the previous Publishers to my subscriber
with the identical setup with the exception of the user login into the
server. The user login sees the appropriate tables in the subscriber yet the
first synchronization errors with the following:
rror messages:
The process could not connect to Subscriber 'hera'. (Source: MSSQL_REPL,
Error number: MSSQL_REPL20084) Get help: http://help/MSSQL_REPL20084 Named
Pipes Provider: Could not open a connection to SQL Server [53].
(Source: MSSQLServer, Error number: 53)
Get help: http://help/53
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact
that under the default settings SQL Server does not allow remote
connections. (Source: MSSQLServer, Error number: 53) Get help:
http://help/53 Login timeout expired (Source: MSSQLServer, Error number: 0)
Get help: http://help/0 The merge process failed to execute a query because
the query timed out.
If this failure continues, increase the query timeout for the process.
When troubleshooting, restart the synchronization with verbose history
logging and specify an output file to which to write. (Source:
MSSQLServer, Error number: 0)
Get help: http://help/0
The subscription to publication 'PUB_1' could not be verified. Ensure that
all Merge Agent command line parameters are specified correctly and that the
subscription is correctly configured. If the Publisher no longer has
information about this subscription, drop and recreate the subscription.
(Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201019) Get help:
http://help/MSSQL_REPL-2147201019
Have any of you seen this error before? Do you know of a possible solution?
Thank you,
Pete Ocasio
Senior Software Developer
It sounds like the publisher login or the distributor login does not have
sufficient rights to pull the subscription.
Verify the subscriber can connect to the publisher - try a ping and map a
drive to the publisher from the subscriber and then verify the distributor
login assigned on the subscriber is in the dbo_role on the publisher and the
publisher login defined on the subscriber is in the pal on the publisher.
http://www.zetainteractive.com - Shift Happens!
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
"Pete Ocasio" <pete.ocasio@.execupay.com> wrote in message
news:O5cWeJ9RIHA.4888@.TK2MSFTNGP02.phx.gbl...
>I am trying to run the replication merge agent for the first time for a
>replication. Several servers already replicate to the same server without
>problems. I have replicated all the previous Publishers to my subscriber
>with the identical setup with the exception of the user login into the
>server. The user login sees the appropriate tables in the subscriber yet
>the first synchronization errors with the following:
> rror messages:
> The process could not connect to Subscriber 'hera'. (Source: MSSQL_REPL,
> Error number: MSSQL_REPL20084) Get help: http://help/MSSQL_REPL20084 Named
> Pipes Provider: Could not open a connection to SQL Server [53].
> (Source: MSSQLServer, Error number: 53)
> Get help: http://help/53
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the fact
> that under the default settings SQL Server does not allow remote
> connections. (Source: MSSQLServer, Error number: 53) Get help:
> http://help/53 Login timeout expired (Source: MSSQLServer, Error number:
> 0) Get help: http://help/0 The merge process failed to execute a query
> because the query timed out.
> If this failure continues, increase the query timeout for the process.
> When troubleshooting, restart the synchronization with verbose history
> logging and specify an output file to which to write. (Source:
> MSSQLServer, Error number: 0)
> Get help: http://help/0
> The subscription to publication 'PUB_1' could not be verified. Ensure that
> all Merge Agent command line parameters are specified correctly and that
> the subscription is correctly configured. If the Publisher no longer has
> information about this subscription, drop and recreate the subscription.
> (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201019) Get help:
> http://help/MSSQL_REPL-2147201019
> Have any of you seen this error before? Do you know of a possible
> solution?
> Thank you,
> Pete Ocasio
> Senior Software Developer
>

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 creating subscription in transactional replication!

Hi ALL

I am configuring subscription for a reporting server and getting the following error message .

Any body having any idea would be of great help.

TITLE: New Subscription Wizard

Microsoft SQL Server Management Studio is unable to access replication components because replication is not installed on this instance of SQL Server. For information about installing replication, see the topic Installing Replication in SQL Server Books Online.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.ReplUtilitiesErrorSR&EvtID=ReplicationNotInstalled&LinkId=20476

ADDITIONAL INFORMATION:

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'CORSTRATASQL01'. (Replication.Utilities)

BUTTONS:

OK

Regards,

Salman Shehbaz.

Hi,
I had similar problems when I want to integrate a publisher into the replication monitor.
I had the problem , when the server is in anothe domain.
So first question is: Is CORSTRATASQL01 in anothe domain then the server you got the error message ?

At last we solve the problem with a reconfiguration of the DNS-suffixes of the TCP/IP properties.

Regards
Wolfgang