Powershell script error with apostrophes
-
Hello Everyone!
I am working on a powershell script to centralize the history of all my sql jobs in a single table during insertion of the message column I have a problem with the apostrophe (see the print screen) Who can help me please
<#
Mahdouani Abdallah @2020
#>
Import-Module "SQLPS" -DisableNameChecking
Add-Type -AssemblyName "Microsoft.SqlServer.Smo"
Add-Type -AssemblyName "Microsoft.SqlServer.SmoExtended"
cd c:
$CollectServer = "DESKTOP-95KKMMH"
$collectDatabase = "msdb"
$serverList ="DESKTOP-95KKMMH","DESKTOP-95KKMMH\production"
foreach($svr in $serverList)
{
$servername = $svr
$sqlRec ="select @@servername as servername
,job.name
,run_time
,STUFF(
STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(run_duration AS VARCHAR(8)), 8), 3, 0, ':'), 6, 0, ':'),
9,
0,
':'
) 'duration'
,message
FROM sysjobhistory history
INNER JOIN sysjobs job
ON job.job_id = history.job_id
LEFT OUTER JOIN [dbo].[sysjobsteps] step
ON step.job_id = job.job_id
AND step.step_id = job.start_step_id
WHERE history.step_id = 0
AND CAST(dbo.agent_datetime(run_date, run_time) AS DATE) >= GETDATE() - 200
"
$result = Invoke-Sqlcmd -ErrorAction 'Stop' -ServerInstance $servername -query $sqlRec -database $collectDatabase
foreach($line in $result){
$insertReq =[String]::format(
"insert into msdb.dbo.History_job
(ServerName
,name
,run_time
,duration
,messsage)
values
('{0}','{1}','{2}','{3}','{4}')"
,@{$true="NULL";$false=($line.ServerName).ToString()}[$line.ServerName -eq [System.DBNull]::Value]
,@{$true="NULL";$false=($line.name).ToString()}[$line.name -eq [System.DBNull]::Value]
,@{$true="NULL";$false=($line.run_time).ToString()}[$line.run_time -eq [System.DBNull]::Value]
,@{$true="NULL";$false=($line.duration).ToString()}[$line.duration -eq [System.DBNull]::Value]
,@{$true="NULL";$false=($line.message).ToString() }[$line.message -eq [System.DBNull]::Value]
)
$insertReq = $insertReq.Replace("'NULL'","NULL")
Invoke-Sqlcmd -ErrorAction 'Stop' -ServerInstance $CollectServer -Database $collectDatabase -query $insertReq
}
}
-
you can do it 2 ways
1 - use parameters instead of string replacements - this is the best option- google and you will find some code with it for powershell
2 - for each variable you are passing to the format function replace ' by ''
-
frederico_fonseca is correct, you should be using parameterized queries. Your current method is prone to SQL injection problems and you need to always account for those. Simply escaping strings (replacing ' with '') isn't really sufficient.
However, there's a much easier way to go about this using the dbatools module (https://dbatools/io). This module has functions which can extract the job history and then write it out to a table for you, including any "special character handling." It also has Invoke-DbaQuery which replaces Invoke-SqlCmd and can handle parameterized queries.
Get-DbaAgentJobHistory -SqlInstance $serverList | select-object sqlinstance, job, rundate, runduration,message | Write-DbaDataTable -SqlInstance $CollectServer -database $CollectDatabase -Table History_job -schema dbo -AutoCreateTable
You can insert some additional filtering in the pipeline where necessary, or set an "oldest date" via the -StartDate parameter for Get-DbaAgentJobHistory. It looks like you're restricting the history to anything newer than a particular point in time, but it's unclear what's happening when you subtract an integer from a datetime (>= GETDATE() - 200) - please use dateadd()!
If you can't use dbatools, please switch to the sqlserver module. It superseded sqlps 4 years ago.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply