ืืขืจ ืึทืจืืืงื ืืื ืจืืื ืคึผืจืึทืงืืืฉ ืืื ืืื ืืขืืึทืงืืืืึทื ืฆื ืืืื ืืจืืืขืจืืง ืืขืฉืืืืข
ืืจืืื ืืื ืคึฟืึทืจ ื ืื ืจืืจ ืคึผืจืึธื ืคึฟืึทืจ RDS (MS SQL), ืืืขืื ืืืึธืก ืึทืืข ืืื ืืืขืจ ืืืืขืจื ืืึธืื ืืึทืืื ื, ืืื ืืขืืืื ืึท ืคึผืจืขืืขื ืืืจืื ื (POC - Proof Of Concept) ืคืื ืึธืืึทืืืืฉืึทื: ืึท ืืึทื ื ืคืื ืคึผืึธืืืขืจืฉืขืื ืกืงืจืืคึผืก. ื ืึธื ืืขืจ ืคึผืจืขืืขื ืืืจืื ื, ืืืขื ืื ืฉืืืจืขืืืืงืข, ืืึทื ืืข ืึทืคึผืืึธืืืกืืขื ืื ืืขื ืขื ืืืืกืืขืฉืืึธืจืื, ืคืึทืจืืืึทื ืืื ืืื ืืืืึธืคึผืืขื ืืืงืข ืึทืคึผืืึธืืืกืืขื ืื, ืืึธืื ืืื ืืืจ ืืขืืึธืื - ืืึธืก ืึทืืฅ ืืื ืืื, ื ืึธืจ ืฆืืืื ืืืืขืึธืืึธืืืฉืข ืกืืืืช, ืึทืจืืขืื ืึทืืข ืืื ืืืขืจืข ืืืฉืขื ืงืื ืก ืฉืงืืึทืคึฟื ืืืืฃ ืืื ืืงืก!
ืืื ืืึธืก ืืขืืืขื? ื ืขืืขื ืึทืืึท ืึท ืืืึทืจืขื, ืืึธืืคึผ ืืืึท ืคึฟืื ืืื ืืขืจ Windows ืืื ืฉืืขืงื ืขืก ืืื ืื ืืืฅ ืคืื ืคึผืึธืืืขืจืฉืขื ืืื ืืขืจ ืืื ืืงืก? ืืื ืืืก ื ืืฉื ืืืืจ?
ืืื ืืื ืฆื ืืึทื ืืื ืงืขื ืืื ืืื ืืขื ืืึธืื ืข ืงืึธืืืื ืึทืฆืืข ืคืื โโืืขืงื ืึทืืึทืืืฉืื. ืคืื ืงืืจืก, ืึทืืข ืืืื 30+ ืกืงืจืืคึผืก ืคืืจืฉืืืคื ืืจืืขืื. ืฆื ืืืื ืืืขืจืจืึทืฉื, ืืื ืืขืจืืื ืฆื ืคืึทืจืจืืืื ืึทืืฅ ืืื ืืืื ืึทืจืืขื ืืึธื. ืืื ืฉืจืฒึทื ืืื ืืืืกื ืืึธื. ืึทืืื, ืืืึธืก ืคึผืืืคืึธืื ืงืขื ืขื ืืืจ ืืจืขืคื ืืืขื ืืจืึทื ืกืคืขืจืื ื ืคึผืึธืืืขืจืฉืขืื ืกืงืจืืคึผืก ืคืื Windows ืฆื ืืื ืืงืก?
sqlcmd ืืืก Invoke-SqlCmd
ืืื ืืืจ ืืขืจืืึธื ืขื ืื ืืืืคึผื ืืืืืง ืฆืืืืฉื ืืื. ืืื ืึทืื ื ืืฆื sqlcmd ืขืก ืืืื ืึทืจืืขื ืืื ืืขืจ ืืื ืืงืก, ืืื ืึผืืขื ืืืืขื ืืงืึทื ืคืึทื ืืงืฉืึทื ืึทืืืื. ืืืจ ืคืึธืจื ืื ืึธื ืคึฟืจืขื ืฆื ืืืกืคืืจื -Q, ืื ืึทืจืืึทื ืฉืจืืึทื ืืขืงืข ืืื -i, ืืื ืืขืจ ืจืขืืืืืึทื ืืื -ืึธ. ืืืขืจ ืื ืืขืงืข ื ืขืืขื, ืคืื ืงืืจืก, ืืขื ืขื ืืขืืืื ืคืึทื-ืฉืคึผืืจืขืืืืืง. ืืืื ืืืจ ื ืืฆื -i, ืืื ืื ืืขืงืข ืฉืจืืึทืื ืืื ืื ืกืืฃ:
GO
EXITืืืื ืขืก ืืื ืงืืื EXIT ืืื ืื ืกืืฃ, sqlcmd ืืืขื ืคืึธืจืืขืฆื ืฆื ืืืึทืจืื ืคึฟืึทืจ ืึทืจืืึทื ืฉืจืืึทื, ืืื ืืืื ืคืจืืขืจ ืึทืจืืืกืืึทื ื ืืืขื ื ืื GO, ืืขืืึธืื ืืขืจ ืืขืฆืืข ืืึทืคึฟืขื ืืืขื ื ืืฉื ืึทืจืืขืื. ืืขืจ ืจืขืืืืืึทื ืืขืงืข ืึผืืื ืึทืืข ืื ืคึผืจืึธืืืงืฆืืข, ืกืึทืืขืงืฅ, ืึทืจืืืงืืขื, ืืจืืงื, ืขืืง.
Invoke-SqlCmd ืืจืืื ืืขืจ ืจืขืืืืืึทื ืืื ืึท DataSet, DataTables ืึธืืขืจ DataRows. ืืขืจืืืขืจ, ืืืื ืืืจ ืคึผืจืึธืฆืขืก ืืขืจ ืจืขืืืืืึทื ืคืื ืึท ืคึผืฉืื ืืืืกืงืืืึทืื, ืืืจ ืงืขื ืขื ื ืืฆื sqlcmd, ื ืึธื ืคึผืึทืจืกืขื ืืืึทื ืจืขืืืืืึทื, ืขืก ืืื ืึผืืขื ืืืืืขืืืขื ืฆื ืืึทืงืืืขื ืขืคึผืขืก ืงืึธืืคึผืืืฆืืจื: ืคึฟืึทืจ ืืขื ืขืก ืืื ืื ืืืึธืืง-SqlCmd. ืึธืืขืจ ืื ืืึทื ืฉืึทืคึฟื ืืืื ืืื ืืืื ืืืืืขื ืข ืืืฉืึธืืงืก:
- ืืืื ืืืจ ืึทืจืืืขืจืคืืจื ืึท ืืขืงืข ืฆื ืืืจ ืืืจื - ืื ืคึผืื ืืขืงืข, ืืขืืืื ืึทืจืืืกืืึทื ื ื ืื ืืืจืฃ, ืืขืจืฆื, ืขืก ืืจืืื ืึท ืกืื ืืึทืงืก ืืขืืช
- -OutputFile ื ืืื, ืืขืจ ืืึทืคึฟืขื ืงืขืจื ืืืจ ืื ืจืขืืืืืึทื ืืื ืึท ืืืืคืขืฅ
- ืขืก ืืขื ืขื ืฆืืืื ืกืื ืืึทืงืกืื ืคึฟืึทืจ ืกืคึผืขืฆืืคืืฆืืจื ืึท ืกืขืจืืืขืจ: -ServerInstance -Username -Password -Database ืืื ืืืจื -ConnectionString. ืึทืืื ืืขื ืื, ืืื ืืขืจ ืขืจืฉืืขืจ ืคืึทื ืขืก ืืื ื ืื ืืขืืืขื ืฆื ืกืคึผืขืฆืืคืืฆืืจื ืึท ืคึผืึธืจื ืื ืืขืจืข ืืื 1433.
- ืืขืงืกื ืจืขืืืืืึทื, ืืืคึผ PRINT, ืืืึธืก ืืื ืคืฉืื "ืืขืืืคื" sqlcmd, ืคึฟืึทืจ ืื ืืืึธืืง-SqlCmd
- ืืื ืืขืจ ืขืืงืจ:
ืืื ืืึธืก ืืื ืืขืจ ืืืืคึผื ืคึผืจืึธืืืขื. ืืืืื ืืื ืืึทืจืฅ ืืขื ืงืืืืขื , ืืื ืืขืกืึธืฃ ืืืจ ืงืขื ืขื ืคืึธืจืืืก!
ืืืขืจืืึทืืึทื ืกืึทืืกืืืืืฉืึทื
sqlcmd ืืื ืืืึทืืขืืืืืง ืกืึทืืกืืืืืฉืึทื ื ืืฆื -v, ืคึฟืึทืจ ืืืึทืฉืคึผืื ืืื ืืึธืก:
# $conn ัะพะดะตัะถะธั ะฝะฐัะฐะปะพ ะบะพะผะฐะฝะดั sqlcmd
$cmd = $conn + " -i D:appsSlaveJobsKillSpid.sql -o killspid.res
-v spid =`"" + $spid + "`" -v age =`"" + $age + "`""
Invoke-Expression $cmdืืื ืื SQL ืฉืจืืคื ืืืจ ื ืืฆื ืกืึทืืกืืืืืืฉืึทื ื:
set @spid=$(spid)
set @age=$(age)ืึทืืื ืืึธ ืขืก ืืื. ืืื * ื ืืงืก ืืืขืจืืึทืืึทื ืกืึทืืกืืืืืืฉืึทื ื ืืึธื ื ืื ืึทืจืืขื. ืคึผืึทืจืึทืืขืืขืจ -v ืืืื ืึธืจืืจื. ืื ืื ืืืึธืืง-SqlCmd ืืืื ืึธืจืืจื - ืืืขืจืืึทืืึทืื. ืืึธืืฉ ืืขืจ ืคึผืึทืจืึทืืขืืขืจ ืืืึธืก ืกืคึผืขืฆืืคืืฆืืจื ืื ืืืขืจืืึทืืึทืื ืืื ืืื ืืืื ืึธืจืืจื, ืื ืกืึทืืกืืืืืืฉืึทื ื ืืื ืึทืจืืขื - ืืืจ ืงืขื ืขื ื ืืฆื ืงืืื ืืืขืจืืึทืืึทืื ืคืื ืฉืขื. ืึธืืขืจ, ืืื ืืื ืืขืืืขื ืืืืืืืืงืืขืจ ืคืื ืื ืืืขืจืืึทืืึทืื ืืื ืืึทืฉืืึธืกื ื ืืฉื ืฆื ืึธืคืขื ืืขื ืืืืฃ ืืื ืืืึท ืึทืืข, ืืื ืึทืงืืึทื ืืจืึธื ืืื ืคึผืจืืืืืืืืื, ืืื ื ืื SQL ืกืงืจืืคึผืก ืืขื ืขื ืงืืจืฅ:
# prepend the parameters
"declare @age int, @spid int" | Add-Content "q.sql"
"set @spid=" + $spid | Add-Content "q.sql"
"set @age=" + $age | Add-Content "q.sql"
foreach ($line in Get-Content "Sqlserver/Automation/KillSpid.sql") {
$line | Add-Content "q.sql"
}
$cmd = "/opt/mssql-tools/bin/" + $conn + " -i q.sql -o res.log"ืืึธืก, ืืื ืืืจ ืคึฟืึทืจืฉืืืื, ืืื ืฉืืื ืึท ืคึผืจืึธืืข ืคึฟืื ืื ืืื ืืงืก ืืืขืจืกืืข.
ืืคึผืืึธืึทืืื ื ืืขืงืขืก
ืืื ืื Windows ืืืขืจืกืืข, ืงืืื ืึธืคึผืขืจืึทืฆืืข ืืื ืืขืืืขื ืืืืืืื ืืืจื ืึท ืงืึธื ืืจืึธืืืจื: ืืืจ ืืืืคื sqlcmd, ืืืงืืืขื ืขืืืขืืข ืืื ืคืื ืืืืืขื ืืื ืืขืจ ืจืขืืืืืึทื ืืขืงืข, ืึทืืึทืืฉื ืืขื ืืขืงืข ืฆื ืื ืงืึธื ืืจืึธืืืจื ืืขืืขืจ. ืฆืื ืืืืง, SQL ืกืขืจืืืขืจ ืืขืืจืืขื ืืืืฃ ืืขืจ ืืขืืืืงืขืจ ืกืขืจืืืขืจ ืืื Jenkins, ืขืก ืืื ืืขืืืขื ืืขืืื ืขืคึผืขืก ืืื ืืึธืก:
CREATE procedure AuditUpload
@id int, @filename varchar(256)
as
set nocount on
declare @sql varchar(max)
CREATE TABLE #multi (filer NVARCHAR(MAX))
set @sql='BULK INSERT #multi FROM '''+@filename
+''' WITH (ROWTERMINATOR = '' '',CODEPAGE = ''ACP'')'
exec (@sql)
select @sql=filer from #multi
update JenkinsAudit set multiliner=@sql where ID=@id
returnืืืื, ืืืจ ืฉืืื ืืขื ืื BCP ืืขืงืข ืืขืืึทืืจืข ืืื ืฉืืืคึผื ืขืก ืืื ืื nvarchar (ืืึทืงืก) ืคืขืื ืคืื ืื ืงืึธื ืืจืึธืืืจื ืืืฉ. ืคืืจืฉืืืื ืืื ืื ืื ืืื ืฆืข ืกืืกืืขื ืืื ืฆืขืคืืื, ืืืืื ืื ืฉืืื ื ืกืงื ืกืขืจืืืขืจ ืืื ืืื ืืืงืืืขื RDS, ืืื BULK INSERT ืืจืืขื ืืืื ื ืืฉื ืืืจื UNC ืฆืืืื ื ืคืจืืืื ืฆื ื ืขืืขื ืื ืขืงืกืงืืืกืืืืข ืฉืืืก ืืืืฃ ื ืคืืื, ืืื ืืื RDS ืืื ืืืก ืืืื ืืืื ืคืื ืื ืกืึทืืข ืึธื ืืืื. ืึทืืื ืืื ืืึทืฉืืึธืกื ืฆื ืืืืฉื ืื ืกืืกืืขื ืคึผืืึทื, ืกืืึธืจืื ื ืื ืงืึธื ืืจืึธืืืจื ืฉืืจื ืืืจื ืฉืืจื:
CREATE TABLE AuditOut (
ID int NULL,
TextLine nvarchar(max) NULL,
n int IDENTITY(1,1) PRIMARY KEY
)ืืื ืฉืจืืื ืึทืืื ืืื ืืขื ืืืฉ:
function WriteAudit([string]$Filename, [string]$ConnStr,
[string]$Tabname, [string]$Jobname)
{
# get $lastid of the last execution -- ะฟัะพัะบะธะฟะฐะฝะพ ะดะปั ััะฐััะธ
#create grid and populate it with data from file
$audit = Get-Content $Filename
$DT = new-object Data.DataTable
$COL1 = new-object Data.DataColumn;
$COL1.ColumnName = "ID";
$COL1.DataType = [System.Type]::GetType("System.Int32")
$COL2 = new-object Data.DataColumn;
$COL2.ColumnName = "TextLine";
$COL2.DataType = [System.Type]::GetType("System.String")
$DT.Columns.Add($COL1)
$DT.Columns.Add($COL2)
foreach ($line in $audit)
{
$DR = $dt.NewRow()
$DR.Item("ID") = $lastid
$DR.Item("TextLine") = $line
$DT.Rows.Add($DR)
}
# write it to table
$conn=new-object System.Data.SqlClient.SQLConnection
$conn.ConnectionString = $ConnStr
$conn.Open()
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $ConnStr
$bulkCopy.DestinationTableName = $Tabname
$bulkCopy.BatchSize = 50000
$bulkCopy.BulkCopyTimeout = 0
$bulkCopy.WriteToServer($DT)
$conn.Close()
}
ืฆื ืืืืกืงืืืึทืื ืืื ืืึทืื, ืืืจ ืืึทืจืคึฟื ืฆื ืืืืกืงืืืึทืื ืืืจื ืฉืืึทื, ืืฉืืืื ื ืืื ืกืืจ n (ืืืืขื ืืืืขื).
ืืื ืืขืจ ืืืืึทืืขืจ ืึทืจืืืงื ืืื ืืืขื ืืืื ืืื ืืขืจ ืืขืืึทื ืืืขืื ืืื ืืึธืก ืึทืืข ืื ืืขืจืึทืงืฅ ืืื Jenkins.
ืืงืืจ: www.habr.com