Error using ODBC with CR Ver 9 to Ingres DB

[darrenh911]

Hi

I am using CR 9 and ODBC to connect to an Ingres 2.6 database on Windows XP. I create a simple report with one field from a table and it works fine. If I close Crystal down, then start it up and run the same report again and refresh the data, I get the following error message

"Failed to open a rowset. Details:42500:[CA][Ingres ODBC Driver][Ingres]Table 'ingres' does not exist or is not owned by you."

There is no table called 'ingres' in the database, and I am not attempting to access one called 'ingres'.

Why would it work once and then not again ? Hope someone can help. Thanks

Darren

 

[pgtek]

Hi

I see your using ingres :-(.
I previuosly used ingres but with open link datasource.
you my have the datasource not set to systems DSN or not have right to used this table.

check permission and grant on this ingres table and database.
I would recommend using MS SQL or Oracle database instead of ingress.
since it's and obselete database systems. MS Access is better than ingres
Computer Associated stop supporting this database system.

Oracle and MS Sql will run faster and efficient than this ingres database
since MS SQL can run over 789,000 request with out a slowdown of performance.
I don't think ingres never come near to that. I don't recommend ingres to anybody.

cheers

pgtek

 

[AndyPSD]

Darren,

I have just run into this same problem. Did you find a solution?

I noted pgtek's comments. Ingres has a huge installed base each Unicentre instalation probably runs on Ingres. Comparing MS Access to an industry standard dbms that can handle terra bytes of data and 1000's of concurrent users made me smile!


Regards

AndyP

 

[darrenh911]

Hi Andy

No I don't have an answer to it yet. I have communications going on with the Crystal Support team at present.

Further to my original post, I have noticed what is actually happening. When you re-open the report, it has saved the SQL Query incorrectly. Instead of having 'SELECT <column> FROM owner.table', it has 'SELECT <column> from table.owner'.

I have had some limited succes using a File DSN instead of a User or System DSN.

I will let you know when I find more details on it.

Darren

 

[AndyPSD]

I now have a fix for this problem. We are running Crystal V9 against Ingres 2.6 on W2000 servers.

You need to edit the report parameters in the Crystal Management Console and check your vnode settings. Details are as below.

Solution

This turned out not to be an ODBC problem as we initially suspected.

There are about 8 components to Crystal Enterprise that run as services. I have changed these to run under the Ingres account, hey were initially installed to run under the system account. Ingres has administration privileges.

The crystal enterprise server accesses Ingres via an ODBC gateway. The ODBC connection uses a standard ingress vnode connection. Depending on what information is in your vnode will determine what user account the crystal server should be running under.

If the vnode uses IIPROMPTALL, IIPROMPT1 Or an installation password then the crystal services must be running under an account that has permissions to access the database you are trying to connect to.

If you run your vnode as a specific user account eg, xyz with the relevant password then it should not matter what account the crystal services are running under as any connection to the database will be as xyz. We use this approach.

When reports are saved into Crystal Enterprise, crystal appears to set up default connection parameters for the reports. It is these default parameters that stop the reports from connecting to Ingres. You need to edit the report parameters via the Crystal Management Console CMC.

Each report is an object with the CMC. The CMC is a browser type environment. Select the mange Objects option.

The reports will be displayed as a table of objects. Select the report you are interested in.

You should see a screen with several tab fields across the top for you selected report. This allows the parameters for each report to be set. Select that database tab which sets the database connection parameters.


Make sure the “Use custom database login information“ radio button is selected. This gets rid of the annoying extra login dialogue box when you run the reports. Make sure that ODBC is select as the database driver type.

At the bottom of the screen ensure that the “Use default table prefix” radio button is selected but leave the entry field blank. This fixes the SQL table name problem.

Remember to save the changes by selecting Update.

The report will now run and can be scheduled or viewed in the normal way.

Regards

Andy