Error:7884,Severity:20,State:1

-- ====================================== ================

-- 创建测试表

-- ======================================================

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,

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 =CONVERT(char(36), NEWID() )

FROM sys.all_columns A WITH(NOLOCK)

,sys.all_columns B WITH(NOLOCK)

;

GO



-- ======================================================

-- 修改 ntext 字段类型为 nvarchar(max)

-- ======================================================

ALTER TABLEdbo.tb_test ALTERCOLUMN Value nvarchar(max);

GO



编写程序，查询数据，并且在查询完成前更改数据，这里用 PowherShell 做测试

# 连接字符串

$ConnectionString = "Data Source=127.0.0.1;InitialCatalog=tempdb;Integrated Security=SSPI"



# 打开连接

$SqlCnnectionQuery = New-Object System.Data.SqlClient.SqlConnection -ArgumentList $ConnectionString

$SqlCnnectionQuery.Open()



# 查询数据

$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()



# 修改数据

$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."



#读取查询数据

#读取过程中会出现错误

# (SQL 2008 R2 SP2)： TCP Provider, error: 0 - Anexisting connection was forcibly closed 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."



# 关闭连接

$SqlReader.Close()

$SqlCnnectionQuery.Close()