Loaded dump from 12.5.4 to 15.7, Error 622 & Tables/Views not found

[Former Member]

Hi all,

I am getting desperate trying to fix that issue. I had to import a dump from an ASE 12.5.4 into a new database on an ASE 15.7. I therefore enabled the compatibility mode server-wide and then proceeded to the load which seems to have performed correctly. The database seems well imported, and I can access most of the tables/views/procedures (from my C# application), but a procedure is giving me troubles.

I am getting this error while executing a given procedure :

Error: 622, Severity: 20, State: 1
Opentable was passed a varno of 52. Object 'temp worktable' in database 'tempdb' already has that session descriptor in use. This occured while opening object id -1245236 in database id 2.

I realised this was caused by a view which is used in this procedure but which is weird is that the error is not thrown when running the SQL code itself, out of the procedure context.

I am using Toad for Sybase as a client, and realised I cannot access the data tab of the concerned view, Toad gives me the same error. So I cannot execute this view while it performs without any trouble on  the original server (production server which runs ASE 12.5.4).

I also realised, when trying to execute some pieces of the query individually that I get warnings about unfound tables (and that I should use sp_help to fix the problem). Although, the tables actually exists and I can access their data.

I have tried so many things and still cannot fix this problem. I've used 'upgrade_object', 'sp_recompile' on all tables, 'dbcc reindex' on all tables.

Would you have any idea where the problem could come from? Thank you very much

[Former Member]

Thank you for your advice.

I tried executing the stored procedure from isql and I still get the same error message.

I don't have any S-user access either so I unfortunately cannot open a case with the tech support.

Is there a way to find out whether I am concerned with the note 1963568 and that my version of ASE contains this bug?

I'll try to work around still. Thank you

[Former Member]

I ran the DBCC ORPHANTABLES command but no oprhan tables have been found. I still tried DBCC ORPHANTABLES('DROP') but it didn't fix my issue.

I am really getting hopeless. Have no clue what's going on, the hell

[c_baker]

You do not have to turn on compatibility mode to load a 12.5.4 database.

Did you drop and recreate the stored procedure/view definitions?

As this is a form of upgrade, there is a section in the manuals covering it:

How Compiled Objects Are Handled When Upgrading Adaptive Server

Chris

[Former Member]

Thank you for your reply.

Good to know that enabling compatibility mode before or after loading the load doesn't have any effect.

I didn't drop/recreate all the SP and views but I can tell you I recreated a similar view and it causes the same issue.

I had a look at your link yes. I have run 'dbcc upgrade_object' several times already, on the view and the procedures calling the view, and it won't solve anything.

I've seen a similar post about the problem I have when executing the code itself of the view in a "normal" adhoc query (errors saying the tables are not found) : SAP Sybase Forums - ASE - Backup and Recovery - PRoblem after restore - Object does not exist.

Could it be the same problem for me?

Although I've tried splitting the SQL query and executing the parts individually and it works without any trouble. Problem is when the 2 parts of the view are put together. It may be something that was possible with ASE 12.5.4 but not with 15.7 and that compatiblity mode doesn't solve. My view consists of an union a two selects where both have left joins, case when then else, not in and a final group by (adding an order by doesn't help, as I tried).

PS : I don't have access to the production server where the dump is actually from (but I could if it is really necessary)