PRB: Dropping Connections from Sybase TCP/IP Socket Clients

ID: Q105794

The information in this article applies to:

Microsoft SQL Server version 4.2x

SYMPTOMS

When you Use Sybase Open Client applications, you can connect to Microsoft SQL Server using TCP/IP sockets. If you have experienced any unexpected client disconnection problems from these applications, you can avoid them by reconfiguring the TCP/IP transport.

After a Sybase TCP/IP client connects to a Microsoft SQL Server and idles for about two minutes, the connection may be dropped unexpectedly, which is typically indicated by the DB-Library error:

Read from SQL Server failed

when you attempt to run queries after the idle. Depending on the operating system platform, you may also receive other error messages, such as:

Connection reset by peer

on a UNIX client. Running sp_who on the SQL Server, you can confirm that the connection was dropped.

CAUSE

In Windows NT, when a TCP/IP connection idles for a certain period, TCP/IP generates "keep-alive" traffic. When TCP/IP determines that no activity has occurred on the connection within the specified time (default 120 seconds), it generates keep-alive traffic to probe the connection. After it tries the configured number of times to deliver the keep-alive traffic without success, it marks the connection as down, and the SQL Server connection is dropped.

WORKAROUND

You can add the registry entry:


   TcpKeepCnt,

or modify it if you already have one, in the following path:


   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

and set it to the number (in seconds) that you prefer. To keep the connection alive forever, you should set the value to zero. For example,


   TcpKeepCnt:REG_DWORD:0.

NOTE: You need to reboot Windows NT after making this change.

MORE INFORMATION

The disconnection problem may not occur with your particular SQL application if the application can automatically reconnect after the connection is dropped.

This problem does not occur in Microsoft SQL Server version 4.21 because the Setup program has been enhanced to automatically set the TCP/IP parameters in the registry. If you have installed SQL Server version 4.21, you do not need to manually modify the registry as described above.

Additional query words: 4.20 Windows NT connectivity

Keywords : kb3rdparty SSrvLAN
Version : 4.2
Platform : WINDOWS
Issue type :