v_R_sys_menuitem_fam_grp 1

[raphael75]

We have a script on our server that uses dbisql to get the quantity of menu items sold for the current day from v_R_sys_menuitem_fam_grp. This script worked perfectly for over a year, but 2 Fridays ago it stopped working for a random selection of 10-15 of our 36 stores. The affected stores, which is somewhat different each day, simply return no data. dbisql still returns data normally for previous days, just not the current day. The other stores return data as normal.

The wierd thing is that if I go to the Micros reports and run Menu Item>Consolidated SYS Menu Item Sales Detail for the current day and then run the dbisql again, it returns current-day data. It's as if it "wakes up" the view for the current day.

What could possibly cause this behavior?

Could there be a Trigger that is run when the v_R_sys_menuitem_fam_grp view is called that makes it start returning current-day data?

Why would this work fine for over a year and suddenly start behaving this way?

Any help is greatly appreciated!

 

[CathalMF]

that is just a view so its an easy way to call a complex Select sql query.

The report may have a stored procedure call which populates the data you are looking for.

I suggest you open "schedulerstatus.exe" on a working and non working server and compare whats running. You probably have some procedure set to run regularly as an autosequence.

After comparing the scedulerstatus if they are both the same then compare the 3700d.log to make sure they are all executing without failure.



Do you want some custom SIM scripts developed. Contact me via my website

http://www.microspostools.com/

 

[hosehead78]

Also, check to make sure any of what is called to run isn't still running because it got hung up on something. Have you restarted your servers lately?

 

[pmegan]

That sounds like a posting issue. Has anything been done to your servers lately? Autosequence or report changes, upgrades, anything like that? Even a procedural change where reports that were being run throughout the day are no longer being run?

Here's why I ask. Micros periodically consolidates, (posts), data from the transaction tables into totals tables, and a couple of these totals tables are used in v_R_sys_menuitem_fam_grp. If you're running your script between these posts, the data won't be up to date. When you run a menu item report it calls the posting procedure for these totals before generating, forcing the data to be current. Since the data is now posted, your script runs correctly.

Another way these totals post is through an incremental posting autosequence that runs throughout the day. This is what CathalMF is suggesting you check. The working server may have autosequences running to post totals every so often. I run mine every 10 minutes, but I've seen them set to anywhere between 2 minutes and 2 hours. Back in the day we set the common total to post every hour and let the not-so-common ones post when a report was run. This was because it took up a lot of processing power and could bog things down. That's no longer the case, but you'll still run into some "programmers" who disable incremental posting to try and speed up the server.

Depending on how your script is written, you may be able to add this before the "select" statement:

call micros.sp_R_family_group;

That will call the family group posting procedure and force the totals current.

Either way, it's a good idea to find out what changed.

 

[raphael75]

pmegan,

I spoke with our Micros guy and you were right. It turned out he had turned off something in one of the autosequences a few weeks ago that apparently was running one of the stored procedures. He re-enabled it and the problem went away.

I also added the

call micros.sp_R_family_group;

to the sql at the beginning so in case something gets disabled again in the future it should still called the stored procedure.

Thank you so much!

 

[raphael75]

Is it possible to call micros.sp_R_sys_menuitem_fam_grp and pass in a date range? It returns like 2 years of data when I run it and takes forever to run. Thanks!