1 Reply Latest reply on Aug 12, 2008 12:48 PM by 432022

    Querying Oracle from MS SQL 2005

    432022
      I am having a strange problem while running an SQL statement in MS SQL 2005 that uses a linked Oracle server. I am getting the following error:

      OLE DB provider "OraOLEDB.Oracle" for linked server "OG" returned message "ORA-01403: no data found".
      Msg 7346, Level 16, State 2, Line 1
      Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "OG"

      I am getting the error on the following SQL statement (OG is the Oracle server):

      SELECT Inv.AreaNo, Inv.CatType, Inv.CatNo, Inv.ItemNo, Inv.NumAvail, Inv.NumTotal, Inv.Active,
      IH.AID, IH.NumOut, IH.DateOut, IH.WorkerOut, IH.NumIn, IH.DateIn, IH.WorkerIn,
      CC.CatID, CC.CatName, CC.BinItem, CC.Active, OG.LASTNAME, OG.FIRSTNAME, OG.MIDDLENAME
      FROM ItemInventory AS Inv
      LEFT JOIN CatCodes AS CC ON Inv.AreaNo = CC.AreaNo AND Inv.CatType = CC.CatType AND Inv.CatNo = CC.CatNo
      LEFT JOIN ItemHistory AS IH ON Inv.AreaNo = IH.AreaNo AND Inv.CatType = IH.CatType AND Inv.CatNo = IH.CatNo AND
      Inv.ItemNo = IH.ItemNo
      LEFT JOIN OGL..D.LE AS OG ON AID = OG.PK
      WHERE Inv.AreaNo = 0 AND Inv.CatType = 0

      What's strange is that the following SQL statement works fine. The only difference between table ItemHistory (above) and ItemsOut (below) is that ItemHistory adds 3 columns that ItemsOut does not have. Other than that table the SQL statements are the same (as you can see).

      SELECT Inv.AreaNo, Inv.CatType, Inv.CatNo, Inv.ItemNo, Inv.NumAvail, Inv.NumTotal, Inv.Active,
      IO.AID, IO.NumOut, IO.DateOut, IO.WorkerOut, '1' AS NumIn, '1/1/1900' AS DateIn, '1' AS WorkerIn,
      CC.CatID, CC.CatName, CC.BinItem, CC.Active, OG.LASTNAME, OG.FIRSTNAME, OG.MIDDLENAME
      FROM ItemInventory AS Inv
      LEFT JOIN CatCodes AS CC ON Inv.AreaNo = CC.AreaNo AND Inv.CatType = CC.CatType AND Inv.CatNo = CC.CatNo
      LEFT JOIN ItemsOut AS IO ON Inv.AreaNo = IO.AreaNo AND
      Inv.CatType = IO.CatType AND Inv.CatNo = IO.CatNo AND
      Inv.ItemNo = IO.ItemNo
      LEFT JOIN OGL..D.LE AS OG ON AID = OG.PK
      WHERE Inv.AreaNo = 0 AND Inv.CatType = 0

      As a test I tried just running an SQL statement against ItemsHistory and the Oracle table, thinking it might be something with ItemsHistory, but the following command also runs just fine:

      SELECT ItemHistory.*, OG.LASTNAME, OG.FIRSTNAME, OG.MIDDLENAME
      FROM ItemHISTORY
      LEFT JOIN OGL..D.LE AS OG ON AID = OG.PK

      I'm not sure why the one SQL statement is erroring out with ORA-01403. I see no problems with it, as it is the same as the one that works except using a different table, and another SQL statement using the table in the erroring SQL works also. I'm stumped!

      I am using the 'OraOLEDB.Oracle' provider in SQL 2005.

      Thanks for any help you can give on this...
        • 1. Re: Querying Oracle from MS SQL 2005
          432022
          I've been testing it, and I've found that if I comment out 4 fields from the field list (all from the same table), it runs. As soon as I add any one of the 4 fields back into the select list, I get the Oracle error. I also found that I had to change the Oracle table from a LEFT JOIN to just a JOIN, or else I also get the Oracle error.

          Here is the SQL (same as the previous post) with the 4 fields commented out, and the modified JOIN. Maybe that will give someone an idea, because it has me puzzled...

          SELECT Inv.AreaNo, Inv.CatType, Inv.CatNo, Inv.ItemNo, Inv.NumAvail, Inv.NumTotal, Inv.Active,
          IO.AID, IO.NumOut, IO.DateOut, IO.WorkerOut, IO.NumIn, IO.DateIn, IO.WorkerIn,
          OG.LASTNAME, OG.FIRSTNAME, OG.MIDDLENAME
          --, CC.CatID, CC.CatName, CC.BinItem, CC.Active
          FROM ItemInventory AS Inv
          LEFT JOIN CatCodes AS CC ON Inv.AreaNo = CC.AreaNo AND Inv.CatType = CC.CatType AND Inv.CatNo = CC.CatNo
          LEFT JOIN ItemHistory AS IO ON Inv.AreaNo = IO.AreaNo AND Inv.CatType = IO.CatType AND Inv.CatNo = IO.CatNo AND
          Inv.ItemNo = IO.ItemNo
          JOIN OGL..D.LE AS OG ON AID = OG.PK
          WHERE Inv.AreaNo = 0 AND Inv.CatType = 0