powershell select update
카테고리 없음 / 2023. 2. 10. 17:01
function SqlQuery
{
param (
[parameter(Mandatory=$true)] [string] $server,
[parameter(Mandatory=$true)] [string] $port,
[parameter(Mandatory=$true)] [string] $database,
[parameter(Mandatory=$true)] [string] $id,
[parameter(Mandatory=$true)] [string] $pass,
[parameter(Mandatory=$true)] [string] $query,
[parameter(Mandatory=$false)] [switch] $isRead = $false ,
[parameter(Mandatory=$false)] [int] $queryTimeout = 0 ,
[parameter(Mandatory=$false)] [int] $connTimeout = 5 ,
[parameter(Mandatory=$false)] [string] $appName = "PowerShell"
)
try
{
$substringLen = 30
if ($query.Length -lt $substringLen)
{
$substringLen = $query.Length
}
$querySubstring = $query.substring(0, $substringLen)
$conn = New-Object System.Data.SQLClient.SQLConnection
$conn.ConnectionString = "server=$($server),$($port);database=$($database);User Id=$($id);Password=$($pass);Connect Timeout=$($connTimeout);Application Name=$($appName)"
$conn.Open()
$cmd = New-Object System.Data.SQLClient.SQLCommand
$cmd.Connection = $conn
$cmd.CommandText = $query
$cmd.CommandTimeout=$queryTimeout
if($isRead)
{
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
$da.fill($ds) | Out-Null
Write-Host ($ds.Tables | Format-Table | Out-String)
}
else
{
$cmd.ExecuteNonQuery() | Out-Null
}
$conn.Close()
Write-Host "SUCCESS, SERVER : $($server), QUERY : $($querySubstring) ..."
}
catch
{
Write-Host "FAILED, SERVER : $($server), QUERY : $($querySubstring) ..."
throw "FAILED SERVER : $($server), ERROR MESSAGE : $($_)"
}
}
[string] $port = "1433"
[string] $id = "id"
[string] $pass = "pass"
[string] $query =
@"
select @@version
"@
SqlQuery -server "ip" -port "1433" -database "master" -id $id -pass $pass -query $query -isRead:$true