Toad World® Forums

cross query error


#1

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.


#2

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


#3

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


#4

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


#5

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


#6

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

Debbie


#7

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

  1. Put your Cross-Query in an editor and prepend it with this tag /diagnose_xquery/
  2. Highlight the whole query and tag and execute. It will return only one row with one column.
  3. 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)


#8

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

  1. Put your Cross-Query in an editor and prepend it with this tag /diagnose_xquery/
  2. Highlight the whole query and tag and execute. It will return only one row with one column.
  3. 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


#9

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


#10

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


#11

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)


#12

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


#13

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


#14

Good Morning Debbie. Do you have an update?


#15

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.


#16

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?


#17

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.


#18

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.


#19

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


#20

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.