Crystal - ODBC Question

[shaunk]

I think I am attempting the impossible here.
I need to access tables from both our Training and Production regions through an ODBC connection to a server where both databases reside. The ODBC connection is setup with a logon id that has read permissions in the Training database and none in the Production database. ODBC is authorising using SQL Server Authentication.
Of course , when the report runs, Crystal is posting the message 'Physical Databse not found'.

I have two questions from this

1) When setting up an ODBC connection to a server with multiple databases on it, how does ODBC know which database you wish to conenct to. You are not given that option, only a Windows or SQL server logon id to use ?
2) How do I acess multiple databases that reside on the same server from a single report

Thanks

 

[synapsevampire]

There's a default, I think the defacto is Master but I can't recall as I've been working with Oracle for quite some time.

You should try posting technical information:

Crystal version
SQL Server version

You shoulb be able to explicitly reference the database using something like:

<database>.<table>.<column>

This assumes that you're using an Add Command and using real SQL. Anotehr approac would be to use a View or a Stored Procedure.

You might speak with your dba about those, and once you learn what version of Crystal you're using, use the appropriate method for extracting from the database (Crystal gui vs. pasting SQL).

-k

 

[shaunk]

The Crystal Version is 8.0 and Sql Server 2000.
I'm acessing the data via ODBC directly from Crystal. The source for the report is not a stored procedure.
I'm not sure what you mean by 'Crystal gui vs pasting SQL'.

Version 8.0 of Crystal does not allow tampering with the SQL code. I may have to go to a stored proc?
Thanks

 

[synapsevampire]

A View or SP would be optimal. It was hard for me to guess that you had an old version, hence the additional posts and reason why one should always at least include their software version when requesting technical information.

You might also create another SQL Server ODBC connection, however the performance will degrade.

Another cheat is to use an Access database and create LINKs (not imports) to the database, and then use a Access query to get the data right, and use the Access query as the CR data source. This shoudl be the last ditch effort... It's more appropriate when using different server types.

-k

 

[vidru]

1) When setting up an ODBC connection to a server with multiple databases on it, how does ODBC know which database you wish to conenct to. You are not given that option, only a Windows or SQL server logon id to use ?

When setting up the ODBC connection, use the 'Change the default database to:' option (it's the step after it asks for valid login information).

2) How do I acess multiple databases that reside on the same server from a single report?

As k stated, a view or stored proc would be optimal. The Access route would work, and probably would be faster than two separate ODBC connections (even though they're to the same server), but it would seem silly given you've got a perfectly good big kid database that can do this on its own.

-dave

 

[shaunk]

OK Stored proedure it is !
Just one final question. Why would Crystal allow me to enter tables from different ODBC data sources at design time and complain about it at run time with 'physical database not found' ?
As said, the tables are from a mixture of Prod and Training d/b's sitting on the one server.