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

Using ODBC, can't see all Stored procs in the database

When using the ODBC driver for SQL, I can't seem to see most of the stored procedures that are on the database. To be exact, I see one only, plus two functions. The one that I can access has the same schema owner as all of the others, so I'm not sure why the others aren't available. Using OLE DB, I can see the other SPs.

The reason I'm trying to use ODBC is that I'm trying to get the report to run under a specific set of credentials, so that the user doesn't need to authenticate to run the report. And I can't seem to do that with OLE DB. I'm not even sure that it works with ODBC, but want to give it a try. Thanks.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Dec 19, 2008 at 12:12 AM

    If you go to file >options >database-->under data explorer try to uncheck Tables,Views.

    Now try to create new connection and see whether you are able to see all the storedprocedures from database.

    Regards,

    Raghavendra

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 19, 2008 at 04:53 PM

    Hi Dan

    There is particular limit of number of objects(Tables,Stored Procedure and fuctions) shown in the Database Expert window.So for that we need to increase the number of objects in the registry.

    Before making any changes in the registry would suggest you to take the backup of registry first and try the below steps:

    1) Click on run|r Type in egedit.

    2) Click on HKEY_Curren_User| Software| Business Objects| Suit 12.0| Crystal Reports| Click on DatabaseOptions

    3) In Left pane| Right Click MaxRecordsToRead|Select Modify

    4) Change the Value from 0 to 100000 and select Base as Decimal.

    Restart the application.

    Also in Crystal report chek the below things:

    1) Click on File|Option|Databse Tab

    Check the check box Stored Procedure.

    Try to use upgraded drivers.

    Hope this will help !!

    Regards

    Asha.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 19, 2008 at 04:07 PM

    I'm afraid that didn't work. I still see the one Stored Proc and two functions that were visible before making this change. The two funcs that are available are actually what I'm interested in calling from my sub report, but I get an error when I try to add them to my report, which is why I created the stored procs; simply to call these functions. The message is that it fails because the functions are table value objects. Do you know if there's a way to get these working directly from Crystal? If not, then I guess I'm still trying to figure out how to see all the SPs in the database using ODBC. Thanks.

    Dan

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 19, 2008 at 05:20 PM

    Thanks for the info, but I may have solved the problem another way.

    I checked the permissions on the one stored proc that I could see, and it had the Public role granted the Exec permission. In my stored proc, I had granted similar permissions to the user that my report would run under, but obviously that didn't work. I changed the permission to the Public role, and now I can see the SP.

    I would have thought that granting the exec permission for the user that the report will run under would have worked, but I guess not. Perhaps I need to grant permissions to my own login in order to see the SPs at design time, then also have the permission granted for the credentials that the report runs under for run time. Not sure.

    I still have to test that the report will actual run when prompted by the host application. I'll report back if this solution works. Thanks.

    Dan

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 19, 2008 at 06:47 PM

    The report ran in the host application, so I'm going to leave it as-is, with the Public role granted access to the SPs.

    Dan

    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.