Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Error message when the Distribution Agent tries to apply to the initial snapshot to an Oracle 11g subscriber: “ORA-00932: inconsistent datatypes”

Error message when the Distribution Agent tries to apply to the initial snapshot to an Oracle 11g subscriber: “ORA-00932: inconsistent datatypes”

Symptoms

Consider the following scenario: You setup transactional replication from SQL Server 2005 to Oracle 11 G server. In this scenario, when delivering the initial snapshot to the subscriber, the distribution agent may receive an error message that is similar to the following:

Agent message code 932. ORA-00932: inconsistent datatypes; expected : INTERVAL DAY TO SECOND ; got: NUMBER

The details of the error message will be similar to the following:

ErrorText = 'ORA-00932: inconsistent datatypes; expected : INTERVAL DAY TO SECOND ; got: NUMBER'

Message: Replication-Replication Distribution Subsystem: agent <distribution agent name> failed. ORA-00932: inconsistent datatypes; expected : INTERVAL DAY TO SECOND ; got: NUMBERErrorId = 1403, SourceTypeId = 16

 ErrorCode = '932'

ErrorText = 'ORA-00932: inconsistent datatypes; expected : INTERVAL DAY TO SECOND ; got: NUMBER'

Category:NULL

Source: OraOLEDB

Number: 932

 

Note: The issue does not occur when setting up replication between

  • SQL Server 2005 and Oracle 10g
  • SQL Server 2008 Service Pack 1 and Oracle 11g

 

↑ Back to the top

Cause

The issue occurs because the SQL Server does not define any data mapping to Oracle 11g servers and hence the MSREPL7 table that gets created on the Oracle server replication setup has an erroneous schema.

If you do a desc on MSREPL7 on Oracle server, the schema will look as follows:

  pubsrv   NVARCHAR2(128)              

  pubdb    NVARCHAR2(128)              

  publcn   NVARCHAR2(128)              

  indagent NUMBER(1)                 

  subtype  INTERVAL DAY(2) TO SECOND(6)

  dstagent NVARCHAR2(100)              

  timecol  TIMESTAMP(6)                

  descr    NVARCHAR2(255)              

  xactts   BINARY FILE LOB

  updmode  INTERVAL DAY(2) TO SECOND(6)

  agentid  BINARY FILE LOB

  subguid  BINARY FILE LOB             

  subid    BINARY FILE LOB             

  immsync  NUMBER(1)       

 

The schema should have been created as follows:

    pubsrv   NVARCHAR2(128)              

  pubdb    NVARCHAR2(128)              

  publcn   NVARCHAR2(128)              

  indagent NUMBER(1)                 

  subtype  NUMBER(10)

  dstagent NVARCHAR2(100)              

  timecol  DATE                

  descr    NVARCHAR2(255)              

  xactts   RAW(16)

  updmode  NUMBER(3)

  agentid  RAW(16)

  subguid  RAW(16)

  subid    RAW(16)

  immsync  NUMBER(1)

 

↑ Back to the top

Resolution

 

You can use the following procedure to resolve the problem.

  1. Drop your publication.
  2. Install SQL Server 2005 Service Pack 3 together with cumulative update 3.

·         For more information on obtaining the latest service pack for SQL Server 2005, click the following article number to view the article in Microsoft Knowledge base;

913089  How to obtain the latest service pack for SQL Server 2005

 

·         For more information about cumulative update package 3, click the following article number to view the article in the Microsoft Knowledge Base:

967909 Cumulative update package 3 for SQL Server 2005 Service Pack 3.

Note: Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

960598 The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 3 was released

Microsoft SQL Server 2005 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 3 hotfix to an installation of SQL Server 2005 Service Pack 3. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack. Manually drop and then re-create the data mapping table for replication by running the following stored procedures.

 

 

    3.   Manually drop and then re-create the data mapping table for replication by running the following stored procedures

use msdb

go

exec sp_MSrepl_DropDatatypeMappings

go

exec sp_MSrepl_CreateDatatypeMappings

go

 

  4.  Recreate your publication and subscription.

↑ Back to the top

More Information

 ·         960574  FIX: Error message when you run the Snapshot Agent for a transactional replication that uses an Oracle 11g publisher in SQL Server 2005 or in SQL Server 2008: "Msg 21613, Level 16, State 1, Procedure sp_IHsyncmetadata, Line 223"

↑ Back to the top

Keywords: vkball, kb

↑ Back to the top

Article Info
Article ID : 2000383
Revision : 1
Created on : 1/8/2017
Published on : 1/18/2011
Exists online : False
Views : 315