COM Error Code 80040e4b (ORA-03147) in OraOLEDB The Accessor is not a Parameteraccessor.

Hi dkrnic,

That's a really wierd message code.

I don't recall ever seeing it and can't find it documented anywhere.


Kent

did you already look in the oracle metalink?
do you have access to metalink?

Yes. I have access to metalink, but I haven't found anything appropriate.

There's a reference to "OraOLEDB error '80040e4b' : Accessor is not a parameter" in this forum which may help.  
It seems it's been an ongoing problem with the last posting in Nov 2007.  
You may want to consider OLEDB version 10.2.

http://forums.oracle.com/forums/thread.jspa?threadID=375592&tstart=1

I've seen that discussion, a long time ago.
I've applied 9.2.0.7 and thought that the problem was gone,
but it wasn't. I couldn't reproduce it with stress tests but it
occurs in production as regularly as before.

Updating the client SW to 10.2 was on my mind, but it is a
can of worms. I may fix this problem and cause several
others to crop up.

The server is 9.2.0.7 on a Linux box.
I'd rather keep client SW in sync with server.

It was a difficult question.
Too difficult.

dkrnic,
There are a couple of options you may wish to consider, *if* you get no further recommendations...

First, it's now unlikely many experts will find this question and respond because it's now slipped far down the list of unanswered questions!  Many questions are asked daily in this XP Topic Area.  Therefore may i suggest you create a new (20 point) question which is linked to this question http:Q_23145665.html 
Be sure to quote the address of this question in your new one, and advise everyone to only reply in this original question.  

Then, if you get no useful suggestions, scroll down to the title "How do I close a question?" you'll see a sub-heading "No solution has been given to your problem".
https://www.experts-exchange.com/help.jsp#hs5

Here you can post a question in >>>
https://www.experts-exchange.com/Community_Support/General/   
asking the Moderators to delete your question.

Hope that helps.  Please post back if you require more assistance.

dkrnic,

I happened to see your post in the CA zone, and did a little poking around because it seems like I used to see that error a lot in a different context. I'm not any kind of Oracle whiz, so you can take this for what it's worth -- but it seems like while this isn't an empidemic problem, it would be cool to see if there's a solution that will work for others.

As you are probably aware, the 80040e4b error is from the OS, not Oracle. Microsoft does a really lousy job of documenting its error messages, but it's shown on one list as:
DB_E_BADACCESSORTYPE: The specified accessor was not a parameter accessor (ref: http://megos.ch/support/doserrors_e.txt).

Of course, we knew that. Essentially, it means there is a problem with the C++ program's connection to the database; there's a big part of me that says that it isn't the database itself, or possibly even the program, but in the part of the database system used for the connection. When people use Access, for example, as a web database, one of the problems is always whether to use OLE or ODBC; one seems easier to set up, while the other is more reliable.

http://www.tech-archive.net/Archive/Data/microsoft.public.data.oledb/2006-03/msg00040.html implies that using an ODBC connection could solve the problem although it doesn't mention Oracle specifically. http://support.microsoft.com/kb/327557 mentions Oracle, but not your specific problem.

I did find a link to one article at gridview.org that looked promising, but the site is a mess -- they can't find the article, even if you search from the menu. Its title was "When you use the Microsoft Remote provider to run a parameterized query against a remote database, the Requery method of the ADODB.Recordset object fails with the following error message: Unknown Error (80040e4b)" -- and it is listed under articles having to do with MDAC 2.7 and 2.8. Have you tried updating the MDAC? Or even reinstalling it (that used to solve a lot of problems back when it was MDAC 2.1 and 2.2).

Sorry that this is all guesswork -- but maybe something will spark an idea for you.

ep

The MDAC is up-to-date - 2.8, but thanks anyway.
I've tracked down the article you mentioned.
It's MS Article Q268972.
Not relevant.

Just out of curiousity -- is the problem you describe in your initial question something that started happening recently? Or is it one of those things that has always been there but has become enough of an annoyance that you're trying to deal with it?

I know that when MDAC 2.2 came out, it actually broke a few things (notably some ASP systems) that worked fine under MDAC 2.1 -- so I'm thinking that if you had upgraded it, that could be the source of the problem.

The other thing I'd ask you about -- and if you've already tried this, I apolgize for asking -- is if you can change the connection string in the C++ program from OLE to ODBC, just for giggles and grins. I understand if you can't -- but it's just a thought.

ep

The current connection string is
"Provider=OraOLEDB.Oracle;"

What would be the string for ODBC ?
"Provider=MSDAORA;" ?

From everything I've found, the current connection is ODBC.

http://www.connectionstrings.com/?carrier=oracle shows the ODBC connection for Oracle as being

Driver={Microsoft ODBC for Oracle};Server=myServerAddress;Uid=myUsername;Pwd=myPassword;
or
Driver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;

And the Microsoft ODBC driver for Oracle (which would replace the bracketed phrase) is shown at http://www.adp-gmbh.ch/ora/network/odbc_connection_string.html as being

Provider=OraOLEDB.Oracle;Data Source=ORA10;User ID=fred;Password=freds_secret;

Also, if you look a little further down the page at connectionstrings.com, the OLE connection string for an Oracle database using the Microsoft driver is

Provider=msdaora;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;

So it might just be that the C++ program doesn't like the Oracle driver; would it be a lot of work for you to change it to the Microsoft driver to see what happens?

Thank you, ericpete.

I'll try the MS driver. It's not easy to verify if that worked, because the error comes only
after longer period of time, about a week at least, but sometimes after a month. If it works
for 2 months without the error. than you earned yourself the bounty. Should the error
re-occur I'll report immediately.

On the other hand,
the capabilities of OraOLEDB.Oracle and MSDAORA are not really compatible.
Switching over to MSDAORA causes many different problems.
It's not a single adjustment of the connection string, it's much much more.

I'm mostly guessing here, so I won't be at all offended if you request deletion for this. I'll also see if I can get the Mods to find you a couple of more knowledgeable experts. My "expertise" is Access (and a little SQL) and web pages, not Oracle and a C++ program -- so at best my suggestions are lucky stabs in the dark.

The other option you have is to see if there's an update to the driver that might have cured the illness...

ep

Educated stabbing around is OK.
That's how I find my way out of problems when I'm in the dark.

Thank you for trying.

My working hypothesis at the moment is that there is something wrong in the way
DLLs are language-dependent. There might be some residual matching impedance
when a language-dependent layer communicates with a generalized driver.

Well, I had a conversation last night with the top C++ Expert on the site (jkr), and his comments were a) he doesn't do databases, and b) that if it were something in the C++ programming, that it would fail right away, and would consistently fail.

That got me thinking about the phrasing of your original question: That it will complete transactions for a good period of time, but will then throw an error, and that the frequency of the errors will start increasing until it gets to the point where your only solution is to restart the program.

Can you explain a little more about what is actually happening? Because the inevitability of the failures implies (to me) some kind of buffering issue -- kind of like deleting emails in Outlook that actually just go to the deleted items folder and don't really get deleted. That will gradually slow your email program down, because it considers the Deleted folder just like the rest.

So I'm wondering if it starts happening after X number of transactions -- if there is some threshhold it reaches at which point the programs starts saying "wait a minute -- I can't handle this much information, so I'm going to throw an error." It sulks for a transaction, then processes a few more, then sulks again for a couple of transactions, processes a few more, sulks a little longer...

ep

Yes, you paraphrased it well.
It does feel like some resource getting exhausted but it doesn't show.
The process does not grow, does not accumulate handles, semaphores or the like.
It doesn't start after X transacation. It starts after X, Y, or Z transactions,
which might be a couple of days a week or a month.

Okay... this is where musing might be about as appropriate as anything... *grin*

There's no pattern (X number of transactions) between zero-day and the first failure. Is there any pattern between failure 1 and failure 2, and between failure 2 and 3? Or can it be X transactions between f1 and f2, and then sometimes it goes straight to f10, where you have to completely restart the program?

We've discussed transactions; are there subtransactions that could be the issue? In other words, if you're processing invoices, you may have more or less the same number of transactions over a period of time, but the number of line items that would need to be processed could vary wildly. Could a subsystem be the actual problem?

Is there any chance that the failures are related to "dirty" records -- records that have been partially finished but not fully committed?

You might be on to something, ericpete.

I have a log of the errors the last time they occurred.
(I could dig out all others too, they're all archived, but this one will suffice.)

I've already made a sort of diagram plotting the time differential between
successive transactions. I've learned a little more about the transactions
(and about plotting software). I'll post a revised chart as soon as I can,
so that you can try squinting at it from different angles in search of a pattern ;-)

Even the log itself might be enough. We can always import it into a spreadsheet and fiddle with it.

OK. I found it. I have attached file "ora-3147.log" as semi-colon-separated values.
What you see is a part of the log an hour before the first #3147 error until the
programm was restarted. First 20 lines are at about 3 minutes apart logging a
new feature of the program. It's a triplet of push-start-push actions which I
collapsed into one line. There are other actions at about 30 seconds apart
which only get logged if they run into a problem so we don't see them.

At 12:59:09 the error ora-3147 occurs for the first time after almost 3 days of
error-free operation. It ruins the push-start-push which started at 12:58:54
but 3 minutes later another triplet gets completed. Then there are
2 errors 1 success, then
3 errors 1 success,
1 error   1 success
5 errors 1 success
2 errors 1 success
8 errors 1 success
1 erorr   2 successes
2 errors 1 success
4 errors 1 success
52 errors 1 success
13 errors 1 success
and since then at 17:40:14 the error is permanent.

I fail to see any pattern here, but 2 pairs of eyes can see better than one.
ora-3147.log

German, huh... *L*... It figures it would be one of those languages I can't even guess at.

Okay... I assume that PFDB-Prozeß-IDs is the database, correct?

What I have done is taken your log, and then color-coded it to show each group of Push-Start-Push sequences as they are successful. Most of the time, the problem seems to start when the first Push times out (if I'm reading the log correctly). Can you increase the time it allows to do that sequence?

You'll note that it ALWAYS fails on the first Push of the three step sequence. However, I have no idea what the log is saying... If you can help me there, maybe I'll be able to find something.

ep
23145665-ora-3147.xls

for the "non-germans":
the error message:
Der Accessor ist kein Parameteraccessor
in english applications would be:
The Accessor is not a Parameter accessor

I think we need to see the (SQL) code of the DoZyklus method :)

what I found here:
http://forums.oracle.com/forums/thread.jspa?threadID=375592&tstart=0
http://support.microsoft.com/kb/327557
which is what confirms what I found recently in one of my own applications, is the MS is sometimes picky about data types that are "bigger" than what it expects. while I had the problem in a linked server from SQL Server to Oracle, the error was the same, as was the solution.

hope this helps

You have interpreted it correctly,ericpete. It is the symptom. The triple push-start-push
can't be completed before the start-stage is announced. The way I look at it to visualize
the flow is I use "vim", position the cursor at the string "PushPFDBBerichtAuftrag" and
press the pound sign "#", which causes all instances of that string to be displayed in an
enhanced mode of one's own choice. On a large enough screen you can see all 3 steps
in one line and it is clear that the error has nothing to do with any of the causes
discussed in other threads like those quoted by "angellll". Each time "push" starts the
sequence "push-start-push" it places the same query to the database (PFDB) and
gets exactly the same answer - I can easily see that the system was quiescent at
the time. If any of the returned values were out of limited range acceptable to MS ADO
it would log the error every time. It would not be able to complete the sequence at all.
But we see that it occasionally "comes through" with a successful cycle.

The other possibility that there is some mismatch in the accessor variables, like
sending an empty string for an OUT var can also be excluded. It doesn't happen.
There is a stored routine which gets called under exceptional circumstances with
a single IN var (setter, not getter) and it never makes any problems. At the time
of failure it was not the case.

I am really impressed by the community's willingness to discuss this thread.
I would like to solve this problem and I will. Your help means much to me.

Is there any possibility of posting the SQL code of the DoZyklus method, as angelIII suggested?

You can also upload a screen shot that will give us an idea of what you're seeing...

Sorry I was away abroad since Friday last.

I'll post a tkprof of the transactions asap.

I have some indication that old versions VS (for instance 3 work better as 6 with Oracle).

Also please check if there is not a default firewall turned on on the XP.
Also check for hidden firewalls like TrendMicro, AlarmZone and antiviruseses like McAffey.
 
Some of them (not all!) have to be uninstalled ...

Thank you for the suggestions schwertner.

Since it is a closed network we regularly disable built-in firewall and
do not install any virus protection programs on those machines.

I have done some tracing of the queries involved in push-start-push sequence
which is where the error manifests itself. The enclosed file contains a tar of
all trace files at 00:07 am today. The biggest file carries the bulk of push-start-push
which is done every 2:30 minutes. The other smaller files are mostly part of a
different cycle which is done every 30 seconds. The tar is compressed by
bzip2 but I had to add one of the acceptable extensions in order to upload it.
00-07.tbz2.log

Try to upgrade the Oracle DB to 9.2.0.8 as last chance.
In some sources this is reported as bug (and it is bug).
Also changing drivers can help.

It's a valuable tip shwertner. I didn't know there was 9.2.0.8.

As it often happens when a program meets irresistible force, it is necessary
to restart it, which is a differente way of upgrading to 9.2.0.8, without
changing anything and invoking God knows what other bugs are skunking in
9.2.0.8. Ever since we scheduled a task of restarting the offending program
on each Sunday afternoon, the program has not been stuck with ORA-03147.

I am very grateful to ericpete and schwertner for the useful stabbin in the dark.
I wish I could spend at least a part of the war chest for the thoughtfull suggestions
and perseverance, even though the problem remains a mistery. Please let me
know if that's possible before I close this thread.

Thank you all for bearing with me.

Thanks for the points; wish we could have helped more.