Sage 100 ODBC Connection

SUGGESTED

We have an application that wraps around the Sage 100 data base using the Sage 100/Providex 64-bit ODBC driver as a linked server to an instance of MS SQL Server 2014 Express 64-bit.  Various views are then defined against the S100 tables in the SQL Server instance which are then in turn queried via different parts of the application.  Sometimes over night or over the weekend when there is a period of no activity in either our application or S100 the S100 ODBC driver appears to go to sleep as a request just hangs with no I/O or CPU being registered in the Windows Resource Monitor tool.  The only way to recover the link is to restart the SQL Server instance.  Is there a log, trace file, or other debugging data that could be enabled or viewed to determine the cause of this hang?

  • 0
    SUGGESTED

    Is Sage server on a different box / VM than SQL Express? If so, in the Linked Server (LS) are you connected to the \mas90 folder via mapped drive? Mapped drives are known to timeout after inactivity so UNC path could be better. Depending on how you setup the LS this change is done either in the DSN you setup of the 64-bit ODBC Administrator or in the Provider String of the LS.

    Another possibility and I've run into this several times is your SQL Express machine has a power saver kicking in after a period of inactivity. You can see this thru Ctrl Panel / Power / Advanced ("turn off hard drives after xxx min/hrs" and similar settings) and Ctrl Panel / Network / adapter properties / Power Mgmt tab. 

    Here are 2 ways to do logging but I don't think either will help you troubleshoot and both will slow you down:

    1) Use Machine ODBC Tracing
    If LS points to a DSN in Data Source field, then in 64-bit ODBC Administrator on Tracing tab you can click the box to ODBC trace.

    2) Turn on ProvideX ODBC Tracing:

    a) If LS points to a DSN in Data Source field, then in System DSN tab where presumably your (silent connect) DSN lives, go to the Debug tab and click Enable Debug. Change the path as needed.

    b) If LS points to a connect string in Provider String field, then after the last semi-colon add LogFile=C:\Temp\PVXODBC.LOG (change the path as needed)