Query with linked server works until where clause added

Question

Post reply

Query with linked server works until where clause added

Fred Schmid

Mr or Mrs. 500

Points: 517
More actions
December 18, 2014 at 2:26 pm

#313187

I have a query:
select a.* b.description
from sqlservertable a
left outer join
oracleparttable b
on a.partid = b.partid;
This runs with no problems but when I add a where clause it breaks.
select a.* b.description
from sqlservertable a
left outer join
oracleparttable b
on a.partid = b.partid
where a.date = '2014-12-18';
Error Message:
OLE DB provider "OraOLEDB.Oracle" for linked server "XXXORACLE" 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 "XXXORACLE".
If I run the query without the linked server but include the where clause it works.
select *
from sqlservertable
where date = '2014-12-18';
Any ideas what might be causing my problem?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Shafat Husain SSCrazy Points: 2754 More actions · Answer 1

Dear Fred,

Have you tried using Openquery??

Select * from Openquery ([Linked Server Name], 'Select * from a.table name.....')

Regards

Shafat Husain

Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)

Fred Schmid Mr or Mrs. 500 Points: 517 More actions · Answer 2

Hi Shafat,

Yes, I have tried OPENQUERY and it does not fix the problem. The sub query that contains the statement works fine but when it is joined to the rest of the query with the WHERE clause it produces the same error.

Fred

Lowell SSC Guru Points: 323518 More actions · Answer 3

can you try explicitly using a datetime variable? i'm wondering if it's the implicit conversion of a string to datetime that is the issue:

select a.* b.description

from sqlservertable a

left outer join

oracleparttable b

on a.partid = b.partid

where a.date = CONVERT(datetime,'2014-12-18');

--OR

DECLARE @TheDate datetime ='2014-12-18'

select a.* b.description

from sqlservertable a

left outer join

oracleparttable b

on a.partid = b.partid

where a.date =@TheDate

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!

Fred Schmid Mr or Mrs. 500 Points: 517 More actions · Answer 4

Yes, I have tried declaring a variable of DATE type to match the SQL Server table field type, but this did not fix the problem.

Fred Schmid Mr or Mrs. 500 Points: 517 More actions · Answer 5

Strange.

Last week my queries weren't working but this week they are. As far as I can tell there have been no changes in the environments.

What would cause the error temporarily? I have two network SQL Server servers. One is the development environment and the other is production. Both work now.

The variable did not make a difference. I can write the code either way (with or without variables) and it works now.

Lowell SSC Guru Points: 323518 More actions · Answer 6

i've had situations where my linked servers odbc drivers get locked up/doinked up in the past, i think related to open transactions in the distributed transaction coordinator,and the only thing that cleared the issue was stopping and starting the SLQ service(which rolled back any pending distributed transactions)

maybe that's what happened here.

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!

mzaidi SSC Journeyman Points: 79 More actions · Answer 7

Hi,

I have a similar issue does any one have a solution yet?

I am trying inner join and my issue is similar when i introduce the where clause i get the error.

The strange thing is we have another db mocked for another developer and the same query works there. schema for table is same checked with redgate. data has some minor differences in date fields on few columns on sql table side. but with this query i even omitted those fields to not to be included but still have the problem.

OLE DB provider "OraOLEDB.Oracle" for linked server "abc" 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 "abc".

select * from (select

[ApEmail]

,[FirstName]

,[LastName]

,[UEmail]

,[PEmail]

,[SCode] from MUser ) m

inner join

(select * from OPENQUERY(abc, 'SELECT AY, JN, APID, APUNAME, EMAIL, FIRST_NAME, LAST_NAME, APSTATUS FROM abc.DATA')) r

on m.ApEmail = r.Email or m.PEmail = r.Email or m.UEmail = r.Email

where m.SCode = 'ns'

rogerwilcoperkins SSC Rookie Points: 25 More actions · Answer 8

When joining an OPENQUERY, you do not need to include the OPENQUERY in a sub query, try this:

select

[ApEmail]

,[FirstName]

,[LastName]

,[UEmail]

,[PEmail]

,[SCode]

,r.AY, r.JN, r.APID, r.APUNAME, r.EMAIL, r.FIRST_NAME, r.LAST_NAME, r.APSTATUS

from MUser inner join

OPENQUERY(abc, 'SELECT AY, JN, APID, APUNAME, EMAIL, FIRST_NAME, LAST_NAME, APSTATUS FROM abc.DATA') r

on ApEmail = r.Email or PEmail = r.Email or UEmail = r.Email

where SCode = 'ns'

As for the other people with the issue of the No Data error message for openquery used as a sub query, I believe that the sql server may be running the sub query for the first record, then the following records find no data when trying to link back to the records retrieve by the first record, to test, add Top 1 after select, as Select Top 1 ..., that should work, where as, Select Top 2 does not work and give the No Data error message.

Update:

For future reference I discovered in my own query with multiple tables and a subquery that selects from a linked server via openquery, that in the main query FROM clause (even though I'm using mostly inner joins) if I move the tables around and select from the most narrowing table first (eg. selecting from transactions table first when I'm only selecting transactions from one day), then the 'No Data' error message is resolved (ie. no more error).

Update 2:

Well I finally figured it out :w00t: I stumbled on the solution when I was refining a crystal report with a sql expression field to a linked server connection, which was working fine until I removed one of the tables from the primary query, crazy as this sounds removing that table made the primary query run faster and when it was time to retrieve the data from the linked server the 'No Data' error message was displayed, apparently if the linked server connection data is not ready by the time the primary query needs the data, then instead of waiting for the data it will assume there is no data and give the 'No Data' error message which causes the whole report to blow up. Adding that table back in (with an enforce clause in crystal) even though I was not selecting any data from that table, made the report run about twice as long and resolved the 'No Data' error message. This also helps to make sense out of an issue with the report when the server was upgraded, before the upgrade the report ran just fine, after the upgrade the report started giving the 'No Data' error message, apparently the report was running much faster on the new server and that caused the data from the linked server to not be ready in time. Hopefully this helps someone, it will certainly help me in the future. 😀

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 9

Shot in the dark but I'm pretty sure that anything having to do with Oracle parts of the query must all be in upper case.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cyp901 Mr or Mrs. 500 Points: 539 More actions · Answer 10

Have you checked the linked server collation settings? Perhaps you could also manually collate to the collation defined on the Oracle database.

It does seem like an issue related to collation since the problem only happens when you try to filter the result set.