Microsoft KB Archive/922767

= Subscription distribution may fail after you synchronize a subscription in SQL Server 2005 =

Article ID: 922767

Article Last Modified on 11/20/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
 * Microsoft SQL Server 2005 Enterprise X64 Edition
 * Microsoft SQL Server 2005 Standard X64 Edition
 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
 * Microsoft SQL Server 2005 Workgroup Edition

-



SYMPTOMS
Consider the following scenario:
 * You synchronize a subscription in Microsoft SQL Server 2005.
 * You use an Oracle-based server as the subscription publisher.
 * You apply any hotfix to the instance of SQL Server 2005.

In this scenario, the subscription distribution may fail. Additionally, you may receive the following error message:

The version of SQL Server used to download the Oracle package code does not match the version of SQL Server running at the distributor. The replication support code at the Oracle publisher needs to be refreshed (Source MSSQL_REPL, Error number: MSSQL_REPL22202)

Heterogeneous Logreader encountered an error in call to LoadReplCmds when processing state ‘INITIALIZE’ (Source: MSSQL_REPL, Error Number:MSSQL_REPL22037)

You may experience this problem after you apply the hotfix to a computer that is running one of the following:
 * The original release version of SQL Server 2005
 * SQL Server 2005 Service Pack 1 (SP1)



CAUSE
After you apply a hotfix to an instance of SQL Server, the @@VERSION function returns a value for the SQL Server build. The Oracle log reader compares the first 40 characters of the current value for @@VERSION with the first 40 characters of the value at the time that the Oracle package code was downloaded. This problem occurs when these two strings do not match.



WORKAROUND
To work around this problem, you must update the HREPL_VERSION table in the Oracle database to reflect the SQL Server value for @@VERSION after you apply the hotfix. To do this, use one of the following methods.

Method 1
  Reset the SQL Server version that is maintained at the Oracle publisher and then create a new linked server to access the Oracle database. To do this, run the following code. exec sp_addlinkedserver @server='', @datasrc='', @srvproduct='Oracle Database', @provider='MSDAORA' exec sp_serveroption '', 'rpc out', 'TRUE' exec sp_serveroption '', 'rpc', 'TRUE' exec sp_serveroption '', 'data access', 'TRUE' exec sp_addlinkedsrvlogin @rmtsrvname = '', @locallogin='', @useself = 'FALSE', @rmtuser = '', @rmtpassword = '' Notes  To perform this update at the Oracle database, you cannot use the linked server that is used for Oracle publishing. You must create a new linked server. After you install the update, you must delete the new linked server.</li> <li>Replace  with the name of new linked server.</li> <li>Replace  with the data source name that is associated with the new linked server.</li> <li>Replace  with your domain logon information.</li> <li>Replace  with the replication user for Oracle publishing.</li> <li>Replace  with the password that is associated with  .</li></ul> </li> <li> Use the new linked server to update the HREPL_VERSION table. To do this, run the following code on the linked server. declare @cmd nvarchar(max) set @cmd = N'UPDATE HREPL_VERSION SET SQLSERVERVERSION =  '    + N' + SUBSTRING(@@version, 1, 40) + ' exec (@cmd) at [<ORASRV>] </li> <li> Drop the new linked server. To do this, run the following code. exec sp_dropserver '<ORASRV>', 'droplogins' </li></ol>

Method 2
<ol> <li> Update the HREPL_VERSION table by using SQL*Plus. Run the following select @cmd command to determine the command that you run in SQL*Plus in the Oracle database: declare @cmd nvarchar(max) set @cmd = N'UPDATE HREPL_VERSION SET SQLSERVERVERSION =  '    + N' + SUBSTRING(@@version, 1, 40) + ' select @cmd You receive output that resembles the following:

<pre class="fixed_text">UPDATE HREPL_VERSION SET SQLSERVERVERSION = 'Microsoft SQL Server 2005 - 9.00. <xxxx.xx>'

Note The placeholder  represents the @@VERSION value of the hotfix that is installed. </li> <li> In SQL*Plus, run the following command, and then commit the update: sqlplus -S <ADMINLOGIN>/ADMINPASSWORD@<ORADB> UPDATE HREPL_VERSION SET SQLSERVERVERSION = 'Microsoft SQL Server 2005 - 9.00.<xxxx.xx>'; commit; EXIT Notes <ul> <li>Replace  with TNSNAME for accessing the Oracle database.</li> <li>Replace  with the replication user for Oracle publishing.</li> <li>Replace  with the password that is associated with  .</li> <li>Replace  with the numeric value that is returned by running the @@VERSION function in step 1 after you install the hotfix.</li></ul> </li></ol>

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.

<div class="moreinformation_section">

MORE INFORMATION
The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.

Additional query words: Heterogeneous Publisher

Keywords: kbtshoot kbprb kbexpertiseadvanced kbsql2005setup KB922767

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.