Background:
The program interrupted “TCPProvider, error: 0-an existing Connection was removed by the remotehost.” There was no problem with manually executing the query, but the execution time was tens of seconds. The execution of the Trace program was found to take tens of minutes, due to the fact that the SqlDataReader was used to read the data and a series of data processing was carried out during the reading process, which made the complete process take a long time. Therefore, it is suspected that some factors such as network instability caused the connection break in the processing process. After coordination, the data was changed to DataTable for one-time data loading. After program adjustment, the frequency of failures was greatly reduced, but it cannot be completely eliminated.
Upon further inspection, the following errors were found in the SQLServer log:
Error: 7884, Severity: 20, State: 1. (Params:). The Error is printed in terse mode because there waserror during formatting. Tracing, ETW, notifications etc are skipped.
This error differs from the standard description in sys.messages and a web search did not find a suitable solution.
Screen:
I directed the table of the query to a server for testing, and finally found that it was related to an NVARCHar (Max) column. Then I remembered that some time ago, I changed the data column of the problem from NTEXT to NVarchar (Max) because of a problem with Logreader, so I specifically tested this change and finally determined that the problem was caused by this change.
(The text/ntext column may cause problems with the logreaderagent, which has been encountered many times, but there is no clear way to reproduce the failure, so we will not discuss this.)
Failure recurrence:
Use the following T-SQL to create the test table, data, and modify the Ntext column NVARCHAR (Max)
– = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
— Create test tables
– = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
USE tempdb;
GO
IF OBJECT_ID(N’dbo.tb_test’, ‘U’) IS NOT NULL
DROPTABLE dbo. Tb_test;
GO
CREATE TABLEdbo.tb_test(
Id intIDENTITY PRIMARYKEY,
Code varchar (50),
Datedatetime,
The Value ntext
);
GO
INSERT dbo.tb_test
SELECT TOP(10000)
Code =RIGHT(10000000000 +ABS(CHECKSUM(NEWID())) % (1000 * 2), 20),
Date= DATEADD(DAY, CHECKSUM(NEWID()) % 100, GETDATE()),
Value = the CONVERT (char (36), NEWID ())
FROM sys.all_columns A WITH(NOLOCK)
, sys. All_columns WITH B (NOLOCK)
;
GO
– = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
— Change the ntext field type to NVARCHAR (Max)
– = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
ALTER TABLEdbo.tb_test ALTERCOLUMN Value nvarchar(max);
GO
Write the program, query the data, and change the data before the query is completed, which is tested with the PowherShell
# connection string
$ConnectionString = “Data Source = 127.0.0.1; InitialCatalog=tempdb; Integrated Security=SSPI”
# Open connection
$SqlCnnectionQuery = New-Object System.Data.SqlClient.SqlConnection -ArgumentList $ConnectionString
$SqlCnnectionQuery.Open()
# Query data
$SqlCommandQuery = New-Object System.Data.SQLClient.SQLCommand
$SqlCommandQuery.Connection = $SqlCnnectionQuery
$SqlCommandQuery.CommandText = “SELECT * FROM( SELECT value, row_id = ROW_NUMBER()OVER( PARTITION BY Code ORDER BY date DESC) FROM dbo.tb_test WITH(NOLOCK) )DATAWHERE row_id = 1”
$SqlReader = $SqlCommandQuery.ExecuteReader()
# Modify data
$SqlCnnectionUpdate = New-Object System.Data.SqlClient.SqlConnection -ArgumentList $ConnectionString
$SqlCnnectionUpdate.Open()
$SqlCommandUpdate = New-Object System.Data.SQLClient.SQLCommand
$SqlCommandUpdate.Connection = $SqlCnnectionUpdate
$SqlCommandUpdate.CommandText = “UPDATE top(1000) dbo.tb_test SET Value =CONVERT(char(36), NEWID() ) WHERE id IN( SELECT TOP 1000 id FROM dbo.tb_testORDER BY id DESC )”
$UpdateRows = $SqlCommandUpdate.ExecuteNonQuery()
$SqlCnnectionUpdate.Close()
“Update $UpdateRows rows.”
# Read query data
An error occurred during the reading process
# (SQL 2008 R2 SP2) : TCP Provider, error: 0-anexisting Connection was removed by the remote host.
# (SQL 2008 R2 SP3) : TCP Provider, Error: 0 – The SpecifiedNetwork Name is no longer available.
At line:1 char:22
“Read query data….”
$ReadRows=0
While($SqlReader.Read()) {$ReadRows+=1}
“Read $ReadRows rows.”
# Close connection
$SqlReader.Close()
$SqlCnnectionQuery.Close()
Fault handling:
For columns with modified data types, the problem can be solved by reupdating the data (UPDATE table SET modified columns = modified columns)
This problem has been tested from SQL Server2008 to 2014, there are problems. In the test of SQL Server 2008 R2 SP3, error information in THE SQL Server log is different, as follows:
Error: 7886, Severity: 20, State: 2.
A read operation on a large object failedwhile sending data to the client. A common cause for this is if the applicationis running in READ UNCOMMITTED isolation level. This connection will beterminated.
Read More:
- DB-Lib error message 20002, severity 9
- A PHP Error was encountered Severity: Warning Message: mysqli::real_connect(): Headers and client
- PHP under linux uses pdo-dblib to connect to mssql to report an error solution. Error message: SQLSTATE[01002] Adaptive Server connection failed (severity 9)
- There is a solution to the problem: severity = corrected, type = physical layer, id = 00e5 or id = 00e8 (receiver ID) under Ubuntu
- SQL Msg 18054, Level 16, State 1
- Android listview entry button click state chaos solution
- A case diagnosis and solution of DB2 error code 1639 and SQL state 08001 is described in detail
- linux VMware Unable to change virtual machine power state: Internal error
- Springboot integrates quartz timed task trigger_ State error resolution
- Insufficient space in the root directory causes the state of the managed server to become FAILED_NOT_RESTARTABLE
- When the springcloud obtains the cloud link database information, an error is reported: errorcode 1045, state 28000
- Cisco ASA prompt modifies the display name hostname priority state context
- Error code 1045, state 28000, Java sql.SQLException :Access denied for user ‘root’@’localhost’
- (26)RuntimeError: Error(s) in loading state_dict for YoloBody:size mismatch for yolo_head3.1.weight
- pytorch raise RuntimeError(‘Error(s) in loading state_dict for {}:\n\t{}‘.format
- Springboot uses druid to log in MySQL. An error occurred: access denied: errorcode 1045, state 28000
- ERROR processing query/statement. Error Code: 0, SQL state: TStatus(statusCode:ERROR_STATUS sqlState
- Spring data JAP SQL error:17059 SQL State:99999
- Pytorch RuntimeError: Error(s) in loading state_ dict for Dat aParallel:.. function submit.py Solutions for reporting errors
- ROS cannot download ROS melody joint state publisher GUI reference