Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

No data found error Expand / Collapse
Author
Message
Posted Tuesday, May 10, 2011 7:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:06 PM
Points: 319, Visits: 757
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
Post #1106110
Posted Tuesday, May 10, 2011 8:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:07 PM
Points: 12,910, Visits: 32,028
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1106167
Posted Tuesday, May 10, 2011 8:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:06 PM
Points: 319, Visits: 757
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.
Post #1106175
Posted Tuesday, May 10, 2011 10:55 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1106354
Posted Tuesday, May 10, 2011 11:18 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:06 PM
Points: 319, Visits: 757
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.
Post #1106374
Posted Friday, July 1, 2011 2:18 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1135374
Posted Tuesday, January 24, 2012 5:45 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 178, Visits: 574
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?
Post #1241303
Posted Wednesday, January 25, 2012 1:49 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:05 AM
Points: 1,222, Visits: 2,545
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
Post #1241766
Posted Wednesday, January 25, 2012 1:58 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:05 AM
Points: 1,222, Visits: 2,545
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
Post #1241775
Posted Wednesday, March 7, 2012 6:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 28, 2014 3:07 AM
Points: 2, Visits: 49
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.
Post #1262908
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse