Wierd ODBC error overnight...

[Former Member]

We have a program that runs continuously and at 1am, fires off a stored procedure and then starts processing accounts that have changed during the day.

However, the last night it has recorded 24,281 errors of the same thing per account: [Sybase][ODBC Driver][SQL Anywhere]Table '^^22072007^^_1114^^_22072007^^' not found.

I don't have a table of this name - does anyone have any ideas?!

[Former Member]

Which version and ebf are you using ? The same happened to me with trigger on table involved in materialized view This error was solved with latest EBF OF SQL Anywhere 16.

From readme of latest ebf:

Build #1829 - Engineering Case #756681

In rare cases, the server could have returned an error like: "Table '^^22072007^^_759^^_22072007^^' not found" for an internal temporary table if an INSERT, UPDATE, or DELETE statement triggered the change of an immediate materialized view. The problem only happened when the very first INSERT, UPDATE, or DELETE of the table in a connection was executed in a nested block, for example a trigger or procedure. In this case subsequent DML operations on the table may then have returned the above error. This has been fixed.

[Former Member]

Unfortunately, I am using v11.0.1 EBF 2661 (x64) - am I knackered then?

Is it something that has changed that is causing this now? (the stored procedure has recently been extended to perform some more UPDATES!)

Thanks.

[Former Member]

I have read 11.0.1 docs until the latest ebf 3158 but the problem seems unresolved. The error happened to me when a trigger update on table causes an update on a differente record of the same table and the table was involved in a materialized view. Release 11.0.1 it's not supported for bug fixes, I think you have to migrate or change something in your procedure.

Good luck

[Former Member]

Thanks for that - I'll change the stored procedure short-term.

Long-term - do you know of any "breaking changes" between 11.0.1 and latest 16?

[Former Member]

I migrate from 11.0.1 to 16 with no problem. Our db consists on more than 1.000 tables, complex triggers, views and more then 300 store procedure. We don't use many of new features but after one year in production we have no problem. Pay attention in new features when you create a new database because many new features are about security and permissions.

[VolkerBarth]

FWIW, according to the CR note this bug has been fixed in v16.0.0.1829 and v12.0.1.4071, so it's available in the 12.0.1 version as well - that may present a "smaller upgrade path".

@da69plc: Do you use immediate materialized views at all?

Note that you should be able to run your v11 database unchanged on a v12.0.1/v16 database engine to test whether that error will still appear...

[Former Member]

Yes - there are 6 materialized views defined in the schema....

I removed all lines from the stored procedure that is called before the main overnight process loop (leaving just an empty procedure) and moved them into an event (at midnight) and the errors still happened so I am changing the order of the CALL's from within the event to see if this will fix it...

[Former Member]

Adding an UPDATE statement before the first CALL fixed the problem.

Thanks for your help guys.

[VolkerBarth]

Glad to hear that - it's great that those CR notes contain work-arounds, isn't it? (Who would have thought that only the initial DML statement would cause such an error, and only when contained within a code block...?)