cross query error

ray.evans2013 · February 7, 2013, 10:17am

Cross Query Database Error: Got error 4208 ‘ODBC data source returned an error: Datetime field overflow.’ from HUB

we consistently have problems with cross query no matter which databases we are joining. The specific instance here is between excel and oracle. Bringing in the date/time field from oracle table in the select statement. Limit is not based on a date/time field.

Debbie_Peabody · February 7, 2013, 1:10pm

Please add "/log=all to your shortcut path like this.

“C:\Program Files (x86)\Quest Software\Toad Data Point Beta 3.2\toad.exe” /log=all

Then run TDP and produce the error. After the error occurs, generate a support bundle and attach. This will gather some info we need in order to look into this.

Debbie

ray.evans2013 · February 10, 2013, 6:39pm

Please see attached support bundle files. Thank you for reviewing.
DataHubSupportBundle.zip (46.4 KB)

ray.evans2013 · February 10, 2013, 6:39pm

Please see attached support bundle files. Thank you for reviewing.
ToadSupportBundleToad for Data Analysts.txt (15.8 KB)

ray.evans2013 · February 10, 2013, 6:39pm

Please see attached support bundle files. Thank you for reviewing.
Toad.log (256 KB)

Debbie_Peabody · February 11, 2013, 8:28pm

Thanks. Let me forward this to the team that handles Cross-Query and see what they can see in the files.

Debbie

Debbie_Peabody · February 12, 2013, 12:01pm

Sorry. That logging method did not give us enough info. I need you to do the following:

Put your Cross-Query in an editor and prepend it with this tag /diagnose_xquery/
Highlight the whole query and tag and execute. It will return only one row with one column.
Copy and paste the contents into a text file and post. The content will look like the attached file.

This will get us better detail.

Debbie
DiagnoseQuery.txt (37.7 KB)

Debbie_Peabody · February 12, 2013, 12:01pm

Sorry. That logging method did not give us enough info. I need you to do the following:

Put your Cross-Query in an editor and prepend it with this tag /diagnose_xquery/
Highlight the whole query and tag and execute. It will return only one row with one column.
Copy and paste the contents into a text file and post. The content will look like the attached file.

This will get us better detail.

Debbie

ray.evans2013 · February 12, 2013, 4:52pm

Please see attached debug file from use of /diagnose_xquery/.
debug_dump.txt (117 KB)

Debbie_Peabody · February 13, 2013, 3:01pm

Is INFOSTR.CIR_INV_TBS is an Oracle table?

Can we get a DESC on INFOSTR.CIR_INV_TBS? DDL would be nice.

Using the x-query editor, does select * from INFOSTR.CIR_INV_TBS; work?

Debbie

ray.evans2013 · February 13, 2013, 5:07pm

INFOSTR.CIR_INV_TBS is an Oracle table.

When using the x-query editor, select * from INFOSTR.CIR_INV_TBS fails with same error. Using standard editor, the same query runs without issue.

Please see attached file for INFOSTR.CIR_INV_TBS.
CIR_INV_TBS.rtf (18.4 KB)

Debbie_Peabody · February 14, 2013, 4:26pm

Thanks. Here is what our dev said:

"Good reply from the customer, unfortunately we now think it’s the data being returned in one of the date fields. Is there anything unusual about the dates they store? Some examples might help.

The ODBC driver is throwing the error. What version is the driver?"

So can you export some sample rows of data? And what version of the Oracle Client are you using?

Debbie

ray.evans2013 · February 25, 2013, 8:08pm

Oracle client is 9.2.0.1. Please see attached data export.
toad_world_sample.xlsx (10.2 KB)

ray.evans2013 · March 13, 2013, 10:44am

Good Morning Debbie. Do you have an update?

speterson · August 23, 2013, 5:04am

Error:8/23/2013 6:57:26 AM 0:00:00.000: Cross
Query Database Error: Invalid data source definition: Data source
name not found and no default driver specified

1: SELECT SHEET_TIN.Identity

    , SHEET_TIN.Claim

    , SHEET_TIN.`Sales

Office`

    , SHEET_TIN.`Cause

major`

    , SHEET_TIN.`Cause

minor`

    , YR_PC_TURN_VW01.YRT_ID_FACILITY

    , YR_PC_TURN_VW01.YRT_DTE_TURN

    , YR_PC_TURN_VW01.YRT_ID_TURN

    , YR_PC_PROD_VW01.YRP_DIM_FINISHED_GAUGE

    , YR_PC_PROD_VW01.YRP_DIM_FINISHED_WIDTH

FROM

    (`PUSXYA1.PSC.USS.COM

(ZC161C6), ZC161C6`.FIN_COIL.YR_PC_TURN_VW01 YR_PC_TURN_VW01

    INNER JOIN

    `PUSXYA1.PSC.USS.COM

(ZC161C6), ZC161C6`.FIN_COIL.YR_PC_PROD_VW01 YR_PC_PROD_VW01

    ON (YR_PC_TURN_VW01.YRT_TMSTP_TURN

= YR_PC_PROD_VW01.YRP_TMSTP_TURN))

    INNER JOIN

    `MTD_SHEET_TIN

20130815`.SHEET_TIN SHEET_TIN

    ON (SHEET_TIN.`Identity`

= YR_PC_PROD_VW01.YRP_ID_COIL_PRODUCED)

TOAD DATA POINT PROFESSIONAL ADDITION 3.2.0916

Sue

Team Manager-Gary Plant Systems

Work: 219-763-5366 or Steelcom 8-465-5366

Cell: 219-798-6238

From:
“Joshua Liong”
bounce-JoshuaLiong@toadworld.com

To:
toaddatapoint@toadworld.com,

Date:
08/22/2013 07:25 PM

Subject:
RE: [Toad Data
Point - Discussion Forum] Cross Query Error

RE:
Cross Query Error

Reply by Joshua Liong
To speterson:

Thank you for posting in Toad World. To better
understand the issue, I would like to ask for the following information:

The SQL Query you utilized
The Type & Version of Toad Data Point
you are using.
It may help to use the
Query Builder to see if Toad Data Point can generate and execute your query.
Cross queries require special syntax which the Query Builder can assist
with.
I look forward to your response.

-Joshua Liong

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.

RyanJohnson · August 23, 2013, 9:42am

Greetings!

I am sorry, could you tell me what connections are a part of the Cross Query? And if one of them is Oracle, if they are by the client or direct connect?

RyanJohnson · August 23, 2013, 10:02am

Nevermind, I saw your other post, which I think that the reply was intended for. For Oracle, was it by client or direct connect? And for ODBC, can you verify that there are ODBC drivers for Excel files available? Go to TDP, right click on a ODBC connection and select properties, click on the ODBC Administrator button, and look in the Drivers tab.

speterson · August 23, 2013, 10:04am

PUSXYA1.PSC.USS.COM (ZC161C6), ZC161C6.FIN_COIL.YR_PC_TURN_VW01
YR_PC_TURN_VW01

PUSXYA1.PSC.USS.COM (ZC161C6), ZC161C6`.FIN_COIL.YR_PC_PROD_VW01
YR_PC_PROD_VW01 are oracle

MTD_SHEET_TIN 20130815.SHEET_TIN SHEET_TIN
is an excel on my hard drive

How can I tell if connecting by client or direct
connect - sorry not familiar with toad

Sue

Team Manager-Gary Plant Systems

Work: 219-763-5366 or Steelcom 8-465-5366

Cell: 219-798-6238

From:
“Ryan Johnson”
bounce-RyanJohnson@toadworld.com

To:
toaddatapoint@toadworld.com,

Date:
08/23/2013 11:43 AM

Subject:
RE: [Toad Data
Point - Discussion Forum] cross query error

RE:
cross query error

Reply by Ryan Johnson
Greetings!

I am sorry, could you tell me what connections
are a part of the Cross Query? And if one of them is Oracle, if they are
by the client or direct connect?

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.

RyanJohnson · August 23, 2013, 11:52am

Check the properties of the Oracle connection by right clicking and checking which tab is active: Oracle Client or Direct

RyanJohnson · August 26, 2013, 9:39am

Here is one more idea: For the Oracle connection, go to properties, then to the Advanced tab, and make sure that the ODBC driver is set.