SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ORA-00932: inconsistent datatypes: expected FILE got BINARY when Initializing SQL 2k8R2 Publication...


ORA-00932: inconsistent datatypes: expected FILE got BINARY when Initializing SQL 2k8R2 Publication to Oracle 12c Sub

Author
Message
raz.the.cat
raz.the.cat
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 62
I'm attempting to setup a Non-SQL subscriber (Oracle 12c) on a SQL 2k8R2 instance. Once I setup the subscriber and the agent attempts to start replicating between the SQL and 12c server, I get the ORA-00932: inconsistent datatypes: expected FILE got BINARY error. The agent will create the MSREPL7 table on the Oracle instance so I know there connection is good and the credentials being used have permission to the Oracle instance.

I've tried changing the data type on the MSREPL7 table on the Oracle server to RAW for anything that was BFILE as a shot in the dark (new to Oracle) but that didn't help. Just flipped the expected/got error.

Has anyone here run into this issue? What am I missing?


SQL Version - Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Oracle version - -------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
PL/SQL Release 12.1.0.1.0 - Production 0
CORE 12.1.0.1.0 Production 0
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production 0
NLSRTL Version 12.1.0.1.0 - Production
dataonfocus
dataonfocus
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 14
Similar issue here, anyone knows?

Thanks
dataonfocus
dataonfocus
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 14
Anyone??
@Ashish
@Ashish
Old Hand
Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)

Group: General Forum Members
Points: 325 Visits: 327
Similar Issue here. Did you able to solve your problem?

_________

Regards,
Ashish
SQLKnight
SQLKnight
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 74
The below has worked for me. You can give it a try in your lower environments if it permits.

https://dbamohsin.wordpress.com/category/sql-server/page/16/


Thanks
@Ashish
@Ashish
Old Hand
Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)

Group: General Forum Members
Points: 325 Visits: 327
I contacted Microsoft support. They agree that it does have to do with Oracle12c upgrade and provided workaround for the issue.

FROM DAC Connection:

Workaround to add oracle publisher support w/o new datatype support:
exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '12'
exec sys.sp_MSrepl_ORA_MSSQL_datatypemappings @source_dbms = N'ORACLE', @source_version = '12', @destination_dbms = N'MSSQLServer'

Warkaround to add oracle subscriber support w/o new datatype support:
exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '12'
exec sys.sp_MSrepl_MSSQL_ORA_datatypemappings @source_dbms = N'MSSQLSERVER', @destination_dbms = N'ORACLE', @destination_version = '12'


---

This worked for me and hopefully will help somebody else too!

Thanks!

_________

Regards,
Ashish
cbrown 29958
cbrown 29958
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 17
Hello,

I am running SQL Server 2008 (SP4) - 10.0.6000.29 (X64). I am experiencing a problem attempting to create a replication article with Oracle 12c as Publisher. The workaround solution mentioned in this thread requires 2 stored procedures below.

1. sys.sp_MSrepl_ORAdatatypes
2. sys.sp_MSrepl_ORA_MSSQL_datatypemappings

I receive the following errors when I execute these procedures. Where are these procedures?

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sys.sp_MSrepl_ORAdatatypes'.
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sys.sp_MSrepl_MSSQL_ORA_datatypemappings'.
Ron Clarke
Ron Clarke
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3213 Visits: 885
Most likely you were not trying to run the stored procedures from a DAC connection. Lookup DAC in SQL Server Books On Line for more information.


Have Fun!
Ronzo

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search