MICROS RES 4.10 to 5.2 Upgrade Error 1

[Moregelen]

Has anyone seen this particular error before? I don't have time right now to really dig into it so I'm hoping someone is familiar with it.

3/28/2014 11:21:41 AM Could not execute statement. Column 'table_id' not found SQLCODE=-143, ODBC 3 State="42S22" File: "CreateViews.sql" on line 56724, column 1
3/28/2014 11:21:41 AM **Error Updating Database**, Database Update Failed!
3/28/2014 11:21:41 AM Check Micros dm.log for errors.
3/28/2014 11:21:41 AM Database was upgraded successfully but failed to apply micros scripts. Try to start the service and use "DM -u" to update the database.

 

[DJensen999]

I am both familiar and unfamiliar with this one. Familiar, in that many many 5.x upgrades will break due to eccentricities in the existing databases. Unfamiliar, in that I don't know which one this is just from the description...

(10 minute research delay)

Line 56724 in that script is a call to MICROS.sp_validateviews(). My guess is that you have a couple of views which are broken and are not able to be re-compiled under the new Sybase engine. Pop open Sybase Central, go to the views, look for ones that are not marked "Valid." Try "Recompile and Enable" on each first. If that fails, copy the view from a known good 5.2 database.

 

[DJensen999]

Whoops. The last part there should be "copy the view from a known good 5.2 database, then try 'recompile and enable' again.

 

[Moregelen]

Could you explain your process for finding that? I've never actually run Ito an issue when upgrading one my databases and was lost as to a starting point for attacking this error.

Thanks!

 

[DJensen999]

I had to reverse-engineer the message myself.

It was about 3-6 hours of finding where the temporary upgrade scripts were at, digging through the scripts themselves, reading the stored procedures, learning what a "view" was, finding DBA passwords, getting into Sybase Central with DBA credentials to make changes, determining the invalid views, and then figuring out how to fix them.

In my last time across this error, I had three invalid views - fixing one by copying it from a known good system allowed it and the other 2 dependent views to compile. After that, the database upgrade was successful.

 

[Moregelen]

Hmmm. Do you recall where the scripts are kept? I know how to do all of that stuff; I have the dba passwords, I deal with views and stored procedures on a regular basis. My problem is I have freaking clue where those upgrade scripts even are.

 

[Moregelen]

Oh, and what you've given me so far has helped A LOT. I went in and sure enough I have a good 20 or so invalid views, all of which are invalid because of a view they all link back to that got corrupted somehow. Once I fixed it I was about to recompile and enable them all and the upgrade went right through.

So, thanks a ton!

 

[DJensen999]

Yeah, that was an hour or more for myself as well...

I believe it creates a new folder under c:\users\YOURUSERNAME\appdata\local\temp\UNIQUEFOLDERNAME - but I would need to run another upgrade to verify. I can zip up and post the upgrade scripts for you, as I stashed them for future use after two upgrades broke.

 

[Moregelen]

If you already have them that would be awesome. Otherwise I can try and track them down myself. The help you gave me already has saved me a huge headache.

 

[DJensen999]

Upgrade scripts linked below. Glad I could help on this one!