FIX: Replication Tasks Affect Query Timeout for Other SQLExec

• Microsoft SQL Server, versions 6.0 and 6.5

SYMPTOMS

Any SQLExec task that uses DB-Library can have its query timeout affected by a replication task. Replication tasks use a DB-Library function to globally set the query timeout for the SQLExecutive process, thereby affecting all "DB-Library based" tasks including TSQL, LogReader, SYNC, and Distribution tasks.

Because of this problem, replication tasks may encounter the following message in the Task History:

   SQL Server connection timed out. Unable to successfully execute
   <command> on <server>

TSQL tasks may encounter the following error as a Failure:

   SQL Server connection timed out. (Error 10024)

Override the default query timeout for any SYNC or Distribution task with the -q0 parameter value (This is only needed for SQL Server 6.5 because the default query timeout for these tasks is 30 seconds, but is 0 for SQL Server 6.0). This can force all tasks to use an infinite query timeout (which is the default for TSQL and LogReader tasks).

For TSQL tasks, you could instead use a CmdExec task using ISQL.EXE with the -t parameter to force a query timeout.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0 and 6.5. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.

MORE INFORMATION

The LogReader, SYNC, and DB-Library subsystems in SQLExecutive all use dbsettime() to set DB-Library query timeout values. By using dbsettime() instead of dbsetopt(<dbproc>, DBSETTIME, <value>), the query timeout set by one of these tasks can affect any other SQLExecutive task that uses DB- Library, including TSQL tasks which do not have an option to control query timeout.

Since the default query timeout for the SYNC and Distribution tasks is 30 seconds in 6.5, any execution of the SYNC task, even if it has no work to perform, can affect the query timeout for the current execution of a TSQL task or other replication task such as LogReader. For SQL 6.0, this problem will only occur if the -q parameter is used for any of the replication tasks.