Problem in Service -> Items

AyaNova 7 (legacy version) SQL related
1

We have developed a problem in Service Workorders- Items. The application just stalls and returns an error stating: “Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.”

All other areas of AyaNova are working properly. Just the Service Workorder Item section is not performing as it was just a few weeks ago.

We have rebuilt indexes on all tables, to no avail. Database size is 2GB, but as I said, performance in every other section of AyaNova is spot on.

One other thing: The columns are all mixed up in Workorder Items, and can not be reorganized. They are jumbled for all users. This issue is on all networked computers and the server, so I’m guessing it’s a database issue.

Please provide a solution, as the software has become unuseable for us.

Versions: SQL 2008 Express and Ayanova 7.2.0 Hotfix 9

2

Hello

What is specifically done in the Items grid that results in that error message? Sorry, but is not obvious from your post.

A solution would require troubleshooting - gathering details and specifics and in what circumstances occurs
Information so far:

  • error message is recreate-able - you can recreate at any time

  • error message occurs for all AyaNova users when performing the exact same steps AND error occurs regardless of which computer on (server and/or workstations).
    These would indicate that yes, the issue is to do with something specific with your data and database.

  • other issue: the columns on this same grid where the error occurs on are unable to be moved to other locations, regardless which AyaNova user logged in as or which computer.
    What is meant in full by “unable to be moved” ? i.e. do you get an error message when you attempt to move a column in the Items grid? If so, what is the message? are you able to move but the columns revert back to their original order when exit out and log back in? are you able to move but the columns revert back to their original order when ever you move to another grid and come back? Please explain in detail what is meant by “unable to move” - what happens when you do so? Provide what you do, how and what happens please.

  • using SQL Express 2008. The latest is SQL Express 2008 R2

  • AyaNova version is 7.2.0.0 hotfix 9 The latest version of AyaNova is 7.3.2.0 and a number of issues have been fixed and possibly your issue may be.

What I would recommend :

  1. Follow the upgrade steps to update to the latest 7.3.2.0 version
  2. Then recreate the steps that would give you the error, to see if the error is now resolved or not. Do confirm on different computers and logged in as different AyaNova users (only once ALL upgrade steps are completed, of course)
  3. If resolved both issues (the issue that results in the error message, and the issue that can not change the order of columns in the Items grid), please do post back.
  4. If not resolved, do provide specifics what is resolved or not. AND importantly, do include in your post back what exact steps results in the error message please, and we can go from there!
  • Joyce
3

The error occurs whenever you select “Service Workorder Items”. You do not have to do anything else to get the error to occur. It will then display the timeout error. If you hit ignore, it will drop you back to the Service Workorder Items screen , but the both the column headers and the results will all be in a random order. You can then access the drop downs to filter columns, and also the top filter and number of row results, but changing any of these results in the Timeout error again. The columns are unable to be rearranged, and the order of them on display is not one that anyone would use.

We are also able to access every other section of the program, including Service Workorders, Units, Unit Models, and also all of the other sections under Service. Only the Items section is having a problem.

SQL is 2008 Express R2

I’m aware that 7.3.2 exists, but honestly this is almost 100% a database issue that I would like to get resolved prior to considering upgrading. I would think this would either be a malformed index, grid layout or result field, but I do not have the source code to know what exactly is happening.

I am attaching the full log below

2015-12-02 10:13:20,694 [8204] INFO AyaNova.clsMain - 291581904
2015-12-02 10:13:33,337 [8204] INFO AyaNova.Form1 - AyaNova Administrator logged in
2015-12-02 10:13:33,622 [8204] INFO AyaNova.Form1 - Settings:
DBServer: MSSQL
DBServer version: 10.50.4042.0
User settings
User: AyaNova Administrator
Language: English-Xtech
TimeZone: (UTC-05:00) Eastern Time (US & Canada)
TimeZone override: None
UserType: Administrator
SubContractor: False
Region: ALL REGIONS
Global settings
Language: English
Use Notification services: True
CJK Index: False
Use inventory: True

2015-12-02 10:14:06,394 [8204] FATAL AyaNova.Form1 - Unhandled exception
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. —> System.ComponentModel.Win32Exception: The wait operation timed out
— End of inner exception stack trace —
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at GZTW.Data.GZTWDatabase.DoExecuteReader(IDbCommand command, CommandBehavior cmdBehavior)
at GZTW.Data.GZTWDatabase.ExecuteReader(DBCommandWrapper command)
at GZTW.AyaNova.BLL.WorkorderServiceItemList.DataPortal_Fetch(Object Criteria)
— End of inner exception stack trace —

Server stack trace:
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at CSLA.Server.DataPortal.CallMethod(Object obj, String method, Object[] params)
at CSLA.Server.DataPortal.Fetch(Object Criteria, DataPortalContext context)
at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Object[]& outArgs)
at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg)

Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at CSLA.Server.DataPortal.Fetch(Object Criteria, DataPortalContext context)
at CSLA.DataPortal.Fetch(Object Criteria)
at GZTW.AyaNova.BLL.WorkorderServiceItemList.A(String A, Int32 B, List1 C) at GZTW.AyaNova.BLL.ListFactory.GetList(String key, String filter, Int32 maxrecords, List1 idlist)
at GZTW.AyaNova.BLL.ListFactory.GetList(String key, String filter, Int32 maxrecords)
at AyaNova.MainGrid.A(String A, Boolean B, String C)
at AyaNova.MainGrid.BindData(Boolean Refresh)
at AyaNova.MainGrid.ShowList(String listKey, Boolean force)
at AyaNova.Form1.A(String A, String B, Boolean C)
at AyaNova.Form1.A(String A, String B)
at AyaNova.Form1.C()
at AyaNova.Form1.A(Object A, EventArgs B)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
2015-12-02 10:14:16,847 [8204] WARN AyaNova.Form1 - User opted to continue after exception
2015-12-02 11:05:13,607 [8204] INFO AyaNova.Form1 - AyaNova Administrator closing AyaNova
2015-12-02 11:05:13,721 [8204] INFO AyaNova.Form1 - AyaNova Administrator logging out

4

Some additional information and odd behavior

Created a completely new user, and logged into AyaNova. Click on Service -> Service Workorder -> Items.

The results are now 1 result, filtered for 1 Project #. This result is the same across all users now.

We are able to change the filter, and the results display properly, however when we switch that column to ALL (100 rows max result), Ayanova returns the timeout error message.

If we set the filter in that column in such a way that all results would be returned, say like “> 0”, NonBlanks, etc, we get the intended results. But as soon as we switch the filter to ALL, it runs for a few dozen seconds and then returns the Timeout error.

5

It is certainly your choice if you choose not to update, but our first recommended troubleshooting step is to update following the update steps (i.e. have all users out, make backup, follow the update steps on server, follow the update steps on workstations, follow update steps for options)

Let us know the outcome once you have followed our recommendations.

Thank you

Joyce

6

I’ve upgraded Ayanova to 7.3 Patch 2, and the exact same issue is present with absolutely no change.

As I said, all evidence shows this to be database or data retrieval/sorting issue, as the program has been working fine for three or four years until the past couple weeks.

Please let me know what solution is needed to this problem.

7

Again, I want to stress, we do not know what the issue is caused by and do not therefore have a specific solution. Troubleshooting needs to take place to determine what the solution is.
Next troubleshooting step I would say would be to get a copy of your database here so we can recreate and look deeper.
Please send all the following:
[ul]
[li]Have all users out and back up your AyaNova database using SQL Management Studio
[/li][li]Zip up that backup. Upload to your site or google docs or etc.
[/li][li]Provide in a private email to support@ayanova.com your URL link to download your zipped backup file
[/li][li]Provide the AyaNova Administrator login and password, and two other users login and passwords
[/li][li]Attach to your email (do not embed) the ayalog.txt file from your AyaNova program folder from your computer
[/li][/ul]

  • Joyce
8

For the information of any others experiencing similar:

With the information provided and a copy of their database, we attempted to recreate the error by performing the steps using AyaNova 7.3.2.0

We were unable to recreate getting the error - performed the steps on the “server” where the database resides, also performed on a LAN computer, and on a computer connecting using a dataportal connection.

As we are unable to recreate here with the exact same data and the same version of AyaNova and SQL version, further troubleshooting is required specifically on their server and network.
What we recommended at this point would be to check the server for possible bottlenecks (i.e. could be as simple as needs more memory, and/or its CPU could be maxing out and therefore not able to pass on the data to the AyaNova program requesting), and check their network (cabling, hub, router, etc etc)

We are looking forward to hearing from them with results of this troubleshooting.

  • Joyce
9

We installed SQL Server 2008 Express R2 on a completely separate machine, and restored the database. Setup for local SQL connection only, no networking. Installed the newest version of ayanova and logged in. Ayanova worked fine for about 20 minutes, and we were able to do all of the things that were causing a problem on the original installation. However after about 20 minutes of just very light one user activity, it is now giving the exact same error as the original installation.

We paid 55$ for a 20minute teamview session in which your support just sat and watched the error, and checked a couple settings. There was no logical guess or reasoning for the actual cause of the problem, just a blanket “install fresh, restore the database”. Well, we have done that and now we are right back where we started.

I am sad that I have referred nearly a dozen service businesses to you over the last 6 or so years we have been using AyaNova. We have dealt with other software providers, large and small, and this is by far the worst support we have ever received.

We would love a solution to this, to be able to continue to use AyaNova and recommend it to our peers, however at this juncture we can not. It is your choice whether to make this right, provide a solution, and attempt to salvage this customer relationship, or to ignore/delete this and continue on as usual.

10

Hello, my name is John and I’m here to help you get working. I am a senior developer and support person here. I can certainly understand your frustration when things do not work properly and am saddened to hear that you are not happy with the support you received because it’s something we take extremely seriously.

Before we can help further I think it would be helpful to clarify a few issues first in order to avoid any further miscommunication.

Just to let you know: I have gone over all communications on this matter between your organization and ours.

First I just want to re-iterate that your issue is unique. There is no precedent for it and therefore it requires troubleshooting to get down to the root cause. Also it is an issue coming from your Microsoft SQL server, AyaNova is not generating the error it is waiting for the database server and times out and then reports that issue.

AyaNova seems to be the messenger in this, not the source of the problem.

AyaNova is a very mature product at this point and any new issues we see are generally unique and unusual so we can not simply say “do XX and it will fix this”. We have standard troubleshooting techniques developed over many years of support that are designed to resolve issues as quickly as possible.

After going over the communications between support and your organization carefully I feel I need to make this point clear: It is in your organizations best interest to follow the instructions of support, we know best how to troubleshoot our own software to get you up and running again quickly.

Since you have publicly inferred some things that I feel need to be replied to:

Support did not suggest remoting in to troubleshoot, someone at your end requested it in order for support to " see the actual issue".

Support replied that it was "your choice " if you wanted that but that their prior recommendations still stood.

You wrote: " There was no logical guess or reasoning for the actual cause of the problem, just a blanket “install fresh, restore the database”. Well, we have done that and now we are right back where we started."

In fact that’s not at all what was recommended and there were in fact very specific steps outlined to you to get to the bottom of this which I think it might be helpful to re-iterate here by pasting supports actual reply from last Thursday again:

"Thank you for showing me that your server’s running of AyaNova experiences the timeout errors.

As the issue is not recreate-able on a different server on a different network with a copy of your AyaNova database with SQL Express 2008 R2, Windows 9 64bit and AyaNova 7.3.2.0, that would imply something specific only on your end and not to do with AyaNova itself.

Troubleshooting of your server, SQL, hardware, etc is needed to be performed on your end.

  1. You have another database Jasper that you say connects to the AyaNova database for reporting purposes but does not change the AyaNova database.
    I would very much recommend that you do the following troubleshooting step of setting up another copy of your AyaNova database that is not linked to that jasper database, and perform the same steps to see if get the timeout error or not

    restore a copy of your AyaNova database to your server using a different database name i.e. AYANOVATESTING
    make a copy of your AyaNova program folder on the server and paste as a new folder
    edit the config.txt file in that copy so points to this testing AyaNova database
    run and log in as AyaNova Administrator,
    confirm via Help -> About AyaNova that you definitely ARE connecting to the AYANOVATESTING db on the server not the existing live db.
    recreate the Items steps to see if error occurs or not.
    If no error occurs, possible something with the Jasper connection causing the issue; or possibly error is resolved by restoring from backup.
    then the next troubleshooting step would be to determine whether due to Jasper or whether due to being restored
    If still exact same error, then this troubleshooting step can be crossed off (if of course the steps ensured no relation to Jasper db, and also testing restoration)

  2. I would recommend performance monitoring while you recreate the error a number of times to narrow down what is going on
    For example such as http://www.sqlshack.com/dba-guide-sql-server-performance-troubleshooting-part-1-problems-performance-metrics/
    and http://blogs.msdn.com/b/sqljourney/archive/2013/06/04/how-to-troubleshooting-sql-server-i-o-bottlenecks.aspx to give you an idea of what to look for to narrow down where coming from.
    I am repeating this troubleshooting steps as you replied regarding the CPU usage, but not about memory, not about i/o aspects.

  3. Other aspects on your server may be interfering - antivirus, security, firewalls, services, other software.
    i.e. if have a security program/antivirus/etc on your server, always make sure that the actual database files are NEVER actually accessed.
    i.e. if you make nightly full snapshots of your server, confirm is SQL compatible and what the specific recommendations are regarding SQL databases.

If you make any changes do note when in the course of troubleshooting was changed, what was changed from, what changed to, and results.

Let us know what you specifically troubleshoot, what steps and what results, and perhaps we will see something too
"

I think you will agree that is pretty specific and you’ll note it’s not a “blanket install fresh…” recommendation. In fact that small portion of the reply was a separate troubleshooting step, not an attempt to dismiss the issue entirely as I think you are inferring. I reviewed those recommendations and they are spot on and need to be followed as the logical next step. In fact everyone here who has reviewed your case has the exact same recommendations.

We would love to see you up and running; this is a unique situation which requires some troubleshooting, it seems to be a problem with something outside of AyaNova itself and more related to database server or network / hardware infrastructure since it’s not been possible to reproduce it here with your own data.

I recommend you carefully go over those troubleshooting steps and meanwhile I will personally review your actual data and see if I can find anything at all unusual that could be exposing the problem with the infrastructure.

11

Hello again, just to update:

I’ve set up a virtual machine to test your database in a debug environment for AyaNova.

On my test virtual machine running a debug copy of AyaNova from within a debugger and tracing through the code step by step (which adds a lot of overhead and slows everything down) it takes about 2 seconds in AyaNova to query, format and display the workorderitems grid from your database.

No error resulted and nothing out of the ordinary was seen in the code itself as it executed.

I tried rearranging column headings (this basically just triggers a query), no error, tried different filters you have already and no error as well.

I extracted the raw query that is sent to MS SQL server when your workorder items grid is refreshed and ran it directly in sql studio and saw no error and only slightly faster performance which is expected as there was no overhead of formatting and display as is done by AyaNova itself when displaying that form.

I’ve taken a quick look at your “raw” database itself in SQL studio and found nothing immediately concerning that jumps out at me.

So far I’m really not seeing anything here though that points to anything wrong with the database itself or the data contained in it.

You did mention at one point you had “rebuilt the indexes” so I’m not sure exactly what was done but my next step will be to pick through the database tables in question and see if there is anything amiss in the indexes and constraints. In theory if an index is missing or messed up it could result in a very slow query which might expose some other issue and result in the timeout at your site (not seeing it here but I’m running out of things to look at).

I’ll post a reply here after I’ve manually examined the schema of your database.

12

Ok, I just ran a differential comparison of a stock AyaNova MS SQL database and your database and found no differences between the two schemas so there doesn’t appear to be anything wrong with your database schema.

I would never rule anything out, I’ve done this too long to make that mistake, but that schema check eliminates all the things I can think of that might inherently be wrong with the db to cause this problem and we are definitely back in the realm of the suggested environmental things to look at for troubleshooting that was sent to you previously.

If I recall correctly this problem started several weeks ago before you first contacted us so it might be fruitful to go back over that time period (internal software and hardware change / work logs, dates of changed files, what people can remember etc) and try to determine what if anything was changed in the environment just prior to the first noted occurrence of the timeout.

Please bear in mind that while it is not practical to provide support for software outside of AyaNova itself we do provide the most common things to look at based on our experience but it is by no means comprehensive, there are many, many reasons for a SQL server to timeout and I encourage you to look into them or contact a database / network specialist if nothing we suggested helps.

Please post back here for anything you need and I will be happy to help and if you find the solution please post here for the benefit of others in future.