Path: news.easynews.com!easynews!feed2.onemain.com!feed1.onemain.com!newsfeed.online.be!sn-uk-xit-01!10.10.123.11.MISMATCH!sn-uk!sn-post-01!supernews.com!news.demon.nl!sybrandb.demon.nl!not-for-mail
From: "Sybrand Bakker" <postbus@sybrandb.demon.nl>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Odbc Call failed
Date: Sun, 9 Sep 2001 12:49:36 +0200
Message-ID: <tpmou83bvr5344@news.demon.nl>
Reply-To: "Sybrand Bakker" <postbus@sybrandd.remove-this.demon.nl>
References: <91f46d00.0109070943.6a68b1a3@posting.google.com> <bzFm7.27575$bY5.155196@news-server.bigpond.net.au>
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
X-Complaints-To: abuse@nl.demon.net
NNTP-Posting-Host: sybrandb.demon.nl
X-NNTP-Posting-Host: sybrandb.demon.nl:212.238.21.78
X-Trace: news.demon.nl 1000039363 news:58242 NO-IDENT sybrandb.demon.nl:212.238.21.78
Lines: 76
Xref: easynews comp.databases.oracle.misc:68501
X-Received-Date: Sun, 09 Sep 2001 05:42:34 MST (news.easynews.com)


"Tony Hunt" <tonster@bigpond.net.au> wrote in message
news:bzFm7.27575$bY5.155196@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@yahoo.com> wrote in message
> news:91f46d00.0109070943.6a68b1a3@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