如何使用PowerShell脚本在SQL Server中的表中删除行？

Question

$uncServer = "\\10.243.174.102\e$" 
$uncFullPath = "$uncServer\New folder\Demo.txt" 
$username = "XYZ" 
$password = "xyz" 

net use $uncServer $password /USER:$username 


$SQLServer = "AP-PUN-SRSTEP29\MSSQLSERVER12" #use Server\Instance for named SQL instances! 
$SQLDBName = "SystemDB" 
$SqlQuery = "Delete * from V_Solution WHERE Notes ='9.4.4'"; 

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True" 
#$SqlConnection.open() 


$SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
$SqlCmd.CommandText = $SqlQuery 
$SqlCmd.Connection = $SqlConnection 

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
$SqlAdapter.SelectCommand = $SqlCmd 

我在远程服务器上安装了SQL Server 2012，我想从本地机器上使用PowerShell脚本从特定数据库中的特定表中删除一行。有可能做到这一点吗？

Answer 1

一种方法是使用ADO.NET对象，你会在任何.NET应用程序。下面的PowerShell示例不需要安装SQL工具。

要执行使用Windows身份验证查询，在连接字符串中指定Integrated Security=SSPI：

$connectionString = "Data Source=YourServer;Initial Catalog=YourDatabase;Integrated Security=SSPI"; 
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString); 
$command = New-Object System.Data.SqlClient.SqlCommand("DELETE FROM dbo.YourTable WHERE YourTableID = 1", $connection); 
$connection.Open(); 
$rowsDeleted = $command.ExecuteNonQuery(); 
Write-Host "$rowsDeleted rows deleted"; 
$connection.Close(); 

要使用SQL身份验证执行查询，指定连接字符串中User ID=YourSqlLogin;Password=YourSqlLoginPassword。

$connectionString = "Data Source=YourServer;Initial Catalog=YourDatabase;User ID=YourSqlLogin;Password=YourSqlLoginPassword"; 
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString); 
$command = New-Object System.Data.SqlClient.SqlCommand("DELETE FROM dbo.YourTable WHERE YourTableID = 1", $connection); 
$connection.Open(); 
$rowsDeleted = $command.ExecuteNonQuery(); 
Write-Host "$rowsDeleted rows deleted"; 
$connection.Close(); 

在任一情况下，需要对表DELETE权限。

我不知道在您添加到您的问题脚本NET USE命令的目的，除非这是验证在工作组环境中的服务器。就个人而言，我只是将使用SQL验证，同时删除NET USE丑陋。

编辑：

在多SELECT语句在同一批次的情况下，每个将返回一个单独的记录。这就要求，如果您使用的是DataReader，将返回false，如果没有更多的记录可供调用NextRecordset：

$reader = $command.ExecuteReader(); 
do { 
    While($reader.Read()) { 
     #process row here; 
    } 
} while($reader.NextResult()); 

或者，你可以使用DataAdapter来一补“数据集”。该DataSet将包含一个单独的DataTable每个结果集：

$da = New-Object System.Data.SqlClient.SqlDataAdapter($command); 
$ds = New-Object System.Data.DataSet; 
$null = $da.Fill($ds); 
foreach($dt in $ds.Tables) { 
    $dt | Out-GridView; 
} 

你也可以调整你的SQL查询来连接成果转化利用UNION ALL如果列和数据类型的数量是相同的一个结果集。下面是一个例子片断：

$sqlQuery = @(" 
SELECT * 
FROM DB926.dbo.Version_Solution 
WHERE Notes ='9.2.7' 
UNION ALL 
SELECT * 
FROM DB_926.dbo.Version_Solution 
WHERE Notes ='9.2.7'"; 
); 
$command = New-Object System.Data.SqlClient.SqlCommand($sqlQuery, $connection); 

Answer 2

更改你的代码是这样的：

$uncServer = "\\10.243.174.102\e$" 
$uncFullPath = "$uncServer\New folder\Demo.txt" 
$username = "XYZ" 
$password = "xyz" 

net use $uncServer $password /USER:$username 


$SQLServer = "AP-PUN-SRSTEP29\MSSQLSERVER12" #use Server\Instance for named SQL instances! 
$SQLDBName = "SystemDB" 
$SqlQuery = "Delete from V_Solution WHERE Notes ='9.4.4'"; 

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True" 
$SqlConnection.open() 


$SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
$SqlCmd.CommandText = $SqlQuery 
$SqlCmd.Connection = $SqlConnection 

$SqlCmd.ExecuteNonQuery 
$SqlConnection.Close()