Connection to Teradata works in Single Connection Editors but not in cross query connection?

Toad Data Point
1

Hi all,

I'm attempting to cross query from Teradata and Oracle in Toad Data Point 6.0.
I currently have Teradata ODBC drivers set up in Toad Data Point 64 Bit ODBC Administrator, and I am able to connect to my Oracle Database without issue in the cross query editor.

I am able to connect to both databases separately in a single connection editor but not in the cross query editor (and only to Oracle in the cross connection).

The following error is as follows:
Cross Query Database Error: Invalid data source definition: Insufficient information to connect to the data source.;Invalid attribute in connection string: CharSet.;Invalid attribute in connection string: CharacterSet.;Invalid attribute in connection string: DBCName.;Invalid attribute in connection string: FBS.;Invalid attribute in connection string: FORWARD_CURSOR.;Invalid attribute in connection string: NO_CACHE.;Invalid attribute in connection string: RunInQuietMode.;Invalid attribute in connection string: SessionMode.;Invalid attribute in connection string: authentication.

The table I'm looking to query populates on the left, and I am able to build it via cross connection SQL builder and extract the SQL. However, when I run the SQL select statement itself, the error gets tripped up. Any help would be greatly appreciated! Thank you.

2

Might need to take this one to Quest Support, but first a few questions:

  1. Did you install, by chance, the .Net provider for Teradata, in addition to a supported Teradata ODBC driver?

  2. You are able to connect to Teradata successfully via both (a) the native protocol and (b) your ODBC driver?

  3. You're able to build the cross-connect query in the Query Builder... do you get the same error message when you run the query directly from the Query builder?

3

Hi Gary!

  1. I don't believe I have - I am only familiar with Teradata ODBC Driver; I'm unsure if I am allowed to install .NET internally.

  2. How can I check this? I am able to connect to Teradata through Toad single connection and through other database query tools.

  3. I do not receive the same error when it's run on a regular query builder. I do receive an error when I run it in a cross connect query builder, regardless of whether it is built via the SQL statement or via the connection visualizer.

Thanks!

4

Hi Gary!

Actually just figured it out - I think I was connecting via the net provider and not the odbc driver even though I had it installed. It works now!

Thank you!

5

Glad to hear this... yes, cross-connection queries will need to either use TDP's ODBC driver that comes with many of the TDP connectors (even though you might be using the database vendor's native protocol... Oracle is a good example of such) or you need to connect via ODBC.
e.g. the cross-connection blending is done via ODBC on the backend.

6

@drazil1567

Can you elaborate on how you were able to get it to work? I'm having the EXACT same problem. I can connect fine to both Teradata and Oracle in single query, and Oracle works fine in the cross query, but I get that same error when trying to use Teradata.

I do have the .Net provider for Teradata installed, as well as the ODBC driver, but I can't figure out how to try and change the connection to the ODBC driver.

Literally in the exact same boat you were just 2 months ago, so hoping you or Gary can steer me in the right direction. Thanks!

7

For anyone else in the same boat, I got it to work as well:

Strangely, the Oracle setup will work via your normal connection under your Oracle list of databases in the navigation manager. For Teradata, you'll need to create a new "ODBC Generic" connection, and then point that to your Teradata driver. Then you'll simply use the connection under the ODBC Generic listing and work your way to your Teradata views that way.

Hope this helps! And thank you to both Gary & Drazil. I was beating my head against the wall for ages about this yesterday, but your discussion helped guide me towards the right path.