OPENQUERY NOT RETURNING ALL AVAILABLE ROWS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

OPENQUERY NOT RETURNING ALL AVAILABLE ROWS

Author

Topic

dpdesi
Starting Member

5 Posts

Posted - 2007-03-20 : 06:18:22

I'm getting a problem when I run this this query using SQL Server 2000 the code:

SELECT * FROM OPENQUERY(EZLIVE, 'SELECT MNME.NM_TYPE,
MTRCK.TR_US_US,
LTRACKINGSTATUS.TRST_DESC,
LTRST.TC_DESC,
MNME.NM_FNAME,
MNME.NM_LNAME,
LPOST.TT_DESC,
MCMP.BS_NAME,
MTRCK.TR_FROM_TIME

FROM MTRCK,
MNME,
LTRACKINGSTATUS,
LTRST,
MCMP,
LPOST
WHERE MTRCK.TR_PC_NM = MNME.NM_IDNO
AND MTRCK.TR_STATUS_TRST = LTRACKINGSTATUS.TRST_IDNO
AND MTRCK.TR_TC_TC = LTRST.TC_IDNO
AND MNME.NM_EM_BS = MCMP.BS_IDNO
AND MNME.NM_ET_TT = LPOST.TT_IDNO
AND (LTRACKINGSTATUS.TRST_DESC = ''COMPLETE''
OR LTRACKINGSTATUS.TRST_DESC = ''OPEN'')
AND (LTRST.TC_DESC = ''MEETING'')
AND (TRUNC(MTRCK.TR_FROM_TIME) = TRUNC(SYSDATE)-1)
')

this is only retrieving the top row, however if I run the exact same sql inside the Query builder in DTS it retrieves all the rows. Please can someone shed some light on this matter. What am I doing wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-20 : 06:24:33

What kind of function is TRUNC?
I don't believe it is MS SQL Server...

Peter Larsson
Helsingborg, Sweden

dpdesi
Starting Member

5 Posts

Posted - 2007-03-20 : 06:26:53

I forgot to mention this is Querying an Oracle database.

dpdesi
Starting Member

5 Posts

Posted - 2007-03-20 : 06:28:25

The linked server is an Oracle database is what I mean

dpdesi
Starting Member

5 Posts

Posted - 2007-03-21 : 12:00:15

IS ANYBODY going to attempt to help me with this, I've googled this all day, and every person who has logged a similar problem on other forums have all had no replies also, is there not anybody out there capable of solving this problem???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 12:29:52

Maybe you should try WWW.DBFORUMS.COM since it appears to be an ORACLE issue.

Peter Larsson
Helsingborg, Sweden

dpdesi
Starting Member

5 Posts

Posted - 2007-03-21 : 12:37:24

Hi,
thanks for the reply, however I'm not sure it is an Oracle issue the query runs fine in SQL Server DTS it is only when the I use the Query inside an OPENQUERY/OPENDATASOURCE or OPENROWSET statement it only returns the top row. The OPENQUERY is communicating with a DSN that I have set up as a linked server...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources