Detailed Template issue

AyaNova 7 (legacy version) Reporting & Development AyaNova Printing & Report Templates
1

ok this is strang . all the standard summary templates work .

but none of the detailed reports work the summary report come up

right away . but if i select any detailed report or try and create a new one i get this error. and this is happening on the server as well as the clients workstation.

i also made sure that the workstation is set to the same dir. as the server.

Unhandled Exception:
Exception has been thrown by the target of an invocation.

System.Reflection.TargetInvocationException

Stack Trace:
at System.Reflection.RuntimeMethodInfo.InternalInvoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean isBinderDefault, Assembly caller, Boolean verifyAccess)
at System.Reflection.RuntimeMethodInfo.InternalInvoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean verifyAccess)
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 CSLA.DataPortal.Fetch(Object Criteria)
at GZTW.AyaNova.BLL.WorkorderServiceDetailedReportData.GetItems(String xmlCriteria)
at AyaNova.WorkordersServiceForm.(Object , ToolClickEventArgs )
at Infragistics.Win.UltraWinToolbars.UltraToolbarsManager.OnToolClick(ToolClickEventArgs e)
at Infragistics.Win.UltraWinToolbars.UltraToolbarsManager.FireEvent(ToolbarEventIds id, EventArgs e)
at Infragistics.Win.UltraWinToolbars.ListTool.set_SelectedItemIndex(Int32 value)
at Infragistics.Win.UltraWinToolbars.ListTool.DoDefaultActionForItem(Int32 itemIndex)
at Infragistics.Win.UltraWinToolbars.ListToolMenuItem.OnClick()
at Infragistics.Win.UltraWinToolbars.PopupMenuItemUIElement.DoClickProcessing(MouseEventArgs e)
at Infragistics.Win.UltraWinToolbars.PopupMenuItemUIElement.OnMouseUp(MouseEventArgs e)
at Infragistics.Win.UIElement.OnMouseUp(MouseEventArgs e)
at Infragistics.Win.TextUIElementBase.OnMouseUp(MouseEventArgs e)
at Infragistics.Win.ControlUIElementBase.ProcessMouseUpHelper(Object sender, MouseEventArgs e)
at Infragistics.Win.ControlUIElementBase.ProcessMouseUp(Object sender, MouseEventArgs e)
at System.Windows.Forms.Control.OnMouseUp(MouseEventArgs e)
at Infragistics.Win.UltraWinToolbars.PopupControlBase.OnMouseUp(MouseEventArgs e)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
*** Exception has been thrown by the target of an invocation.
at System.Reflection.RuntimeMethodInfo.InternalInvoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean isBinderDefault, Assembly caller, Boolean verifyAccess)
at System.Reflection.RuntimeMethodInfo.InternalInvoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean verifyAccess)
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 co

2

Hi again Randy

A few suggestions for troubleshooting as from the message you are getting a time out due to the amount of time its taking to process the request:

Are you using the SQL Express 2005 or SQL Server2000 or ?

What’s the load on the server? Are you low on memory, high CPU usage?

What’s the average pages/sec? (check out processes in Task Manager in"Page Faults") (also check out http://support.theplanet.com/knowledgebase/users/kb.php?id=10109)

Take a look at this link for monitoring performace http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=28&rl=1

What other programs and services are running on this server at the same time? If you’re not using a service on your SQL Server box, stop it. The processing and memory that it’s taking up can be put to better use supporting SQL Server.

What has been applied (new program, new service, new service patch, new hardware, etc) to this server between when you did not get this issue and now?

Do you have an anti-virus program on this SQL Server that is scanning the database? This can substantially slow performance.

Do you have hyper-threading enabled? There are a number of posts and tests on the Internet that hyperthreading with SQL can slow local performance - other posts show improved performance. If you have it enabled, disable it and test again.

3

this server is a p4 3.4 g 2 gigs of ram 35gig scsi 320 drive .

this is my test box and is not running any other software. except what you (ayanova) recommended

also i should point out . that the only reports that do fail are from the main service module

i did some further testing today . and found that all detailed reports in all other modules seem to work fine . inside a work order i can create a new detailed template and that works fine

but if i try and create or use a detailed report (template) in the main service module it fails every time . now this happend ( or i noticed it ) after i imported the template from the template area of your forum .i dont beleave they are at fault but it seemed after i uploaded them is when i seemed to have an issue

4

Hi Randy

The message you are receiving is due to a timeout - the query to obtain the data for the report is timing out possibliy due to the amount of data, or due to a bottleneck on the server.

How many records are displaying in your Service Workorders grid when you run this report?

When you check Task Manager - what is the CPU usage? What is the memory usage? What are the pages/sec?

I am assuming from your reply “is not running any other software. except what you (ayanova) recommended” that you meanthere is no other software installed other than SQL Server and all relevant patches, and AyaNova v3.03, all operating patches and your backup software?

Have you checked services as suggested and confirmed services not needed are disabled as depending on the operating system and choices taken during the installation there could be a lot of other services running just by default?

Have you confirmed if hyperthreading is enabled on this server and if so, disabled it?

What operating system are you running?

What version of SQL Server are you running?

Please do provide information on the questions I have asked as they all are important to determining the issue.

  • Joyce
5

The message you are receiving is due to a timeout - the query to obtain the data for the report is timing out possibliy due to the amount of data, or due to a bottleneck on the server.

How many records are displaying in your Service Workorders grid when you run this report?

When you check Task Manager - what is the CPU usage? What is the memory usage? What are the pages/sec?

I am assuming from your reply “is not running any other software. except what you (ayanova) recommended” that you meanthere is no other software installed other than SQL Server and all relevant patches, and AyaNova v3.03, all operating patches and your backup software? yes running 3.03 and sql express

Have you checked services as suggested and confirmed services not needed are disabled as depending on the operating system and choices taken during the installation there could be a lot of other services running just by default? no the only service that is using resources is ayanova and sql. ayanova runs at around 10% and when the report fails sql in running at around 40-50% of resourse.

Have you confirmed if hyperthreading is enabled on this server and if so, disabled it? will check this today.

What operating system are you running? server 2003 sp1

What version of SQL Server are you running? Sql Express (will do latest updates )

also one thing i did notice is that the sql express is running at around 180 to 200 megs of ram it is using .seems a little high. and when the report that i am test has about 300 tickets open work orders. it fails if i only run a report for 50 of these workorders it does run fine.

what is the fix for this.

i need to be able to run atleast 300 to 500 workorder in a month . with out having to separate clients out or workorder types.

6

<TABLE cellSpacing=0 cellPadding=0 width=“100%”><TBODY><TR><TD class=smalltxt vAlign=top>The message you are receiving is due to a timeout - the query to obtain the data for the report is timing out possibliy due to the amount of data, or due to a bottleneck on the server.

How many records are displaying in your Service Workorders grid when you run this report? ok i did some more testing . if i run less then 100 it work with no problem .and is fairly quick . about 30-45 sec to run a detailed report on those 100 workorders. but if i try to run 101 it doesnt work . the system never hits over 50% resources

When you check Task Manager - what is the CPU usage? 30-50 max

What is the memory usage? total is 350megs. of which ayanova is using 45-50 and sql is using 180-225 megs

What are the pages/sec? well check this after i change the bios setting </TD></TR></TBODY></TABLE>

7

Hi Randy

Have you checked hyperthreading as of yet?

SQL uses a lot more resources than Firebird which is why you are seeing a much larger amount of memory used by the SQL.

For comparison, using SQL Express on a Athlon 2000+ with 512MB, I am able to run the report Total Workorders Billable Grouped by CLient for 912 workorders in less than a minute with no timeout occurring. I am also able to run the report Total Workorders Billable Grouped by Client for 9369 workorders with no timeout occurring.

  • Joyce
8

hyperthreading is now off on the server and i will test again.

also this is happening on the server i am not even using the network to do this testing.

is there something wrong with my install ?

should i start over on a new server and setup firebird ?

the reason i went with Sql was thought it would do better with a large amount a data.

but maybe i should try firebird instead ?

9

Hi again Randy

Is it still occurring now that hyperthreading is turned off?

As outlined in the AyaNova v3 Manual and on the AyaNova website, SQL will be faster than network Firebird especially with a lot of data.

It is possible there is an issue with your installation, or configuration of SQL, or a hardware issue if you still experience this with hyperthreading turned off.

Do check the SQL logs as they also may point to a solution.

As stated above in my reply, I am using a much slower computer than you with even less memory and with SQL Express even 1000 workorders for the detailed report displays in under a minute.

  • Joyce
10

ok turned off hyperthreading . still having same issue cannot run a report with more than 100 workorders

what should i look at . the resourse are only hitting 50% when this is set to run. . i will reinstall on another box. and see what happens

is there any way to export the data out of ayanova and reimport it to another database. ?

i think i was told no. but want to verify

11

We are not able to reproduce this issue, nor have received reports from other users with such an issue. Everything points to your SQL server or its configuration or possibly even a hardware issue.

I have made suggestions to get you pointed in the right direction, however be aware that troubleshooting your SQL Server is beyond the scope of thesupport we provide for AyaNova.I did a quick search on the Internet and I came across recommendations and suggestions on what could be done to identify what is happening on your SQL Server as there is something defintely occurring outside of AyaNova- I would recommend that you do a search of “the server is not responding” or “timeout expired” or “SQL Express performance” etc.

As for the issue of moving your SQL database to another computer, I recommend that you refer to Microsoft documentation on moving a SQL Server database.

  • Joyce
12

ok still have no fix for this . i am starting a new database in firebird. and entering all data over again. i figure this way i can still goto sql when or if i need to .

but the funny thing is the only reports that fail are the reports that are detailed .

also since i am reinstalling a new database i will also need a new lic. key to be sent i tried to update the lic. and i state could not validate lic.

so i am guessing that the lic key is expired ? i thought i read in the email i needed to update the lic. buy 2.28 and rolled the new server back to that date before installed but it didnt work.

so if possible can you email me a new key .

is there any performance tips for the firebird database.

reason i ask is with the sql database it only takes about3-5 sec to open a workorder . but with the firebird database it takes about 10-15sec. . this was the main reason we went to sql .but since we cannot figure out what is causing the timeout on the sql database on detailed only reports . i am starting over . i also did reinstall the whole database and system last week still had same issue.

the other wierd thing is i cannot even open a new detail report(create one) so i really think this is some issue with the software to some degree. ( i would blame Microsoft, but thats not nice)…

13

You are continuing to identfiy what is the cause of this issue.

What would be recommended is to continue to change aspects until you narrow down at what this occurs - always being sure to perform testing on the actual local computer, so you are not also adding network issues into it.

For example: Is this Firebird database installed to the same computer as when it was SQL?

If a different computer than when it was SQL - what is the same about the two computers?

Please note that if it is taking 10 - 15 seconds to just open a workorder, especially any workorders after the very first one opened of that program session - there would indicatestill an issue with performance on this computer.The AyaNova v3 Manual section Performance Optimization starting on page 571 identifies a list of common issues to do or not do that affect performance.

14

i installed the firebird database to another server . and it does this even (fail) on the local server as well

since i updated it to the 3.1 version . it now fails instantly . before it took a few (60sec or so)

like i said i installed firebird and am going forward . will leave the other database alone for now until i can figure out why it works on everything but a detailed report .

it is just frustrating

15

I would recommend to run a registry scan on your stations, compare what software is installed on the stations this happens with, compare what is similar hardware, similar “tweaking” to hardware settings, check for viruses, scan the hard drives- etc. There is defintiely something funky going on with your setup.

I’ve also posted on this forum to ask if anyone else is experiencing this so that you could compare computers for narrowing down the cause.

  • Joyce