Crystal issue only with GP databases 2

[Luvsql]

Our sql server has GP databases, CRM and other apps on it. I have an ODBC connection setup, using nt authentication. My network login is an administrator, so I automatically inherit the dbo permissions and have access to all dbs and full rights.

When I connect to any other application's databases, I can see all tables, views and sps. However, when I select any of the GP company databases (the DYNAMICS db itself is fine), I can only see stored procedures (ie the table and view is not even listed). I have verified that they are all owned by the same (DYNSA). I even tried changing them to sa owned. This is the same sql server, same ODBC connection, same login.

When I connect to a GP company database say with excel using the same ODBC, I can open the tables and view fine. THis issue seeems to only be with Dynamics and Crystal.

Is there something in these GP databases that is preventing connection to the tables and views via a trusted connection?

 

[jymm]

Which version of Crystal & SQL are you running? I had the same problem a while back.

I ended up creating views of the data with better field and table names so the users of Crystal would understand (plus I did not have to see all of the variables that made no sense).

EVENTUALLY I also ended up creating a report db so that I could combine all company info - and put the views there.

Currently I am using SSRS (Crystal was asking too much for new licensing) for the reporting and I know that v10 is suppose to have more capabilities for ssrs - but we are not there yet...

 

[Luvsql]

I am using Crystal 9 with sql 2000. If I had all 3 (table, view and stored procedures marked in the options tab of the ODBC setup in Crystal), it would only bring part of the stored procedures, but no tables or views. If I uncheck the stored procedure, it will then show the tables and views, but a lot of my reports do use stored procedures as well.

There seems to be some maximum number of objects that Crystal can access at once and seems to do stored procs first (mine go from amAutoGrant down to zDO_PA10900SS_2). Since GP has thousands of sps, it doesn't appear possible to have all 3 listed.

 

[LyleU]

Try this solution from the Business Objects Knowledgebase.

LyleU

KBase Article ID:c2013280

Cause:
The number of database objects returned to CR is determined by the setting in the Windows Registry. The default value for this registry setting is 8,000. You can change this default value by editing the Windows registry setting, NTablesMax.
NTablesMax - specifies the number of database objects to get a listing of from the database. Once this number is reached the listing of objects in Crystal Reports is stopped. Objects can include tables, views, synonyms and stored procedures.

Solution:
To access the NTablesMax item in the registry:
1. On the Start menu, click Run.
2. Type "Regedit", then click OK. The Registry Editor will appear.
3. Browse to the following folder:

CR XI Release 2

HKEY_CURRENT_USER\Software\Business Objects\Suite 11.5\Crystal Reports\FetchOptions
CR XI (Release 1)

HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports\FetchOptions

CR 10

HKEY_CURRENT_USER\Software\Crystal Decisions\10.0\Crystal Reports\FetchOptions
CR 9

HKEY_CURRENT_USER\Software\Crystal Decisions\9.0\Crystal Reports\FetchOptions

CR 8.5

HKEY_CURRENT_USER\Software\Seagate Software\Crystal Reports\FetchOptions
4. In the right-hand window of the Registry Editor, double-click NTablesMax. The 'Edit DWORD Value' dialog box appears.
5. Select Decimal.
6. In the Value Data text box type "10,000". Click OK.
7. Close the Registry Editor and then restart your computer.

The next time you open Crystal Reports, you will be able to see all 10,000 database objects.

 

[Luvsql]

OMG, that's perfect. I actually had to increase it to over 25000 as GP has 17,000 stored procs alone! Thanks for this. I did check crystal knowledge base, but couldn't find anything.

 

[LyleU]

Once in a while something from my dusty old memory actually comes in handy!

Glad to have helped and thanks for the star.

Lyle

 

[RedJessie]

Hi

I'm new to Crystal (11.0), and am trying to connect to Oracle 10 objects with the Ora920 odbc driver and am having similar issues to Luvsql.
Even though I have changed the default parameter from 8000 to 20000 as suggested above, it makes no difference.
If I have Stored Procedures checked in the options window then I cannot see tables or views.
I can use the same odbc driver with MS Access and see everything without an issue.
I've tried using the OLE ADO driver also and that does show me everything, but it's a little flaky and stops working after a couple of hours.

Thanks
Red

 

[LyleU]

Red,

When you are able to see everything in MS Access, just how many objects are there? If the total of all tables, views, and SP's is over 20,000 then you won't see them all by specifying 20,000 in the registry. The registry edit has to be a number large enough to cover the total of the tables, views, and SP's.

Lyle

 

[RedJessie]

Hi Lyle

Not sure why, but when I went back to the registry setting this morning, it was back at 8000.
I changed it to 100000, closed the registry and launched Crystal, changed the options for the odbc connection so it listed all object types, collapsed the instance and when I opened it up, the tables & views were gone again.
I thought I'd up the FetchOptions to 200000 but when I went back to the registry it was back at 8000 again.
I have administrative rights to my PC, so I don't understand why the changes aren't sticking.

Thanks for getting back to me so quickly.

Red

 

[RedJessie]

Sorry, worked it out. Had to reboot to make the registry setting stick.
I see all objects now.
Thanks again.