SQL Server Update Statement via Powershell

Question

我目前正在使用以下一段代码进行测试。

$database = 'REMOVED'
$server = 'REMOVED'
$SQLServer = "REMOVED"
$SQLDBName = "REMOVED"
$SQLuser = "REMOVED"
$SQLpassword = "REMOVED"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = 
$SQLDBName; User ID = $SQLuser; Password = $SQLpassword;"
$SqlConnection.Open()
$update = @"
update ca_contact set last_name='Rech' where userid='OR010101'
"@
$dbwrite = $SqlConnection.CreateCommand()
$dbwrite.CommandText = $update
$dbwrite.ExecuteNonQuery() 
$Sqlconnection.Close()

我得到的返回值为"1"，它告诉我该语句似乎有效。但是，通过 SQL Server Management Studio 查看时，该值永远不会写入数据库。

Answer 1

它必须是您构建连接字符串的方式。我不确定..但这应该有效。

$SQLServer = "tcp:ServerName.Domain.com"
$SQLDB = 'DB_name'
$Password = "VeryComplexPassword"
$USer = "Sophisticated_User"
$cn = new-object system.data.SqlClient.SqlConnection("Server=$SQLServer;Database=$SQLDB;User Id=$User;Password=$Password;");
$ds = new-object "System.Data.DataSet" "DataObject"
$q = 
@"
UPDATE [DB_Name].[schema].[Table_Name]
SET Updated_Column =  'New_Value'
WHERE Filter_Column = 'Filter_Criteria'
"@
$da = new-object "System.Data.SqlClient.SqlDataAdapter" ($q, $cn)
$da.Fill($ds)