I’ve gotten a Heterogeneous Query setup to try and avoid having to build an intermediate Access Database in order to cross-reference data from our Oracle Instance to the User data stored on the SQL Server (they cannot both be stored in one or the other - that’d be too easy).
I keep getting an error message:
“Error:3/23/2010 0:00:00.015: [Microsoft][ODBC Microsoft Access Driver] Unrecognized keyword WHEN.”
Which appears to be an issue with the CASE statement that I’m using in the query. What’s odd about that, and I’m not sure what I have to do to fix it, is that the CASE query should not be executing against the SQL Server - only against Oracle.
Is there something I have to do, to specify which parts of the query execute against which source? This is not a query that was built natively in TDA - the first version was written by hand in SQLTools, I then imported and tweaked it in TDA - all of that was fine until joining to the SQL Server.
Will the IIF work on Oracle? The CASE statement is for the Oracle part of the query, and not for the SQL Server part. The SQL Server part is actually just a join to a defined view on the SQL Server.
Debbie Peabody wrote:
The Heterogeneous queries are being executed using the JetEngine so you need to use Access syntax. Change your Case statement to an IIf like below.
SELECT JOBS.JOB_ID
, JOBS.JOB_TITLE
, JOB_HISTORY.EMPLOYEE_ID
, JOBS.MAX_SALARY
, IIf([EMPLOYEE_ID]<400,0,2) AS Total
FROM …
Yes. It will work for Oracle. The statement below was built using the HR.JOBS table from Oracle. It is a matter of what SQL Engine is executing the statement and in this case it is the Access engine, so we have to talk in ‘Access’.
Ok, I’m trying to figure out how to make this work, and perhaps I’m just not reading this well enough. These are my two CASE statements:
(CASE IDW.WSTAT
WHEN '1' THEN 'I'
WHEN '2' THEN 'R'
WHEN '3' THEN 'C'
WHEN '4' THEN 'D'
END) "Job Type",
(CASE IDW.Q_L
WHEN 1 THEN 'V'
WHEN 2 THEN 'H'
WHEN 3 THEN 'V-H'
WHEN 4 THEN 'C'
WHEN 5 THEN 'V-C'
WHEN 6 THEN 'H-C'
WHEN 7 THEN 'V-H-C'
END) Products
(IIF (IDW.Q_L=1,‘V’,IIF(IDW.Q_L=2,‘H’,IIF(IDW.Q_L=3,‘V-H’,IIF(IDW.Q_L=4,‘C’,IIF(IDQ.Q_L=5,‘V-C’,IIF(IDW.Q_L=6,‘H-C’,IIF(IDW.Q_L=7,‘V-H-C’)))))))) AS Products
You are correct. That is how you would translate a case to an IIF. Not very eloquent, I know, but workable for cross-query.
Here is full sample query I just made and executed to confirm it works.
SELECT address.ADDRESS_ID
, IIf([address.ADDRESS_ID]=1,‘Atlanta’, IIf([address.ADDRESS_ID]=2,‘Los Angeles’, IIf([address.ADDRESS_ID]>2,‘Unknown’))) AS NewRegion
, REGIONS.REGION_NAME
, address.POSTAL_CODE
FROM
{{Odbc(“DRIVER={Microsoft ODBC for Oracle};SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ALV206302)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=YAKE920)));UID=DEB;PWD={0};,D9B9460730671CCB”)}}.“HR.REGIONS” REGIONS
INNER JOIN
{{Odbc(“DRIVER=SQL Server;SERVER=ALVBF0Y2D1;UID=;APP=Toad for Data Analysts;WSID=ALVBF0Y2D1;DATABASE=AdventureWorks;Trusted_Connection=Yes;PWD={0};,”)}}.“dbo.address” address
ON (CVAR(REGIONS.REGION_ID) = CVAR(address.ADDRESS_ID))
This was the referral I was given when I had similar confusion over syntax, when I created a heterogeneous query (HQ). Nothing obvious in the current TDA (2.5) product to tell you when involving ODBC to switch to the “Access” syntax.
Ok, I took that and implemented it, but now I'm getting this error, and the only information I can find refers to issues with Access '97 and older (I have 2007):
Error:3/24/2010 0:00:03.562: [Microsoft][ODBC Microsoft Access Driver] Reserved error (-1001); there is no message for this error.
This is the full select:
1: SELECT AE.SuperName AS Supervisor
, AE.[NAME] AS [Name]
, IDW.W
, IDW.SR
, IDW.AC
, IDW.H
, IDW.C
, IDW.WORDATE
, IDW.SCHDAT
, IDW.WIDATE
, IDW.WFINDATE
, IDW.WSTAT
, (IIF (IDW.WSTAT = '1','I', IFF (IDW.STAT='2','R',IIF (IDW.WSTAT='3','C',IIF (IDW.STAT='4','D'))))) AS "Job Type"
, (IIF (IDW.Q_L=1,'V',IIF(IDW.Q_L=2,'H',IIF(IDW.Q_L=3,'V-H',IIF(IDW.Q_L=4,'C',IIF(IDQ.Q_L=5,'V-C',IIF(IDW.Q_L=6,'H-C',IIF(IDW.Q_L=7,'V-H-C')))))))) AS Products
FROM
({{Odbc("DRIVER={Microsoft ODBC for Oracle};SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=99.99.99.99)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=id)));UID=NB;PWD={0};,")}}."GGS.ID_W" IDW
CROSS JOIN
{{Odbc("DRIVER={Microsoft ODBC for Oracle};SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=99.99.99.99)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=id)));UID=NB;PWD={0};,")}}."GGS.ID_CT" IDC
ON (CVAR(IDW.AC) = CVAR(IDC.AC)) AND (CVAR(IDW.H) = CVAR(IDC.H)) AND (CVAR(IDW.C) = CVAR(IDC.C)))
LEFT OUTER JOIN
{{Odbc("DRIVER=SQL Server;SERVER=tnnass-sql01;UID=;APP=Toad for Data Analysts;WSID=TNNASD-BN;DATABASE=Employee Core;Trusted_Connection=Yes;PWD={0};,")}}."dbo.AE" AE
ON (CVAR(IDW.WHO) = CVAR(AE.CSR))
WHERE IDW.AC IN (5,3)
AND IDW.WSTAT IN ('1', '2', '3', '4')
AND IDC.STAT IN ('1', '2', '3', '4')
AND IDW.SCHDAT <= SYSDATE - 29
AND IDW.WFINDATE IS NULL
ORDER BY IDW.WORDATE ASC
(some things may look slightly odd because I had to sanitize the select for public posting, but everything works except the join between Oracle and SQL Server)
I hate to resurrect an old thread, but I'm still having a problem with this very select.
In the intervening time, it occurred to me that when I was able to get a cross-source select to work, we were running SQL Server 2000 - and since then we've upgraded to SQL Server 2008. Since I know MS the way I do, I went in and looked at my SQL Server ODBC Driver version - and it's from 2004.
I updated - or Tried To (tm) my ODBC driver by installing SQL Server 2008 Management Objects, which gave me an "SQL Server Native Client 10.0" with a date of 03.30.2009. Oh joy! I thought I had found my solution. I quickly adjusted the properties of my SQL Server connection in TDA to use this ODBC driver, and then ran my query again.
No luck. The really odd part is, though I've even deleted all System and User DSN's, and deleted and rebuilt my SQL Server connection in TDA, the error message that TDA gives me still seems to indicate that it's trying to use the old ODBC driver - despite the fact that the connection has been reconfigured to use the new one:
{{Odbc("DRIVER=SQL Server;SERVER=tnnass-sql01;UID=;APP=Toad for Data Analysts;WSID=TNNASD-BN;DATABASE=Employee Core;Trusted_Connection=Yes;PWD={0};,")}}."dbo.AE" AE
I would expect to see:
{{Odbc("DRIVER=SQL Native Client 10.0;SERVER=tnnass-sql01;UID=;APP=Toad for Data Analysts;WSID=TNNASD-BN;DATABASE=Employee Core;Trusted_Connection=Yes;PWD={0};,")}}."dbo.AE" AE
Anyone have any ideas? We would really like to get this working, as we could eliminate some 30 Access DB's that are used as intermediaries.
To configure TDA to use a specific ODBC driver you can do one of two things:
Right click on the SQL Server connection. Using the properties dialog, select the Advanced Tab and choose the driver you want to use when executing a cross-query. See screenshot.
-or-
Connect directly to SQL Server using the ODBC type and then use that in a cross-query.
I did method # 1, but the error message (and display of the SQL involved) still seems to indicate that it’s trying to use the old ODBC driver instead of the new one. I am loading this query out of a .tsm file where I’ve saved it - is there a setting that’s getting saved in the file that’s over-riding the connection setting in TDA?
As to the second solution, I’m afraid I don’t quite know what you mean - I don’t typically use ODBC on a regular basis.
Debbie Peabody wrote:
To configure TDA to use a specific ODBC driver you can do one of two things:
Right click on the SQL Server connection. Using the properties dialog, select the Advanced Tab and choose the driver you want to use when executing a cross-query. See screenshot.
-or-
Connect directly to SQL Server using the ODBC type and then use that in a cross-query.
Connection information including the old driver is included in the Query Builder file. You can see in the query below that the Driver information is the very first part after the “{{Odbc…” part.
I suggest that you do step one from below(set the new driver in the Advance Connection tab). Then go to the Query builder and build the simplest query joining from the different databases and tables. Execute it and get some results. Go to the Query tab of the Query Builder and copy the Query. At that point you could manually edit your query and the tsm file. Just copy and paste the full Odbc string for each table.
Debbie
SELECT ADDRESS.ADDRESS_ID
, ADDRESS.ADDRESS
, REGION.REGION_NAME
FROM
{{Odbc(“DRIVER={Oracle in OraClient11g_home2};SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ALV208534.prod.quest.corp)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DEB10G)));DBQ=DEB10G;UID=DEB;PWD={0};,121AC12B052173920E24E5EBB409F03A”)}}.“QUEST_STAGE.REGION” REGION
INNER JOIN
{{Odbc(“DRIVER=Adaptive Server Enterprise (TDA);UID=sa;SERVER=irv000002610b;PORT=5000;APP=Toad for Data Analysts;PWD={0};DB=QUEST_STAGE;,7B308B5B6E499169”)}}.[dbo.ADDRESS] ADDRESS
ON (CVAR(REGION.REGION_ID) = CVAR(ADDRESS.REGION_ID))
Ah, well, that certainly explains it then. I did as you suggested and created the simplest join I could between the two sources.
The good news is: it's showing the new SQL Server driver in the error message.
The Bad News is: I'm still getting this error message:
Error:4/28/2010 0:00:07.718: [Microsoft][ODBC Microsoft Access Driver] Reserved error (|); there is no message for this error.
Any ideas?
Debbie Peabody wrote:
Connection information including the old driver is included in the Query Builder file. You can see in the query below that the Driver information is the very first part after the "{{Odbc..." part.
I suggest that you do step one from below(set the new driver in the Advance Connection tab). Then go to the Query builder and build the simplest query joining from the different databases and tables. Execute it and get some results. Go to the Query tab of the Query Builder and copy the Query. At that point you could manually edit your query and the tsm file. Just copy and paste the full Odbc string for each table.
Debbie
SELECT ADDRESS.ADDRESS_ID
, ADDRESS.ADDRESS
, REGION.REGION_NAME
FROM
{{Odbc("DRIVER={Oracle in OraClient11g_home2};SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ALV208534.prod.quest.corp)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DEB10G)));DBQ=DEB10G;UID=DEB;PWD={0};,121AC12B052173920E24E5EBB409F03A")}}."QUEST_STAGE.REGION" REGION
INNER JOIN
{{Odbc("DRIVER=Adaptive Server Enterprise (TDA);UID=sa;SERVER=irv000002610b;PORT=5000;APP=Toad for Data Analysts;PWD={0};DB=QUEST_STAGE;,7B308B5B6E499169")}}.[dbo.ADDRESS] ADDRESS
ON (CVAR(REGION.REGION_ID) = CVAR(ADDRESS.REGION_ID))
Are you talking about my quick-and-dirty query, or the one I've been having issues with?
The one I'm having issues with hasn't changed:
SELECT AE.SuperName AS Supervisor
, AE.[NAME] AS [Name]
, IDW.W
, IDW.SR
, IDW.AC
, IDW.H
, IDW.C
, IDW.WORDATE
, IDW.SCHDAT
, IDW.WIDATE
, IDW.WFINDATE
, IDW.WSTAT
, (IIF (IDW.WSTAT = '1','I', IFF (IDW.STAT='2','R',IIF (IDW.WSTAT='3','C',IIF (IDW.STAT='4','D'))))) AS "Job Type"
, (IIF (IDW.Q_L=1,'V',IIF(IDW.Q_L=2,'H',IIF(IDW.Q_L=3,'V-H',IIF(IDW.Q_L=4,'C',IIF(IDQ.Q_L=5,'V-C',IIF(IDW.Q_L=6,'H-C',IIF(IDW.Q_L=7,'V-H-C')))))))) AS Products
FROM
({{Odbc("DRIVER={Microsoft ODBC for Oracle};SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=99.99.99.99)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=id)));UID=NB;PWD={0};,")}}."GGS.ID_W" IDW
CROSS JOIN
{{Odbc("DRIVER={Microsoft ODBC for Oracle};SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=99.99.99.99)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=id)));UID=NB;PWD={0};,")}}."GGS.ID_CT" IDC
ON (CVAR(IDW.AC) = CVAR(IDC.AC)) AND (CVAR(IDW.H) = CVAR(IDC.H)) AND (CVAR(IDW.C) = CVAR(IDC.C)))
LEFT OUTER JOIN
{{Odbc("DRIVER=SQL Server;SERVER=tnnass-sql01;UID=;APP=Toad for Data Analysts;WSID=TNNASD-BN;DATABASE=Employee Core;Trusted_Connection=Yes;PWD={0};,")}}."dbo.AE" AE
ON (CVAR(IDW.WHO) = CVAR(AE.CSR))
WHERE IDW.AC IN (5,3)
AND IDW.WSTAT IN ('1', '2', '3', '4')
AND IDC.STAT IN ('1', '2', '3', '4')
AND IDW.SCHDAT <= SYSDATE - 29
AND IDW.WFINDATE IS NULL
ORDER BY IDW.WORDATE ASC
Still shows the old ODBC driver, but that apparently isn't the issue as I'm still getting the same error message.
Debbie Peabody wrote:
Copy and paste the current SQL for me to review.
I made the changes you suggested - I'm still getting the same error - there's got to be something else that ODBC doesn't like. Here's the current syntax - and I must admit, I'm so used to doing things in Oracle that I may be making a simple mistake like with the SYSDATE:
SELECT AE.SuperName AS Supervisor
, AE.[NAME] AS [Name]
, IDW.W
, IDW.SR
, IDW.AC
, IDW.H
, IDW.C
, IDW.WORDATE
, IDW.SCHDAT
, IDW.WIDATE
, IDW.WFINDATE
, IDW.WSTAT
, (IIF (IDW.WSTAT = '1','I', IFF (IDW.STAT='2','R',IIF (IDW.WSTAT='3','C',IIF (IDW.STAT='4','D'))))) AS "Job Type"
, (IIF (IDW.Q_L=1,'V',IIF(IDW.Q_L=2,'H',IIF(IDW.Q_L=3,'V-H',IIF(IDW.Q_L=4,'C',IIF(IDQ.Q_L=5,'V-C',IIF(IDW.Q_L=6,'H-C',IIF(IDW.Q_L=7,'V-H-C')))))))) AS Products
FROM
({{Odbc("DRIVER={Microsoft ODBC for Oracle};SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=99.99.99.99)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=id)));UID=NB;PWD={0};,")}}."GGS.ID_W" IDW
INNER JOIN
{{Odbc("DRIVER={Microsoft ODBC for Oracle};SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=99.99.99.99)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=id)));UID=NB;PWD={0};,")}}."GGS.ID_CT" IDC
ON (CVAR(IDW.AC) = CVAR(IDC.AC)) AND (CVAR(IDW.H) = CVAR(IDC.H)) AND (CVAR(IDW.C) = CVAR(IDC.C)))
LEFT OUTER JOIN
{{Odbc("DRIVER=SQL Server;SERVER=tnnass-sql01;UID=;APP=Toad for Data Analysts;WSID=TNNASD-BN;DATABASE=Employee Core;Trusted_Connection=Yes;PWD={0};,")}}."dbo.AE" AE
ON (CVAR(IDW.WHO) = CVAR(AE.CSR))
WHERE IDW.AC IN (5,3)
AND IDW.WSTAT IN ('1', '2', '3', '4')
AND IDC.STAT IN ('1', '2', '3', '4')
AND IDW.SCHDAT <= DATE() - 29
AND IDW.WFINDATE IS NULL
ORDER BY IDW.WORDATE ASC
Debbie Peabody wrote:
I think it is complaining about two things.
Change sysdate - 29 to Date() - 29. Sysdate is Oracle specific
Change the CROSS JOIN keyword. I can get the same error you are getting when using this join type
It would make it easier for me to help if I had the DDL for the three tables. Can you copy and paste the DDL from the Script Tabs for each table? If you want you can zip and send in private email.
2)In your first iif statement you have a bad keyword of IFF and you are using switing back and forth between IDW.STAT and IDW.WSTAT
3)Neither of your iif statements have a default value.
I attached my script that works. You will need to cut and past your connect strings. Doing this carefully should get it to work for you. Let me know if that is the case.
Debbie
Okay, I created some dummay tables and tweaked the query to work for me. I found out a few things along the way.
I can reproduce the error [Microsoft][ODBC Microsoft Access Driver] Reserved error (|); there is no message for this error. This seems to occur when the connection string is split up on more than one line. If you happen to open up a cross-Query in the editor the parse splits the table/connection strings (the one’s starting with ({{Odbc("DRIVER=) into several lines. So make sure all connection string are on one line DebsTweakWorks.sql (1.65 KB)
headdesk I’ve been at this too long. I also found a few other mistakes based on table names when I sanitized the SQL - apparently I saved, and then kept trying to use that version - but I’m having two different issues now.
When I go in and I make the changes to my existing SQL that you’ve mentioned, I’m still getting the same error message. If I take your example, and just copy & paste in my ODBC strings, I’m getting an ORA error about a missing right paren that I can’t figure out where I’m missing it from. The cursor in the editor jumps to the line that (I’m guessing) it thinks is causing the error, but I’ve got the same number of Left and Right parens there, so I’m not sure what the deal is.
Do you want me to email you both so you can look at them? I keep getting the feeling I’m making a stupid mistake here.
Oh, and the ODBC strings don’t wrap in my editor - they apparently wrap when I posted them - unless the entire thing - Join Syntax included - has to be on one line?
Debbie Peabody wrote:
2)In your first iif statement you have a bad keyword of IFF and you are using switing back and forth between IDW.STAT and IDW.WSTAT
3)Neither of your iif statements have a default value.
I attached my script that works. You will need to cut and past your connect strings. Doing this carefully should get it to work for you. Let me know if that is the case.
Debbie
Okay, I created some dummay tables and tweaked the query to work for me. I found out a few things along the way.
I can reproduce the error [Microsoft][ODBC Microsoft Access Driver] Reserved error (|); there is no message for this error. This seems to occur when the connection string is split up on more than one line. If you happen to open up a cross-Query in the editor the parse splits the table/connection strings (the one’s starting with ({{Odbc("DRIVER=) into several lines. So make sure all connection string are on one line
