Re: Odbc Call failed

"Tony Hunt" <tonster_at_bigpond.net.au> wrote in message news:bzFm7.27575$bY5.155196_at_news-server.bigpond.net.au...
> That error is a nightmare because Access spits it out with no explanation!
>
> I've had problems with different (but compatible) versions of drivers for
> client & server. 8.1.7 client not good in some cases with 8.0.5 server.
>
> As much as I've used Access I never knew you could call those Oracle SQL
> functions. I always use the AccessVB equivelant, suach as DateValue, CStr
&
> IIf rather than TRUNC, TO_CHAR & DECODE.
>
> "Ravi Patel" <ravi197_at_yahoo.com> wrote in message
> news:91f46d00.0109070943.6a68b1a3_at_posting.google.com...
> > Hi
> >
> > I have 10 passthrough queries in Access2000 that connects to Oracle
> > database.
> > I have scheduler setup to run those queries everynight. All queries
> > run just fine except one. In that query i get "Odbc call failed"
> > error.
> >
> > Here is the SQL syntex for my query:
> >
> > SELECT m.messageid,
> > mt.stringvalue messagetype,
> > m.completedate kanacompletedate,
> > TRUNC(m.completedate) kanacompleteday,
> > TO_CHAR(m.completedate, 'HH:MI:SS AM') kanacompletehour,
> > LTRIM(TO_CHAR(m.completedate, 'DAY'))
> > kanacompleteweekday,
> > m.completedate,
> > TRUNC(m.completedate) completeday,
> > TO_CHAR(m.completedate, 'HH:MI:SS AM') completehour,
> > LTRIM(TO_CHAR(m.completedate, 'DAY')) completeweekday,
> > m.elapsedtime,
> > f.label queuename,
> > d.label deptname,
> > e.emailaddress,
> > DECODE(mav.suggest,-1,'Manual
> > Response',DECODE(mav.resptype,6,'Auto Response',2,'Auto Suggest','No
> > Response')) response,
> > me.stringvalue messageevent
> > FROM kc_message m, kc_messagetype mt, kc_folder f, kc_department d,
> > kc_emailaddress e, messageautomationview mav, kc_messageevent me
> > WHERE m.type = mt.messagetypeid
> > AND m.queueid = f.folderid
> > AND f.departmentid = d.departmentid
> > AND m.recipientid = e.emailaddressid
> > AND m.completeeventid = me.messageeventid
> > AND m.messageid = mav.messageid(+)
> > AND TRUNC(m.completedate) >= TRUNC(sysdate-4)
> > AND TRUNC(m.completedate) < TRUNC(sysdate)
> > ORDER BY m.completedate;
> >
> >
> > Thanks in Advance
> >
> > Ravi Patel
>
>

Just FYI:

By using Vb functions in a query on an Oracle database, you will *force* the *entire* query to be run on your client! This is for me one of the major reasons to stay away from ODBC as much as possible. The performance usually is just plain terrible

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Sun Sep 09 2001 - 05:49:36 CDT