Micros 4.1 SQL DB will not start

[grantsdale]

Hi all,

Having an issue with a customer I recently took over. Its a Micros 3700 (v4.10) setup inside a VM running XP.

They called me a couple hours ago with an issue, saying they couldn't get into the system. I've been working on it, but I can't get Sybase to fire up the SQL DB.

Here are the relevant lines from the log.

Changing [Restaurant] From [OFF] To [DATABASE]
Operational State going from 'OFF' to 'OFF', Target State set to 'DATABASE'
dwWin32ExitCode 0 (0x0) dwServiceSpecificExitCode 0 (0x0)
***ERROR*** Failed to start SQL Engine as a service.
***ERROR*** Will rest a bit and try to start it again.
dwWin32ExitCode 0 (0x0) dwServiceSpecificExitCode 0 (0x0)
***ERROR*** Failed to start SQL Engine as a service.
***ERROR*** Will rest a bit and try to start it again.
Op step changed from [0] to [1] after [runDBMS] [FAULT]
dwWin32ExitCode 0 (0x0) dwServiceSpecificExitCode 0 (0x0)
***ERROR*** Failed to start SQL Engine as a service.
Failed to start SQL Server


I've tried manually starting the service as well, no luck.
I don't know what to even try.

Thanks for any help.

 

[pmegan]

Ok, Try this
In SQL, run
Select * from micros.emp_def where obj_num = xxx
Replace xxx with the employee number you're using to sign in

Find the emp_class_seq

Now run this
Update micros.emp_class_def
Set ob_allow_rebuild = 'T', ob_allow_backup = 'T', ob_allow_restore = 'T'
Where emp_class_seq = yyy;
Commit;

Replace yyy with the emp_class_seq from above


That should let you back up, restore and rebuild from DM

 

[grantsdale]

Ok, that worked - well, not on the user I was using, but I was able to do it on a new user. The user I was in showed all blanks after running the first query.

Ran the rebuild, and it failed. I checked the log and got this:

Unloading "micros"."trans_dtl" into trans_dtl (relative to server)
***** SQL error: Statement interrupted by user
***** SQL error: Primary key for table 'trans_dtl' is not unique

 

[pmegan]

Now that's an ugly error. The trans_dtl table is the parent for all transactions run into Micros. By definition then primary key, trans_seq, has to be unique. The error you're getting sounds like there's a duplicate entry. Do you have any 3rd party software running that effects guest checks, or know of anybody who has dba access to the system there?

Let's find out how bad it is. Run this query in SQL

with CTR as (
select trans_seq, business_date, count(*) [trans_cnt]
from micros.trans_dtl
group by trans_seq, business_date
having [trans_cnt] > 1

That will list any instances of duplicate trans_seq entries. Hopefully there will only be one.

Next, run this:

with CTR as (
select trans_seq, business_date, count(*) [trans_cnt]
from micros.trans_dtl
group by trans_seq, business_date
having [trans_cnt] > 1
)

select TR.business_date, min(TR.trans_seq), max(TR.trans_seq) 
from 
    CTR
    join micros.trans_dtl [TR]
        on CTR.trans_seq = TR.trans_seq
group by
    TR.business_date
order by 
    TR.trans_seq desc

That will list the first and last trans_seq for each day with duplicate entries. We're hoping for the result from the first query to be at least near the end of the day.

I'm thinking that if you have regular posting going on, there couldn't have been much business rung in after the duplicate was inserted. At the first round of posting everything would have tanked the way it is now.


From what I can tell, the only way to get things going again is to fake Micros into thinking that the problem transaction has already posted.
Now to find out how much has happened since the last post run this:

begin
declare @max_trans_seq int

select max(trans_seq) into @max_trans_seq
from 
(
    select trans_seq, business_date, count(*) [trans_cnt]
    from micros.trans_dtl
    group by trans_seq, business_date
    having [trans_cnt] > 1
) as V


select @max_trans_seq [max_trans], hist_ttl_seq, hist_ttl_name, hist_ttl_logical_name, last_trans_seq, @max_trans_seq - last_trans_seq [diff]
from micros.hist_ttl_def
where last_trans_seq < @max_trans_seq

end

That will give you all the historical totals that been hung up by this, and how many transaction have happened since the successful post. This is to give you an idea of how much data will be lost if you skip past the problem transaction.

The last step will be to actually skip the outstanding posts. Just to make it clear, this means that you'll be missing some data and ti won't be recoverable. Make sure you back up the database before doing this.

Replace xxx with the max_trans value from the previous query

update micros.hist_ttl_def
set last_trans_seq = xxx
where last_trans_seq < xxx;
commit;
call micros.sp_postall();

This will skip any data between the last successful post and the last duplicate trans_seq, save the changes and post all outstanding totals.


Like I said, ugly stuff. I can't stress enough the need to back up your db before running that update.

 

[grantsdale]

I'm getting errors on the first 2 unfortunately.

First one says - syntax error near end of line (even after adding the closing parenthesis, and the second is 'function or reference to 'trans_seq' in the order by clause is invalid'. Third one does run, and only comes up with 34 items.

I guess I don't need to run the first two as long as the last two work, correct?

I have to run the skipping by the client but I'm sure they'd rather be up and running 100% then not.

 

[pmegan]

I forgot this is 4.1 with that ancient version of SQL. Those CTE's won't work

We can skip the 2nd one, but replace the first one with this:

select trans_seq, business_date, count(*) [trans_cnt]
from micros.trans_dtl
group by trans_seq, business_date
having [trans_cnt] > 1
Order by trans_seq describe

That will show how many bad trans_seq entries there are and put the latest one on top. Again, hopefully there's only one.

One big thing to look at in the last query is the diff column. That will show how many transactions are going to be skipped.

 

[grantsdale]

Under trans_cnt it says 2.

 

[grantsdale]

In the 'diff' column its mostly all 1's, but shift_rvc_emp_ttl is 4 and dly_rvc_fixed_prd_mi_ttl is 86080. Do I need to be concerned about those?

 

[pmegan]

Probably not. The last one is revenue center fixed period menu item sales, used in menu item time period reporting. If it's that high, my guess is that they're not posting those totals regularly, so most likely not using that report. Whether they are or not, you're probably going to have to skip them anyway.

When this settles down you may want to set up an incremental posting autosequence, or at least tweak the one they have to make sure everything stays up to date.

 

[grantsdale]

Everything seems to be up and running now. Was able to run autosequences and no problems.

Was not able to rebuild the database, however. Not too worried about that currently.

Anyway - thanks a million pmegan. Would have been completely screwed without your help. I owe you a beer.

 

[grantsdale]

Looks like I spoke too soon:

SQLExecDirect: (S1000)[Sybase][ODBC Driver][Adaptive Server Anywhere]Internal database error *** ERROR *** Assertion failed: 201501 (9.0.2.3586)
Page for requested record not a table page or record not present on page -- transaction rolled back
SQLTransact: (08S01)[Sybase][ODBC Driver][Adaptive Server Anywhere]Connection was terminated
Error in call to sp_UpdateCheckInfo statement
ClientState COMMIT failed. Error:
SQLFreeConnect: (S1010)[Microsoft][ODBC Driver Manager] Function sequence error
CDbPool : Flushing Connection Pool
SQLTransact(ROLLBACK): (08S01)[Sybase][ODBC Driver][Adaptive Server Anywhere]Communication error
SQLConnect: (08001)[Sybase][ODBC Driver][Adaptive Server Anywhere]Unable to start specified database: server exit code 1

 

[grantsdale]

Here's where I'm at right now:

Credit Card Batching errors out. The batch program tells me the following:

Error #9729 : Key violation.
Error #13059 : [Sybase][ODBC Driver][Adaptive Server Anywhere]Internal database error *** ERROR *** Assertion failed: 201501 (9.0.2.3586)
Pag
Error #13059 : for requested record not a table page or record not present on page -- transaction rolled back

Also getting an error while doing End of Night:

Autosequence 10152 step 4 of type S has begun.
SQLExecute: (08S01)[Sybase][ODBC Driver][Adaptive Server Anywhere]Connection was terminated
Error In QueryEngine::Execute_spSQLExecute: (08S01)[Sybase][ODBC Driver][Adaptive Server Anywhere]Connection was terminated
Autosequence 10152 step 4 of type S has encountered a stored procedure error...Error In QueryEngine::Execute_spSQLExecute: (08S01)[Sybase][ODBC Driver][Adaptive Server Anywhere]Connection was terminated
SQLExecDirect: (08S01)[Sybase][ODBC Driver][Adaptive Server Anywhere]Communication error
SQLTransact: (08S01)[Sybase][ODBC Driver][Adaptive Server Anywhere]Connection was terminated
Error fetching row count for Get alert_event_status count
ClientState COMMIT failed. Error:
SQLFreeConnect: (S1010)[Microsoft][ODBC Driver Manager] Function sequence error
CDbPool : Flushing Connection Pool

Then after that error I get this message constantly from the terminals:

CSeatingSectionLayout::UpdateLastActiveLayout FAILED - TMSProxyInstance.GetRvcStatus - rc[3603712]

 

[pmegan]

That assertion error is a bad thing. There's something corrupted in the database, and if it's bad enough to cause a rebuild to fail the database won't really be reliable.

Your best option at this point is probably to make a backup of the database for recovering totals and restore a backup from before the problems started. You can also try to get Micros support to help out, but it may take them a while to deal with it, and they may just tell you to restore a backup. They may also give you a hard time since 4.1 was released in 2006 so is far outdated and not even remotely PCI compliant.

Here's what Sybase has to say about it, but I wouldn't try it on a live Micros db.
[URL unfurl="true"]http://www.sybase.com/detail?id=1039322[/url]

 

[grantsdale]

I took your advice and restored from an old DB. Everything's now working correctly, last night closed correctly, everything seems to be ok.

One thing left - any way I can export the credit card transactions from the broken DB from 10/19 to 10/21 and then import them into the working DB so I can batch and process them?

Thanks again.

 

[pmegan]

I don't think there's a way to export/import CC payment info. You can usually get MerchantLink to rekey the batches for you from their transaction history, but they may give you a hard time since the system is still running Res 4.1

 

[grantsdale]

Fair enough, thanks again. Really appreciate it.