No data found error

  • newbieuser

    SSCrazy Eights

    Points: 9413

    Hi all,

    We get the below error when using order by clause in the select query using the linked server to Oracle table (LS..USER2.UNIT3)

    SELECT U.ABO,U.LED_ID, U.PROJECT_NO, T.WORDC, P.CHECKC, P.PIN_NO,

    FROM unit1 U, unit2 L, LS..USER2.UNIT3 P, unit4 T

    WHERE

    U.ABO like 'LE%'

    AND U.CREATE_DT = '18/01/2009'

    AND U.ABO = L.ABO

    AND L.CT_ID = P.CT_ID AND U.PCT = T.ACT AND U.FJT_TYP = T.FJT_TYP

    ORDER BY U.ABO, U.LED_ID

    Error:

    OLE DB provider "ORAOLEDB.ORACLE" for linked server "ls" 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 "ls".

    But the query is successful if we comment out the order by clause & also if we remove the U.CREATE_DT condition in the where clause..

    I'm not sure what we are missing.. Please help.

    Thanks

  • Lowell

    SSC Guru

    Points: 323321

    oops i thought the issue might be how Oracle is less forgiving than SQL when it comes to conversions from string to DAteTime, but most of what you qare doing is executed locally.

    are any of the columns coming from the linked server CLOB/BLOBS? some drivers don't bring them over nicely,

    here's your SQL re-written to use the updated ANSI format with JOIN syntax:

    SELECT

    U.ABO,

    U.LED_ID,

    U.PROJECT_NO,

    T.WORDC,

    P.CHECKC,

    P.PIN_NO,

    FROM unit1 U

    INNER JOIN unit2 L

    ON U.ABO = L.ABO

    INNER JOIN LS..USER2.UNIT3 P

    ON L.CT_ID = P.CT_ID

    INNER JOIN unit4 T

    ON U.PCT = T.ACT

    AND U.FJT_TYP = T.FJT_TYP

    WHERE U.ABO like 'LE%'

    --AND U.CREATE_DT = TO_DATE('18/01/2009' ,'DD/MM/YYYY')

    AND U.CREATE_DT ='2009-01-18'

    ORDER BY U.ABO, U.LED_ID

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • newbieuser

    SSCrazy Eights

    Points: 9413

    Thanks a lot for your reply.

    We don't have any CLOB/BLOBS columns in the oracle tables.. Since our application code is already written and we are testing the linked server with this code, we are trying as much not to change the queries so it reduces huge amount of time altering all the queries in the code.. Is there anything else that we could do to make this work without altering the query? Like may be change the format etc..

    SELECT U.ABO,U.LED_ID, U.PROJECT_NO, T.WORDC, P.CHECKC, P.PIN_NO,

    FROM unit1 U, unit2 L, LS..USER2.UNIT3 P, unit4 T

    WHERE

    U.ABO like 'LE%'

    AND U.CREATE_DT between '01/18/2009' and '04/20/2009'

    AND U.ABO = L.ABO

    AND L.CT_ID = P.CT_ID AND U.PCT = T.ACT AND U.FJT_TYP = T.FJT_TYP

    ORDER BY U.ABO, U.LED_ID

    The above query & the query that you posted using the inner join works only if I remove the order by clause or if I remove one of the where clause condition. Is this a limitation in linked server?

    Thanks again.

  • PaulB-TheOneAndOnly

    SSC Guru

    Points: 66773

    Out of curiosity, what happens is you execute very same offending query as OPENQUERY()?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • newbieuser

    SSCrazy Eights

    Points: 9413

    I tried the sql using openquery but I'm not getting it right..

    SELECT * from OPENQUERY(LS, 'SELECT * FROM U.ABO,

    U.LED_ID,

    U.PROJECT_NO,

    T.WORDC,

    P.CHECKC,

    P.PIN_NO,

    FROM unit1 U

    INNER JOIN unit2 L

    ON U.ABO = L.ABO

    INNER JOIN LS..USER2.UNIT3 P

    ON L.CT_ID = P.CT_ID

    INNER JOIN unit4 T

    ON U.PCT = T.ACT

    AND U.FJT_TYP = T.FJT_TYP

    WHERE U.ABO like 'LE%'

    --AND U.CREATE_DT = TO_DATE('18/01/2009' ,'DD/MM/YYYY')

    AND U.CREATE_DT ='2009-01-18'

    ORDER BY U.ABO, U.LED_ID')

    Getting lot of syntax errors.. can you help me please. Also, since the query works if I remove either the order by clause or one of the where condition, could it be something to do with the ORAOLEDB we use in ODBC? like a setting to be changed?

    Thanks.

  • PaulB-TheOneAndOnly

    SSC Guru

    Points: 66773

    newbieuser (5/10/2011)


    I tried the sql using openquery but I'm not getting it right..

    SELECT * from OPENQUERY(LS, 'SELECT * FROM U.ABO,

    U.LED_ID,

    U.PROJECT_NO,

    T.WORDC,

    P.CHECKC,

    P.PIN_NO,

    FROM unit1 U

    INNER JOIN unit2 L

    ON U.ABO = L.ABO

    INNER JOIN LS..USER2.UNIT3 P

    ON L.CT_ID = P.CT_ID

    INNER JOIN unit4 T

    ON U.PCT = T.ACT

    AND U.FJT_TYP = T.FJT_TYP

    WHERE U.ABO like 'LE%'

    --AND U.CREATE_DT = TO_DATE('18/01/2009' ,'DD/MM/YYYY')

    AND U.CREATE_DT ='2009-01-18'

    ORDER BY U.ABO, U.LED_ID')

    Getting lot of syntax errors.. can you help me please. Also, since the query works if I remove either the order by clause or one of the where condition, could it be something to do with the ORAOLEDB we use in ODBC? like a setting to be changed?

    Please post table structures as well as Oracle side error messages.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • nick.mcdermaid

    Hall of Fame

    Points: 3668

    I think Pablo means use OPENQUERY on just the Oracle table, not all tables - it definitely won't work on all tables as it can't see the SQL tables when it runs remotely on the Oracle srever.... also FYI, you need to escape-quote your OPENQUERY string - note that your dates and stuff arent coloured correctly when syntax highlighted.

    So try this:

    SELECT U.ABO, U.LED_ID, U.PROJECT_NO, T.WORDC, P.CHECKC, P.PIN_NO

    FROM unit1 U

    INNER JOIN unit2 L

    ON U.ABO = L.ABO

    INNER JOIN

    (

    SELECT CT_ID, CHECKC, PIN_NO FROM

    OPENQUERY(LS,'SELECT CT_ID, CHECKC, PIN_NO FROM USER2.UNIT3')

    ) P

    ON L.CT_ID = P.CT_ID

    INNER JOIN unit4 T

    ON U.PCT = T.ACT

    AND U.FJT_TYP = T.FJT_TYP

    WHERE U.ABO like 'LE%'

    AND U.CREATE_DT = '2009-01-18'

    Looking at the Oracle error info, maybe its trying to do something smart for thel inked server- but it's not smart enough.

    Also I have removed the ORDER BY for now to help isolate the problem.

    Does it work if you use a different value for U.CREATE_DT?

  • wolfkillj

    SSCrazy Eights

    Points: 9772

    newbieuser (5/10/2011)


    Hi all,

    We get the below error when using order by clause in the select query using the linked server to Oracle table (LS..USER2.UNIT3)

    SELECT U.ABO,U.LED_ID, U.PROJECT_NO, T.WORDC, P.CHECKC, P.PIN_NO,

    FROM unit1 U, unit2 L, LS..USER2.UNIT3 P, unit4 T

    WHERE

    U.ABO like 'LE%'

    AND U.CREATE_DT = '18/01/2009'

    AND U.ABO = L.ABO

    AND L.CT_ID = P.CT_ID AND U.PCT = T.ACT AND U.FJT_TYP = T.FJT_TYP

    ORDER BY U.ABO, U.LED_ID

    Error:

    OLE DB provider "ORAOLEDB.ORACLE" for linked server "ls" 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 "ls".

    But the query is successful if we comment out the order by clause & also if we remove the U.CREATE_DT condition in the where clause..

    I'm not sure what we are missing.. Please help.

    Thanks

    Would the solution of inserting the Oracle data into a temp table, then joining the temp table, work for you, as suggested (by someone much smarter about SQL Server than me) to solve a similar problem here:

    http://www.sqlservercentral.com/Forums/Topic538856-149-1.aspx#bm539530

    Jason Wolfkill
    Blog: SQLSouth
    Twitter: @SQLSouth

  • wolfkillj

    SSCrazy Eights

    Points: 9772

    newbieuser (5/10/2011)


    Hi all,

    We get the below error when using order by clause in the select query using the linked server to Oracle table (LS..USER2.UNIT3)

    SELECT U.ABO,U.LED_ID, U.PROJECT_NO, T.WORDC, P.CHECKC, P.PIN_NO,

    FROM unit1 U, unit2 L, LS..USER2.UNIT3 P, unit4 T

    WHERE

    U.ABO like 'LE%'

    AND U.CREATE_DT = '18/01/2009'

    AND U.ABO = L.ABO

    AND L.CT_ID = P.CT_ID AND U.PCT = T.ACT AND U.FJT_TYP = T.FJT_TYP

    ORDER BY U.ABO, U.LED_ID

    Error:

    OLE DB provider "ORAOLEDB.ORACLE" for linked server "ls" 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 "ls".

    But the query is successful if we comment out the order by clause & also if we remove the U.CREATE_DT condition in the where clause..

    I'm not sure what we are missing.. Please help.

    Thanks

    Also, this may be a driver issue. If your SQL Server is running on 64-bit hardware, you should check to be sure that you're using the 64-bit Oracle OLEDB/ODBC drivers for SQL Server. I've also heard that having both the 32-bit and 64-bit versions installed can cause such problems. Then make sure that you have the latest versions of the drivers.

    Jason Wolfkill
    Blog: SQLSouth
    Twitter: @SQLSouth

  • penny.hannaford

    SSC Journeyman

    Points: 76

    Hi

    I have the same error, in a bit of code that worked perfectly yesterday....!

    This code fails >

    SELECT @SqlString = 'UPDATE tblVATGLDetails SET VGLDNetFigure = NetFigure

    FROM tblVATGLDetails VATGL

    INNER JOIN OPENQUERY(' + @server + ', ''

    SELECT SUM(CAST(FE.DebitBase - FE.CreditBase AS FLOAT)) AS NetFigure

    ,CAST(FE.TransactionNumber AS VARCHAR(50)) AS MacTransactionNumber

    FROM ' + @dbname + '.FinanceEntry FE

    WHERE AccountNumber = ''''30000''''

    GROUP BY FE.TransactionNumber

    '')

    ON VGLDTransactionNumber = MacTransactionNumber

    WHERE VGLDEntryDate BETWEEN @fromdate AND @todate

    '

    EXEC sp_executesql @SqlString, @params, @StartDate, @EndDate

    But if I change it to do a select rather than an Insert as below it works! Any ideas?

    SELECT @SqlString = 'SELECT NetFigure, VGLDTransactionNumber

    FROM tblVATGLDetails VATGL

    INNER JOIN OPENQUERY(' + @server + ', ''

    SELECT SUM(CAST(FE.DebitBase - FE.CreditBase AS FLOAT)) AS NetFigure

    ,CAST(FE.TransactionNumber AS VARCHAR(50)) AS MacTransactionNumber

    FROM ' + @dbname + '.FinanceEntry FE

    WHERE AccountNumber = ''''30000''''

    GROUP BY FE.TransactionNumber

    '')

    ON VGLDTransactionNumber = MacTransactionNumber

    WHERE VGLDEntryDate BETWEEN @fromdate AND @todate

    '

    EXEC sp_executesql @SqlString, @params, @StartDate, @EndDate

    Thanks for any help!

    P.

  • corderoj

    Grasshopper

    Points: 23

    I also had code working perfectly yesterday and errors today. I have a view pulling data from oracle with an openquery, and a stored procedure using the view with SQL tables. The SP works without the where clause, but errors with "No data found" when the where clause is included. I ended up dumping the view into a temp table first, then using the temp table in the stored procedure and that works. It looks like a problem with date formatting, but who knows why it worked yesterday and not today???

  • Welsh Corgi

    SSC Guru

    Points: 116520

    You should not get an error but you should include an exception Handler to Include WHEN NO DATA FOUND, perform an action.

    Take you select statement and look at what is in the WEHRE Clause.

    Examine the Data closely and determine what in the Where Clause is causing no data to be returned.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Jo Pattyn

    SSC-Dedicated

    Points: 31255

    For newbieuser: convert '18/01/2009' to a proper datetype instead of relying on the occasional dateformat of the server. By using convert?

    For penny: have you checked the distiributed transaction coordinator security? (articles on the web)

    Is there an Oracle MTS (multithreadedtransaction)-service runningn, so MS DTC can communicate with it

  • helensingleton84

    SSC Journeyman

    Points: 75

    OPENQUERY on just the Oracle table.

  • Swashbuckler

    SSC Enthusiast

    Points: 196

    newbieuser (5/10/2011)


    I tried the sql using openquery but I'm not getting it right..

    SELECT * from OPENQUERY(LS, 'SELECT * FROM U.ABO,

    U.LED_ID,

    U.PROJECT_NO,

    T.WORDC,

    P.CHECKC,

    P.PIN_NO,

    FROM unit1 U

    INNER JOIN unit2 L

    ON U.ABO = L.ABO

    INNER JOIN LS..USER2.UNIT3 P

    ON L.CT_ID = P.CT_ID

    INNER JOIN unit4 T

    ON U.PCT = T.ACT

    AND U.FJT_TYP = T.FJT_TYP

    WHERE U.ABO like 'LE%'

    --AND U.CREATE_DT = TO_DATE('18/01/2009' ,'DD/MM/YYYY')

    AND U.CREATE_DT ='2009-01-18'

    ORDER BY U.ABO, U.LED_ID')

    Getting lot of syntax errors.. can you help me please. Also, since the query works if I remove either the order by clause or one of the where condition, could it be something to do with the ORAOLEDB we use in ODBC? like a setting to be changed?

    Thanks.

    Not sure what your errors are, but when using OPENQUERY you need to escape your quotes by double (single) quoting. E.g. WHERE U.ABO like ''LE%''

Viewing 15 posts - 1 through 15 (of 16 total)

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