FW: Oracle Teradata Connection: Answer suggested

Toad Data Point & Toad Intelligence Central
1

2nd post attempt; first attempt bounced

From: Christenson, Lance

Sent: Thursday, October 01, 2015 11:15 AM

To: Toad World - Automated Email

Subject: RE: Oracle Teradata Connection: Answer suggested

Thanks, I did as you suggested, and I didn’t initially get the MySQL error this time, and the cross_connection query seemed to start running.

But it stopped with an error ( I was successfully logged onto both the Oracle and Teradata databases so not sure what it finds invalid).

But then I exited that error message, and tried another query, and this time I am back to the original message:

From: Toad World - Automated Email [mailto:info@toadworld.com]

Sent: Thursday, October 01, 2015 10:28 AM

To: Christenson, Lance

Subject: RE: Oracle Teradata Connection: Answer suggested

The following post by Ryan Johnson was suggested as an answer to “RE: Oracle Teradata Connection”:

Well, it is likely a hub issue. You will need to reset it. You will lose anything you had in the local storage.

First, open TDP, go to options, then Local Storage -> General. You should see this:

Make note of this folder and shut down TDP. Navigate to this folder and delete or rename it. Restart TDP and let me know if it is working again or not :slight_smile:

Did this answer your question?
Verify as the answer

Not the answer to your question?
Reject as the answer

Flag
this reply as spam/abuse.

2

I apologize but it looks like the image links are broken…

3

The first error message after the query started:

Got error 4206 ‘{0} Invalid data source definition: The UserId, Password or Account is invalid, ; Not enough inform’ from HUB

Then I tried another query and back to the original problem:

MySQL Database Error

Unable to connect to any of the specified MySQL hosts.

From: Ryan Johnson [mailto:bounce-RyanJohnson@toadworld.com]

Sent: Thursday, October 01, 2015 11:30 AM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] FW: Oracle Teradata Connection: Answer suggested

RE: FW: Oracle Teradata Connection: Answer suggested

Reply by Ryan Johnson

I apologize but it looks like the image links are broken…

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point - General notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

4

Logged out and back in for another try. This time I am logged on to both databases, and I clicked on cross-connection in the BUILD icon, but it only allows me to drop a table from one of the databases. The first database I choose will allow a table to drag and drop, but when I drag a table from the other database, when I release, nothing drops. Also, I don’t notice any images change in the GUI when I am in cross-connection mode. Should there be something indicating I am in cross-connection mode? I don’t think having two databases open at the same time and showing in object explorer would do it, as I believe I could have that scenario and still be building queries one database at a time.

From: Ryan Johnson [mailto:bounce-RyanJohnson@toadworld.com]

Sent: Thursday, October 01, 2015 11:30 AM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] FW: Oracle Teradata Connection: Answer suggested

RE: FW: Oracle Teradata Connection: Answer suggested

Reply by Ryan Johnson

I apologize but it looks like the image links are broken…

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point - General notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

5

Do you have the ODBC driver installed along with the ADO.net client for Teradata? Those are considered separate things and the ODBC driver is required for X-Query.

6

I have both ODBC and .net for Teradata installed. And I can access Teradata from TDP, even with Oracle also connected.

What I am noticing now is that when I first bring up TDP, and I click on the Cross_connectivity drop down in the Build Icon (ie, I have not clicked on anything else, not connected to any database, etc.), the Build icon is highlighted for a few seconds, and then I get the MySQL Database Error; Unable to connect to any of the specified MySQL hosts.

Do I need any other software installed in support of the TDP itself, some MySQL software?

From: Ryan Johnson [mailto:bounce-RyanJohnson@toadworld.com]

Sent: Thursday, October 01, 2015 1:43 PM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] FW: Oracle Teradata Connection: Answer suggested

RE: FW: Oracle Teradata Connection: Answer suggested

Reply by Ryan Johnson

Do you have the ODBC driver installed along with the ADO.net client for Teradata? Those are considered separate things and the ODBC driver is required for X-Query.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point - General notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

7

For now, let us get the hub running again. Please repeat the steps I posted earlier to reset.

Can you check the 32 bit ODBC Data Administrator (odbcad32.exe) and see if Teradata is amongst the available drives on the Drivers tab? You can get to it in TDP by going to create a new Generic ODBC connection and clicking the highlighted button.

ODBC.png
ODBC.png557×800 33.2 KB

8

OK, I refreshed the hub, so no more MySQL errors.

I checked ODBC Data Administrator, and it shows “TERADATA” as an available driver.

What happens now is that I can connect both Oracle and Teradata (at the same time), and in Query Builder mode I can build and run queries against either database, one at a time.

But if I change the Build to Cross-connection mode, I can only drag and drop from Oracle. With dragging Oracle table into the cross-query, it starts out with the circle with a back slash symbol, but then that changes to a square with a + and drops in the table. With Teradata it also starts with the circle with a back slash symbol, but that symbol remains when I move into the query builder window, it never changes to the square with a +.

From: Ryan Johnson [mailto:bounce-RyanJohnson@toadworld.com]

Sent: Thursday, October 01, 2015 4:52 PM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] FW: Oracle Teradata Connection: Answer suggested

RE: FW: Oracle Teradata Connection: Answer suggested

Reply by Ryan Johnson

For now, let us get the hub running again. Please repeat the steps I posted earlier to reset.

Can you check the 32 bit ODBC Data Administrator (odbcad32.exe) and see if Teradata is amongst the available drives on the Drivers tab? You can get to it in TDP by going to create a new Generic ODBC connection and clicking the highlighted button.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point - General notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

9

Try this: Create the Teradata and Oracle connections as Generic ODBC connections, using the respective ODBC driver for each, then use those in the X-Query.

10

Not sure what is meant by Generic ODBC connections, or how that relates to the ODBC driver for each. I simply downloaded the latest ODBC driver from the Oracle and Teradata Websites. In TDP 3.7 in Create New Connection, for “Group”, I picked Oracle for oracle and Teradata for the teradata Then for Oracle I go to Advanced Tab and use the drop down to pick Oracle in Oraclient12 Home driver. For some reason the advanced tab for the Group Teradata doesn’t give the choice of a drop down, so I had to use the odbc Data Source Administrator to associate the Teradata database to the Teradata ODBC driver.

From: Ryan Johnson [mailto:bounce-RyanJohnson@toadworld.com]

Sent: Friday, October 02, 2015 3:13 PM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] FW: Oracle Teradata Connection: Answer suggested

RE: FW: Oracle Teradata Connection: Answer suggested

Reply by Ryan Johnson

Try this: Create the Teradata and Oracle connections as Generic ODBC connections, using the respective ODBC driver for each, then use those in the X-Query.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point - General notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

11

I mean in Create New Connection, choose Generic ODBC. There will be a list of DSNs. Click the button I mentioned previously to bring up the ODBC Data Administrator, go to the User DSN tab, and click Add. Select Teradata (or Oracle) and follow the instructions for one, then the other. You can then select those DSNs for generic ODBC.

12

Ok, and I did notice when instead of attempting to drag a Teradata table into the cross-connection query I did a double click on the table, I get a message saying a cross-connection cannot be made with Teradata; create a generic ODBC connection (ie, exactly what you are saying).

So, I tried that. But I don’t get very far. I set up the Teradata database as a Generic ODBC.

It connected, and I dragged a table to a standard query, but the > Execute SQL at the bottom of the window remained grayed out, even after I check the boxes for which columns to include.

From: Ryan Johnson [mailto:bounce-RyanJohnson@toadworld.com]

Sent: Friday, October 02, 2015 4:50 PM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] FW: Oracle Teradata Connection: Answer suggested

RE: FW: Oracle Teradata Connection: Answer suggested

Reply by Ryan Johnson

I mean in Create New Connection, choose Generic ODBC. There will be a list of DSNs. Click the button I mentioned previously to bring up the ODBC Data Administrator, go to the User DSN tab, and click Add. Select Teradata (or Oracle) and follow the instructions for one, then the other. You can then select those DSNs for generic ODBC.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point - General notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

13

You should still be able execute by using the right click Execute menu action or the green tool button on the top left of the window.

14

Unfortunately those options are grayed out also. Since the Object Explorer is being populated with a list of the tables, it seems like the connection is there, but TDP doesn’t want to execute any queries. Also odd is that when I drag a table into the query builder, it take about 5 seconds for the table to appear, and then when I click on a column, the X doesn’t show until I move off of that column. All that would be fine, though, if the query would execute, which it won’t. I wonder if there are any upgrades or beta’s available for 3.7, maybe there are some bugs.

From: Debbie Peabody [mailto:bounce-Debbie_Peabody@toadworld.com]

Sent: Monday, October 05, 2015 6:10 PM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] FW: Oracle Teradata Connection: Answer suggested

RE: FW: Oracle Teradata Connection: Answer suggested

Reply by Debbie Peabody

You should still be able execute by using the right click Execute menu action or the green tool button on the top left of the window.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point - General notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

15

If you would like to persue this please make a support case and ask for a webex session.