sql-server - Powershell SQL Server 更新查询

Question

我正在尝试连接到 Microsoft SQL 数据库并将已更改字段 = 的任何记录更新为“x”。我可以查询数据库，但是当我尝试进行更新时，出现此错误:

Fill : Exception calling "Fill" with "1" argument(s): "Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

这是我正在使用的脚本:

#Create SQL Connection
$con = new-object "System.data.sqlclient.SQLconnection"

#Set Connection String
$con.ConnectionString =(“Data Source=server;Initial Catalog=IDCards;Integrated Security=SSPI”)
$con.open()

$sqlcmd = new-object "System.data.sqlclient.sqlcommand"
$sqlcmd.connection = $con
$sqlcmd.CommandTimeout = 600000
#$sqlcmd.CommandText = “select * from tblPhotoID where changed = 'X'”
$sqlcmd.CommandText = “UPDATE dbo.tblPhotoID SET Changed = '1' WHERE Changed ='X'”
$adapter = New-Object system.data.sqlclient.sqldataadapter ($sqlcmd.CommandText, $con)
$set = New-Object system.data.dataset
$adapter.Fill($set)

目前大约有 4000 条记录需要更新。该脚本运行大约 30 秒后超时。我尝试增加命令超时并得到相同的结果。

Answer 1

您的更新语句不会返回记录集，因此没有任何内容可以填充数据集。相反，您想尝试以下操作:

#Create SQL Connection
$con = new-object "System.data.sqlclient.SQLconnection"

#Set Connection String
$con.ConnectionString =(“Data Source=sb-inft-orange.ads.iu.edu;Initial Catalog=IDCards;Integrated Security=SSPI”)
$con.open()

$sqlcmd = new-object "System.data.sqlclient.sqlcommand"
$sqlcmd.connection = $con
$sqlcmd.CommandTimeout = 600000
$sqlcmd.CommandText = “UPDATE dbo.tblPhotoID SET Changed = '1' WHERE Changed ='X'”
$rowsAffected = $sqlcmd.ExecuteNonQuery()