May 10, 2011 at 7:38 am
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
May 10, 2011 at 8:25 am
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
May 10, 2011 at 8:39 am
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.
May 10, 2011 at 10:55 am
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.May 10, 2011 at 11:18 am
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.
July 1, 2011 at 2:18 pm
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.January 24, 2012 at 5:45 pm
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?
January 25, 2012 at 1:49 pm
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
January 25, 2012 at 1:58 pm
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
March 7, 2012 at 6:04 am
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.
March 26, 2013 at 8:00 am
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???
March 26, 2013 at 8:16 am
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/
June 15, 2014 at 12:37 pm
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)
February 8, 2015 at 9:14 pm
OPENQUERY on just the Oracle table.
May 23, 2016 at 10:34 pm
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 15 total)
You must be logged in to reply to this topic. Login to reply