OLE_DB OPENQUERY error with linked Oracle Server

• Paul S. Huebner

  Right there with Babe

  Points: 719

  More actions

  October 15, 2008 at 1:29 pm

  #199865

  Greetings,

  I am having trouble with running an OPENQUERY query. This query executes on a 64-bit, MS-WinServer 2003 Stnd Ed., SP2 platform. The SQL Server edition is 2005 Enterprise Ed., SP2. It links using OLE_DB to an Oracle server. The Oracle server is version 10g.

  This query works:

  SELECT * FROM OPENQUERY ([ORA_ILFRZ-WMS],

  'SELECT

  PARM4 AS error_code

  ,PARM3 AS chg_qty

  ,PARM1 AS lp

  ,PARM2 AS sku

  ,DATE_RAN AS Adjustment_Date

  FROM DOT_SQL_TRACK

  where SCRIPT_NAME = ''dock_error.sql''

  and parm5 is not null

  and parm6 is not null

  and length(parm3) < 4')

  This query does not:

  select * FROM OPENQUERY ([ORA_ILFRZ-WMS],

  'SELECT

  PARM4 AS error_code

  ,PARM3 AS chg_qty

  ,PARM1 AS lp

  ,PARM2 AS sku

  ,DATE_RAN AS Adjustment_Date

  ,PARM5 as ORDER_CNTRL_NO

  ,PARM6 AS ORDER_LINE_NO

  FROM DOT_SQL_TRACK

  where SCRIPT_NAME = ''dock_error.sql''

  and parm5 is not null

  and parm6 is not null

  and length(parm3) < 4')

  As you can see, the only difference is leaving off 2 columns (PARM5 as ORDER_CNTRL_NO, PARM6 AS ORDER_LINE_NO). I am very baffled by this. Please, your help is appreciated.

  Sorry, here is the error:

  Msg 7399, Level 16, State 1, Line 1

  The OLE DB provider "OraOLEDB.Oracle" for linked server "ORA_ILFRZ-WMS" reported an error. The provider reported an unexpected catastrophic failure.

  Msg 7321, Level 16, State 2, Line 1

  An error occurred while preparing the query "SELECT

  PARM4 AS error_code

  ,PARM3 AS chg_qty

  ,PARM1 AS lp

  ,PARM2 AS sku

  ,DATE_RAN AS Adjustment_Date

  ,PARM5 as ORDER_CNTRL_NO

  ,PARM6 AS ORDER_LINE_NO

  FROM DOT_SQL_TRACK

  where SCRIPT_NAME = 'dock_error.sql'

  and parm5 is not null

  and parm6 is not null

  and length(parm3) < 4" for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "ORA_ILFRZ-WMS".

• TryingToLearn

  SSCrazy

  Points: 2646

  More actions

  April 30, 2015 at 7:08 am

  #1794661

  was a fix ever found? I am having the same problem with SQL 2008r2