Cannot see views in linked server connection

Who owns the view in Progress?

Hi Tom.  I'm working with Paul on this.  He's going to ask some questions instead of using me as a conduit.

Thomas,

I am working with bunggo on this issue. I don't believe there is any named owner of the table. There are no users set up in the Progress database (version 10.2B). Is that something we need to do in order to achieve connectivity via Linked Servers in SQL Server using ODBC?

Regards,
Paul Fry
+1 216.496.9915

Owner <> User.  Like all the regular Progress tables are owned by PUB.

Ok. So do I need to modify something in the _view records after I create the views? Here's a sample statement, executed from a ABL procedure editor window:

CREATE VIEW contact_vwagency (agncy_id, name, address, city_state, zipcd, add_date, add_user, mtc_date, mtc_termid, mtc_time, mtc_user, co_no, group_cd, type, skill_cat, phone, location, email, GM_Group, GM_coun_id) AS SELECT agncy-id, name, address, city-state, zipcd, add-date, add-user, mtc-date, mtc-termid, mtc-time, mtc-user, co-no, group-cd, type, skill-cat, phone, location, email, GM-Group, GM-coun-id FROM agency.

Here's the _view record which is created:

        Auth-Id: progressoe
      View-Name: CONTACT_VWAGENCY
    Base-Tables: AGENCY
      Where-Cls: ?
       Group-By: ?
       View-Def: VIEW CONTACT_VWAGENCY (AGNCY_ID, NAME, ADDRESS, CITY_STATE,
       Can-Read: progressoe
      Can-Write: *
     Can-Create: progressoe
     Can-Delete: progressoe
      Updatable: yes
           Desc:

Obviously I'm logged in as progressoe. How do I change ownership?

For what it's worth...

This query:

select * from openquery(testcon, 'select * from progressoe.contact_vwagency')

Generates this error:

OLE DB provider "MSDASQL" for linked server "testcon" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Table/View/Synonym not found (7519)".

Msg 7321, Level 16, State 2, Line 8

An error occurred while preparing the query "select * from progressoe.contact_vwagency" for execution against OLE DB provider "MSDASQL" for linked server "testcon".

I have verified the query is OK by doing:

select * from openquery(testcon, 'select * from sysprogress.systables')

I have tried a few other flavors too:

select * from openquery(testcon, 'select * from contact_vwagency')

select * from openquery(testcon, 'select * from PUB.contact_vwagency')

select * from openquery(testcon, 'select * from Contact.PUB.contact_vwagency')

select * from openquery(testcon, 'select * from progressoe.contact_vwagency')

select * from openquery(testcon, 'select * from contact.progressoe.contact_vwagency')

We got this figured out! Thanks to everyone who helped, esp. to Stefan from Belgium and Scott Dulecki who told me about http://www.oehive.org/proogle -- that's how I came upon this post which contained the key to solving our problem:

www.progresstalk.com/.../why-doesnt-the-simplest-query-on-earth-work.45971

Stefan points out that the VIEWs must be created using Progress SQL Explorer (sqlexp.bat) which I confess I'd never used before. Once we did that we were able to set up views the way we wanted and UPDATE tables without getting errors. Thanks again, everyone!