Empty Recordset From Linked Server

[Elysium]

Since the search function is not working on this site, I apologize if I am asking a question that has already been answered. Any way, here's my problem. I set up a linked server on SQL Server 7 to an Oracle db. When I expand the linked server to see the tables, everything is there. However, when I query a table that I know has data in it, all I get are the column headings and no data. What's going on here?

RS

 

[sguslan]

Please pot your query ! ________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[Elysium]

Here is the query I am using:

select TME.Week_End_Date, AD.Account_Num, AD.Account_Name, VNR.Package_Quantity
from Warehouse...Time as TME, Warehouse...Account_Detail as AD, Warehouse...UPS_Organization as Org, Warehouse...Volume_and_Revenue_TOTALS as VNR
where (TME.Week_End_Date = '2003-03-01') AND (AD.Account_Num LIKE '%006%') AND (Org.Region_Num = '04') AND (Org.District_Num = '74') AND (VNR.Customer_Shipment_Role_Cd = '03')

 

[JamesLean]

You haven't specified any JOIN criteria on the tables. You need to add how they are related, ie:

SELECT ...
FROM warehouse...time tme JOIN warehouse...account_detail ad ON tme.col1 = ad.col2
  JOIN warehouse...ups_organization org ON ad.col3 = org.col1
...

--James

 

[Elysium]

I am not so sure that it's a JOIN problem. I say this because when I query a single table that holds product types (it has no dependencies or joins), I get the same result: column headings with no data. Also, we are using HP's Intelligent Warehouse which defines all of the joins on the server side. We don't have to explicitly define the joins.

RS

 

[sguslan]

Elysium ,,,

Make sure you reference Oracle tables in FQN fashion.
For example, refer to warehouse...time as warehouse..schema.time, where schema is the owner name.

Test it and let us know !

Good luck ________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[Elysium]

sguslan,

Sorry for sounding ignorant, but the only way that I know how to find the owner of these oracle tables I have is to run sp_tables_ex. When I do, the 'Table_Schema' column has 'Null' in it for every table. Is there another way? I don't have admin rights to the data warehouse we have. Our corporate office has locked it up pretty tightly.

RS

 

[sguslan]

Well, in linked server to Oracle configuration, the Schema/Owner must have been set to some value. Find out from the linked server properties.
More, Oracle has Synonyms, so may be you are accessing some public synonyms. Try using Oracle's pre-configured user ids, i.e. "sys" or "system". ________________________________________________________________________________
If you do not like change, get out of the IT business...