Reading data from SQL Server, sometimes returning error ---- A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

Aspire 81 Reputation points
2023-10-12T06:07:07.4433333+00:00

I have a program written in C # that reads data from the SQL Server database on a regular basis. After running for a period of time, error

A transport-level error has occurred when receiving results from the server. (provider:TCP Provider, error: 0 - The semaphore timeout period has expired.)

Invalid attempt to call FieldCount when reader is closed.

will occur, but the next time I execute the same SQL statement, It return OK.

When the problem occurred, I checked the Event Viewer and there was no Network disconnection.

What is the reason for this? Or how can I find the cause of this issue?

string szServerName = "10.10.10.10";
string DatabaseName = "DBName";
string UserName = "sa";
string securePwd = "password";
string queryString = "SELECT TOP 1000000 * FROM dbo.Event";
var sqlCnn = new SqlConnection();
SqlCredential sqlCred = new SqlCredential(UserName, securePwd);
sqlCnn.ConnectionString = "Pooling=true;Max Pool Size = 2000;Min Pool Size =0"
		+ ";server=" + szServerName
		+ ";database=" + DatabaseName
		+ ";Encrypt=no"
		+ ";connect Timeout=" + "60";
sqlCnn.Credential = sqlCred;
sqlCnn.Open()

DbCommand cmmand = new SqlCommand(queryString, sqlCnn as SqlConnection);
command.CommandTimeout = 0;

DbDataReader reader = command.ExecuteReader();
Boolean readSucceed = sqlReader.Read();
if (readSucceed)
{
	try
	{
		for (int i = 0; i < sqlReader.FieldCount; i++)
		{
			.....
		}
	}
	catch (Exception e)
	{
		string err = e.Message;
	}
}			
reader.Close();
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,899 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,391 questions
{count} votes

Accepted answer
  1. Olaf Helper 41,411 Reputation points
    2023-10-12T06:14:27.18+00:00

    The semaphore timeout period has expired.

    That's mostly caused by network issues, so check network card, cable, switch etc.

    https://bobcares.com/blog/the-semaphore-timeout-period-has-expired


0 additional answers

Sort by: Most helpful