| Author |
Topic |
|
rme8494
Yak Posting Veteran
98 Posts |
 Posted - 2007-06-13 : 08:33:24
|
| Good morning everyone, I'm having an issue here where an open query to an Oracle database produces the following error in SQL Analyzer.Server: Msg 7321, Level 16, State 2, Line 3An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'. [OLE/DB provider returned message: [NCR][ODBC Teradata Driver][Teradata RDBMS] Syntax error: expected something between '(' and the word 'cc'. ]OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80040e14].The line of code the error is referring to is this:SUBSTR(ex.EXTERNAL_ID,1,9)BAN,cc.BA_NO,decode(cc.CYCLE_SEQ_NO ...I believe the error has something to do with the "decode" function in the oracle part of the query because I ca remove that party of the query and it errors on the next decode function. I googled last night looking for an answer and never found one. Anyone her have any suggestions?? Thanks in advance,Ryan Ryan EverhartSBCSBC. Going Beyond the Call! |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-13 : 10:04:12
|
When I get a "syntax" error in an OPENQUERY against Oracle I re-run the guts of the query directly on Oracle using Oracle tools (or Toad or somesuch). Then you are likely to get a much more meaningful error, more easily fiddle with the query until it works etc., and then feed that back into the OPENQUERYDunno if that helps though Kristen |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-06-13 : 10:10:08
|
Maybe the version of ORACLE database you are using is too old to have DECODE function supported in it  I believe DECODE() was introduced in ORACLE 8.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
rme8494
Yak Posting Veteran
98 Posts |
Posted - 2007-06-13 : 11:23:15
|
| Kristen and Harsh,Thanks for the replies I have taken the guts out of the query and run them against the Oracle database and they run fine. The query is from the DBA of the Oracle box even, I was hoping I could just plug and play!Any other thoughts?RyanRyan EverhartSBCSBC. Going Beyond the Call! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-06-13 : 11:38:11
|
| Why are you using the ODBC Teradata Driver? Why not an Oracle ODBC driver?CODO ERGO SUM |
|
|
|
rme8494
Yak Posting Veteran
98 Posts |
Posted - 2007-06-13 : 11:59:53
|
Michael,I honestly have no idea why it says Teradata up there. I have other queries to this same database that work just fine. Here is a screen shot of my linked server set up. Doesn't really tell you much, does it... RyanRyan EverhartSBCSBC. Going Beyond the Call! |
|
|
|
sshelper
Posting Yak Master
216 Posts |
Posted - 2007-06-13 : 12:32:50
|
| Verify if your ENBP01 Data Source is really pointing to your Oracle database and not your Teradata database because based on the error message, it looks like you are connected to Teradata and not Oracle.SQL Server Helperhttp://www.sql-server-helper.com |
|
|
|
rme8494
Yak Posting Veteran
98 Posts |
Posted - 2007-06-13 : 12:48:19
|
SSHelper,Thanks for your reply, looking at the ODBC connection on the server it appears to be using an Oracle database... Ryan EverhartSBCSBC. Going Beyond the Call! |
|
|
|
sshelper
Posting Yak Master
216 Posts |
Posted - 2007-06-13 : 12:56:29
|
| Can you post the FROM clause of your SQL statement as well as a more complete DECODE statement.SQL Server Helperhttp://www.sql-server-helper.com |
|
|
|
rme8494
Yak Posting Veteran
98 Posts |
Posted - 2007-06-13 : 13:12:39
|
| SSHelper, Michael and everyone else that helped me, thank you so much for noticing the TeraData message in the error above. This issue had nothing to do with drivers and everything to do with operator error. I was trying to connect to the wrong linked server, once I changed it everything worked as expected. I apologize for wasting your time but it really did help me fix the problem.RyanRyan EverhartThe New AT&T |
|
|
|
|
MS SQL Open Query to Oracle Issue: Decode