Alert Server 1

[Zak2009]

Hi All,

Am getting an error 'Error -2147217871: Timeout expired' when i run certain processes that were running before in the sage Accpac alert server.

Please assist.

 

[DjangMan]

Sounds like your query is taking too long to run. If Alerts is connecting to the Accpac database via ODBC then you have, by default, about 60 seconds to get the job done or you get the timeout message.

Either adjust the query so it makes use of better indexes or add an index or two to speed things along.

 

[Zak2009]

How do you do this my query is like this

SELECT PTPEND.SEQUENCE, PTPEND.AUDTDATE, PTPEND.AUDTUSER, PTPEND.DOCNUMBER, PTPEND.TRANORIG, PTPEND.NXTACTIONR, PTPEND.DOCDATE, PTPEND.DOCVALUE, PTPEND.COSTCTR, PTPEND.DUEDATE, PTPEND.DOCSTATUS, PTPEND.DESCRIPTIO, {*FormatedCurrentDate} AS DATE FROM PTPEND WHERE ((PTPEND.DOCSTATUS = 22) AND (PTPEND.NXTACTIONR <> '') AND (PTPEND.DOCNUMBER <>'') AND (PTPEND.AUDTDATE > 20100601) AND ({*FormatedCurrentDate} - PTPEND.AUDTDATE > = 2)) GROUP BY PTPEND.SEQUENCE, PTPEND.AUDTDATE, PTPEND.AUDTUSER, PTPEND.DOCNUMBER, PTPEND.TRANORIG, PTPEND.NXTACTIONR, PTPEND.DOCDATE, PTPEND.DOCVALUE, PTPEND.COSTCTR, PTPEND.DUEDATE, PTPEND.DOCSTATUS, PTPEND.DESCRIPTIO

 

[DjangMan]

Read up on using the query analyzer. It can show you how the database is breaking down the query and what indexes it is using. When you see, in the query analyzer log, statements like 'Table Scan' that means that the database engine is reading every record in the table.

In your where clause you have:
((PTPEND.DOCSTATUS = 22) AND (PTPEND.NXTACTIONR <> '') AND (PTPEND.DOCNUMBER <>'') AND (PTPEND.AUDTDATE > 20100601) AND ({*FormatedCurrentDate} - PTPEND.AUDTDATE > = 2))

The AUDTDATE fields typically don't have an index so in order for the database engine to locate the records in PTPEND that have an audtdate greater than 20100601 it has to read every record in the table. So you might start by adding an index to that field.

 

[Zak2009]

Thanks it has worked