OLE_DB OPENQUERY error with linked Oracle Server

  • Paul S. Huebner

    SSChasing Mays

    Points: 609

    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: 2641

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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply