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

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

• The original release version of SQL Server 2005
• SQL Server 2005 Service Pack 1 (SP1)

Back to the top

Back to the top

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'

Back to the top

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.

Back to the top