Heterogeneous Query ODBC issue

Toad Data Point
1

Hello all.

Having an issue using TOAD for Data Analysts. I am testing some ETL. The source is SQL Server 2008, and the target Oracle 11 on a unix box. I can use TOAD to make successful selects against either the source or the target by themself using the coneections I have set up in TOAD, and I can build a heterogeneous query just as the manual says, and the background watermark “Heterogeneous Query” pops in on query builder, but when I try to run the query, I get following error:

Database Error
[Microsoft][ODBC Microsoft Access Driver] Reserved error (|); there is no message for this error.

I click OK on that higher level warning, end up with a “Toad for Data Anlaysts Error Message”, label as “Toad for Data Anlaysts 2.7.0.348”, message within states:
Quest.Toad.Exceptions.BaseException
Invalid use of OdbcFunction call. Invalid number of parameters.
Stack Trace:
at Quest.Toad.Db.Functions.OdbcFunction.ImplementCall(Connection conn, String function, String parTotal)
at Quest.Toad.Db.SqlFunction.EvaluateSql(Connection connection, String sql, Object[] parameters, IDataParameter[]& outparameters, Int32& inParameterIndex, Int32& outParameterIndex, Boolean function)

I’ve googled but not found this error. Similar ones, but not this one. Have now read the documentation and see that TOAD is actually using an MS Access database it loaded during it’s installation to my machine. I’ve also read posts here that state that even though I built these connections in TDA that TOAD will “generatee on the fly” ODBC connections to the two different data sources, suck into the Access DB, and do the work there. So somehow, this “generating the ODBC calls on the fly” is apparnelty not workng for me.

In reading the error message I saw the initial reference to an “invalid number of parameters” . . . could this somehow be related to fact that source SQL Server not installed on its default port, and is instead using a differing port than 1433? If so, how do I help TOAD get aorund the obstacle. If not, any ideas on what exacty is the iissue and help would be greatly appreciated!

I am “under the gun” for figuring this out, so Please do feel free to e-mail me follow up questions at williamqualters@gmail.com or call me on my mobile at (336)-816-2586.

Thanks in advance for any assistance you can offer folks!

Bill

2

In TDA version 2.7 we use the Access database to do the heterogenous queries. Any rewriting of the queries is done by Access. This method is also very slow. I suspect that something in your SQL syntax is something that is not expected. Can you post or email me the query you are using? dpeabody@quest.com Are you using named or positional parameters?

Also, starting with TDA version 3.0 we wrote our own optimizer to execute heterogenous queries. Here we have full control over what is occuring. I would suggest that you download a trial of Toad Data Point 3.2 and try this there. (We changed our name from Toad Data Analysts to Toad Data Point. Same product, different name)

You can download the trial from here. http://www.quest.com/toad-data-point/

Debbie

3

Hi Debbie,

Thanks for the very quick initial response! I’m impressed! ;o)

As to query syntax, there are no parameters in the SQL. I imagine that this query is about as “vanilla” as a heterogeneous query can get. Same table resides at both source and target, and in query they are simly being joined on the three key fields with absolutely no where predicate (I chose these tables specifically because they are TINY . . 244 rows, few data elements). I’ve overstruck actual server names and database names, however, the fields are for standard reference data on country codes, so left remainder of query the tool built intact.

Here is what it generated:
SELECT REF_CNTRY.SOR_ID
, REF_CNTRY.RAW_CNTRY_CD
, REF_CNTRY.PERIOD_DT
, REF_CNTRY.CNTRY_CD
, REF_CNTRY.CNTRY_NM
, REF_CNTRY.REGION_NM
, REFCNTRY.SUB_REGION_NM
FROM
{{Odbc(“DRIVER=SQL Server;SERVER=ABCDE1234F3,1647;UID=;APP=Toad for Data Analysts;WSID=DTCDE0F1F5BB6DF;DATABASE=SRCDB;Trusted_Connection=Yes;PWD={0};,”)}}.“dbo.REF_CNTRY” REF_CNTRY
INNER JOIN
{{Odbc(“DRIVER={Microsoft ODBC for Oracle};SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACL00X0123.CLIENT.COM)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=a111d)));UID=BillQ;PWD={0};,999A9BC23T9XYZ1234JKL456FGG97E”)}}.“TGTDB.REFCNTRY” REFCNTRY
ON (CVAR(REF_CNTRY.SOR_ID) = CVAR(REFCNTRY.SOR_ID)) AND (CVAR(REF_CNTRY.RAW_CNTRY_CD) = CVAR(REFCNTRY.RAW_CNTRY_CD)) AND (CVAR(REF_CNTRY.PERIOD_DT) = CVAR(REFCNTRY.PERIOD_DT))

As to downloading and using TDP 3.2, unfortunately this is a large corporate environment and I am constrained for using only TDA 2.7 as approved. I can inquire about when newer versions might be approved, but in the near term, I really need to get a solution to the issue at hand with the tool at hand. If you can help me do so, I’d greatly appreciate your assistance! ;o)

Thanks,

Bill

4

It looks like you are using OS Authentication. Can you use an SQL Server authenticated ID? I think this is the issue.

Debbie

5

Hi Debbie.

No, sorry, I can not. “SQL Server” accounts are not supported.

Even in my previous roles elsewhere, they were not supported by the way, and wherever I’ve been for several years now they have used Active Directory on Windows for access to the SQL Servers, and not supported fixed id/pw thinking.

Can you help me understand why you think it’s an issue with “Windows Authentication”? I mean, I am able to use your tool with the WIndows Authentication process on the single database query. I have used TDA in other words to query the data in SQL Server successfully by itself, without the heterogeneous thing. And when we cross over to the heterogeneous mode, from eyeballing it, it sure appears to have built the conection string knowing that it’s a ‘trusted account’ . .

Please advise asap if what you are saying is that your tool cannot support Windows Authentication on the heterogeneous side?

Thanks,

Bill

6

I have some good and bad news. The good news is that I can produce the exact error you are getting and it has nothing to do with you SQL server OS authentication.

Now for the bad news. Unfortunately in version 2.7 there is a bug with using Oracle 11G. It simply won’t work in that version. There are no work arounds. The current version 3.2 works fine using the combination of SQL Server and Oracle 11G.

I have no solution except to upgrade. Sorry. Or ofcourse, upgrade would also solve the issue.

Debbie

7

P.S. You could consider exporting the Oracle data into your Toad Sample database (which is access) and joining that to your SQL Server data.

Debbie

8

Hi again Debbie,

Thanks for this infomration. So, as you probably know, big companies take the software and “package it” before allowing it to be distirbuted on their machines. I’ve searched the download services, but can’t locate TOAD Data Point at all. I’m assuming it is actually installed as a component of one of your larger Quest product suites. Would it be “TOAD for Oracle 11g”? If not, what version of TOAD carries this 3.2 version of Dat aPoint that I need please?

Thank you again fo rlal of your help.

Bill

9

The Toad for Oracle 11.5 bundle will have the Toad for Data Analysts 3.1 installer. This is the base edition and you could use that.

Debbie

10

Hi Debbie,
I’m beginning to feel very stupid today. So, managed to get ok to download TOAD Data Point 3.2 and install. Went to ODBC panels and see I have no drivers for Oracle. I googled the drivers for Oracle 11g and it appears only way I can get the driver so TOAD will be abel to do it’s thing is to install the entire Oracle client packages? Is that accurate?

Thanks again for you rhelp.

Bill

11

I prefer using the Oracle packages but you can install the Oracle instant client. If you do this you also need to install the ODBC drivers separately as they do not come together.

There are instructions for using the Oracle Instance client in the Help file under “Create Oracle Connections”

Debbie

12

Hi Debbie,

Thank you for al of your help to get me this far! I truly appreciate you!

That said, I am sorry to trouble you again. I now have heterogenous queries working IF I am only joining across the two disparate sources.

So, if I run:

Select A.field1, A.field2
From TableA A (sitting in SQL Server on Windows)
Join TableB B (sitting in Oracle on Unix)
On A.field1 = B.field1

. . . it runs like a charm . . .

However, this won’t run for me when I edit the SQL statement as follows:

Select A.field1, A.field2 From TableA A (SQL Server on Windows)
except
Select B.field1, B.field2 From TableB B (Oracle on Unix)

nor if I use “minus” instead of “except”:

SQL Server uses “EXCEPT”, whereas Oracle uses “MINUS”.

Any idea how I can develop the same approach this in heterogeneous mode in TOAD? The Database Error directs me to “check the manual that corresponds to your Cross Query server version for the right syntx to use . . .”

Please let me know if anyone else has faced thi sgap and how ti was handled . .

Thanks,

Bill

13

Except and minus are no supported. You need to re-write the query as SELECT A.field1, A.field2 FROM TableA A (SQL Server on Windows) WHERE (B.field1, B.field2) NOT IN (SELECT B.field1, B.field2 From TableB B (Oracle on Unix));

Debbie

14

Hi Debbie,

That’s what I thougt (was afraid of) . . . but hey, still better than my other alternatives to not using TOAD! I really want to say THANK YOU for your time and for being so very helpful! I hope al lappreciate your being there as much as I do!

Have a bleseed day and a great weekend!

Cheers,

Bill

15

Hello Debbie and all who are more competent than I! ;o)

OK. I tried this last September, and comparing SQL Server versus an Oracle 11g db it worked using the “Not In” approach (Minus/Except being unsupported). So, here I am again, and now, I again need to compare against two separate datasets, the one on my side again being Oracle 11g, and I am now using Toad For Data Analsyts 3.1.0.638. I have all my connections set up using the tnsnames.ora file. This time, both ‘source’ and ‘target’ for the ETL are in Oracle. Still , different connections, so I need a heterogeneous query. I am proceeding as follows:

Build Query --> Cross-Connection Query Builder

I grab the table “A” from the source Oracle connection and drop it onto the “Diagram” tab.

I grab the same table “A” (identical DDLs) from the target Oracle connection and drop it onto the ‘Diagram’ tab.

I create simple inner joins on the three fields constituting the primary keys.

I run the query. Voila! A result set.

Now, to do the work I really need to do, I drop the relationships (remove them), move myself over to the “Query” tab, and edit the sql now foudn there to leverage the “not in” approach:

Select a,b,c from SourceDB.Table_A Where (a,b,c) Not In (Select a,b.c From Targetdb.Table_A);

I execute and …Aaarrggh . . .

[Microsoft][ODBC Microsoft Access Driver] ODBC–connection to ‘Oracle in OraClient11g_home1}(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostnamehere.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=w123q)))’ failed. 1 0

PLEASE HELP ASAP!

Thanks,

Bill

16

You are using Base XQuery. That uses the Microsoft access engine which is very restrictive. If I remember correctly it does not support an 11G client. Our Pro version has our own XQuery engine and we support this fully.

You could try using 10g Client or Trial version of TDP 3.2

Debbie