Skip to Content
author's profile photo Former Member
Former Member

Database views are missing

Post Author: dee7231

CA Forum: Data Connectivity and SQL

We have an ODBC connection to an Oracle database. There are 7 views that are created. However when we try to pick the view from the table selection in crystal, we see the tables but not the views. We have two ODBC connections, one to a test database and one to production. the test is fine when we expand the connection we get 2 options tables, and views. In the production connection, when we expand the ODBC we just get the list of tables. We had the same problem with test, and we fixed, but now we can't remember how we fixed it. We've compared the two connections and we can't find the difference. I know it's something simple that we're missing, please help.Thanks. Dee7231

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

8 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 31, 2008 at 06:46 AM

    Post Author: amr_foci

    CA Forum: Data Connectivity and SQL

    first you've to make sure the USER you've used for creating the ODBC connection gained an access right to those views

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 31, 2008 at 12:08 PM

    Post Author: dee7231

    CA Forum: Data Connectivity and SQL

    Sorry should have mentioned it. Yes we granted all access to the views, and we've gone in to SQL Plus and tested the user. And we can see the views that way. Testing it yesterday, we found that if we used the Oracle Server connection and connected directly to the prod database that way, we could see the views. If we try to go in through the ODBC connection we can't. We've taken the ODBC connection for the test DB (that's working correctly), and pointed it to the prod DB and we still can't see the views. We deleted and recreated both ODBC connections and the test DB works and the prod DB doesn't.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 31, 2008 at 05:28 PM

    Post Author: yangster

    CA Forum: Data Connectivity and SQL

    is there a reason why you would rather use the odbc connection over the native oracle connection in the report?the native connection will run faster and allow you to write back to tables via the command if you so choosethe views wouldn't be listed under tables, they should be seperated into branches for each type of db values crystal supportsTables, Views and Procedures

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 31, 2008 at 05:35 PM

    Post Author: dee7231

    CA Forum: Data Connectivity and SQL

    The software we are using to read the Crystal reports uses the ODBC connection. And we don't want to give anyone the ability to write directly to the tables. It should be read only. The sub categories(Tables, Views, Procedures) are also missing from the ODBC connection. when you expand it, it just give a list of the tables, instead. I've even checked the option settings for Crystal to see if something had change, all 3 are check marked.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 31, 2008 at 05:51 PM

    Post Author: kcheeb

    CA Forum: Data Connectivity and SQL

    Did you confirm you can see the views and tables with the same user you are using in your ODBC connection?

    Are there any parameters in your ODBC connection to allow/disallow seeing views?

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 03, 2008 at 11:45 PM

    Post Author: GraemeG

    CA Forum: Data Connectivity and SQL

    In Database expert, right-click the system name and select 'Options'. Make sure your 'views' box is ticked.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 04, 2008 at 01:57 PM

    Post Author: dee7231

    CA Forum: Data Connectivity and SQL

    Yes we tested the user in SQL Plus and we can see the views. No there are no parameters to disallow the views.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 04, 2008 at 02:00 PM

    Post Author: dee7231

    CA Forum: Data Connectivity and SQL

    Yes, Tables, Views and stored procedures are all check marked. Only Tables are listing.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.