BUG: Replication Distribution Agent Job Fails on Case-Sensitive Servers with Error 21056 (290054)
The information in this article applies to:
This article was previously published under Q290054
BUG #: 101319 (SQLBUG_70)
SYMPTOMS
The following 21056 error message may occur when you run a Distribution Agent job
The subscription to publication Null has expired or does not exist.
when all of these conditions are met:
- The publisher and the subscriber servers are both case-sensitive.
- The SQL Server servers are registered using a name in upper case.
- The sysservers system table of the publisher server has two entries for the same subscriber server. One entry uses an upper case server name, and one entry uses a lower case server name.
- The sysservers table of the subscriber server has two entries for the same publisher server. One entry uses an upper case server name, and one entry uses a lower case server name.
After the initial synchronization finishes successfully, when you make additional changes to the publisher, and you then try to run the Distribution Agent job, the job may fail with the 21056 error message.
CAUSE
By default, the Distribution Agent job command uses upper case for the subscriber server name, even though in the sysservers system table on the publisher you have one server name in upper case, and one lower case entry, which each have different server ids for the same subscriber server in the distribution.MSdistribution_agents table. However, there is only one Distribution Agent for the subscriber name, and that Distribution Agent is bound to only one of the server ids. In another words, one of the subscriber names (either the upper case name or the lower case name) has no corresponding Distribution Agent job.
During the process of the Distribution Agent job, the Distribution Agents calls the sp_MShelp_distribution_agentid stored procedure. If the subscriber server name (case-sensitive) that is passed to the stored procedure is the server name that has no corresponding Distribution Agent, the error message occurs.
WORKAROUND
To work around this behavior, use these steps:
- Determine the server id for the Distribution Agent, and then use the server id to obtain the correct server name from the sysservers table on the publisher. To do this, run the following SQL query:
select srvname
from master..sysservers as S
inner join
distribution..MSdistribution_agents as M
on S.srvid = M.subscriber_id
- Stop this Distribution Agent job.
- Navigate to the Distribution Agent job command in the SQL Server Enterprise Manager, and then change the subscriber name by using the correct case that you obtained in step 1.
- Restart the Distribution Agent job.
STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0.
| Modification Type: | Major | Last Reviewed: | 10/16/2002 |
|---|
| Keywords: | kbBug kbDSupport KB290054 |
|---|
|