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

Article translations Article translations
Article ID: 922767 - View products that this article applies to.
Expand all | Collapse all

On This Page

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

  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='<ORASRV>', @datasrc='<ORCL>',
    @srvproduct='Oracle Database', @provider='MSDAORA'
    exec sp_serveroption '<ORASRV>', 'rpc out', 'TRUE'
    exec sp_serveroption '<ORASRV>', 'rpc', 'TRUE'
    exec sp_serveroption '<ORASRV>', 'data access', 'TRUE'
    exec sp_addlinkedsrvlogin @rmtsrvname = '<ORASRV>',
    @locallogin='<YourDomain\YourLogin>', @useself = 'FALSE',
    @rmtuser = '<ADMINLOGIN>', @rmtpassword = '<ADMINPASSWORD>'
    
    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.
    • Replace <ORASRV> with the name of new linked server.
    • Replace <ORCL> with the data source name that is associated with the new linked server.
    • Replace <YourDomain\YourLogin> with your domain logon information.
    • Replace <ADMINLOGIN> with the replication user for Oracle publishing.
    • Replace <ADMINPASSWORD> with the password that is associated with <ADMINLOGIN>.
  2. 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>]
    
  3. Drop the new linked server. To do this, run the following code.
    exec sp_dropserver '<ORASRV>', 'droplogins'

Method 2

  1. 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:
    UPDATE HREPL_VERSION SET SQLSERVERVERSION  =
      'Microsoft SQL Server 2005 - 9.00. <xxxx.xx>'
    Note The placeholder <xxxx.xx> represents the @@VERSION value of the hotfix that is installed.
  2. 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
    • Replace <ORADB> with TNSNAME for accessing the Oracle database.
    • Replace <ADMINLOGIN> with the replication user for Oracle publishing.
    • Replace ADMINPASSWORD with the password that is associated with <ADMINLOGIN>.
    • Replace <xxxx.xx> with the numeric value that is returned by running the @@VERSION function in step 1 after you install the hotfix.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" 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.

Properties

Article ID: 922767 - Last Review: November 20, 2007 - Revision: 1.2
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
Keywords: 
kbexpertiseadvanced kbtshoot kbprb kbsql2005setup KB922767

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com