阿仁's Blog

在 PowerShell 中使用 Error Handling

| Comments

正常我們會使用一些腳本程式,來協助執行一些日常自動化的作業。
例如我們常用的 Linux Shell Script 搭配 Crontab。
Windows Server PowerShell 搭配 Task Scheduler 等等。

但是!你的自動化 PowerShell Script 有做 Error Handling 嗎?

其實 PowerShell 中有支援 try catch 的語法。
來,直接看 Demo Code。

$ErrorActionPreference = "Stop"
$bak_filenameA = "D:\test\test.txt"
$bak_filenameB = "D:\test\testB.txt"

try {

    #delete file if not exists

    Remove-Item $bak_filenameA -ErrorAction Stop
    Remove-Item $bak_filenameB 
    
} catch {
    $formatstring = "{0} : {1}`n{2}`n" +
                    "    + CategoryInfo        : {3}`n" +
                    "    + FullyQualifiedErrorId : {4}`n"
    $fields = $_.InvocationInfo.MyCommand.Name,
              $_.Exception.Message,
              $_.InvocationInfo.PositionMessage,
              $_.CategoryInfo.ToString(),
              $_.FullyQualifiedErrorId

    $ErrorMessage = $formatstring -f $fields
    Write-Host -Foreground Red ($ErrorMessage)
}

幾個須注意的地方。
第一行所給定的變數 $ErrorActionPreference = "Stop"
意思是,針對後面的程式碼,預設的 ErrorAction 參數皆為 Stop。
(原先系統預設的參數是 $ErrorActionPreference = Continue)

這是甚麼意思?
意思是,如果沒有下這一行指令的話,
在 Try Catch Block 中,你都必須要像刪除檔案A的指令一樣,
在後面個別去設定對應的 -ErrorAction 參數。
Remove-Item $bak_filenameA -ErrorAction Stop

至於後面 Catch 到 Exception 之後,
再從 PS 的內建變數元件 $_ 裡面去抓到對應的參數即可。

Reference

https://blog.kloud.com.au/2016/07/24/effective-error-hanalding-in-powershell-scripting/
https://stackoverflow.com/questions/38419325/catching-full-exception-message
https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_preference_variables?view=powershell-6
https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_try_catch_finally?view=powershell-6

使用 PowerShell 檢查 SQL Agent Job 狀態

| Comments

其實是接續著前一篇 使用 PowerShell 執行 SQL Server Job.

當我們現在可以用 PowerShell 來呼叫指定的 SQL Job 執行時,
接下來的問題就會是這個 :

那? 我如何利用 PowerShell 來得知指定的 SQL Job 做完了沒?

原理其實也蠻容易的。(江湖一點絕,說出來不值錢。)
我們同樣是利用 SMO 來做到。

來,直接看 Code。

#Setup SQL param

$SQLServerName = 'TestServer'
$JobName = 'TestJob'
$JobRunTimeOutSec = 60

#Load SMO

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$srv = New-Object Microsoft.SqlServer.Management.SMO.Server("$SQLServerName")
$job = $srv.jobserver.jobs["$JobName"] 

if ($job) { 
    Write-Host "Starting SQL Agent Job $($JobName) on Server $($SQLServerName) $(Get-Date -format 'yyyy-MM-dd HH:mm:ss')"

    $job.Start()      #Run SQL Job

    Start-Sleep -s 1  #Wait for Job running


    $JobRunSec = 0
    while ($job.CurrentRunStatus -ne "Idle") {
        Write-Host "$(Get-Date -format 'yyyy-MM-dd HH:mm:ss') job.CurrentRunStatus = " + $job.CurrentRunStatus
        Start-Sleep -s 1
        $JobRunSec = $JobRunSec + 1
        $job.Refresh() #Refresh the Job status


        #If Job Run over 60 Sec. force break

        if ($JobRunSec -ge $JobRunTimeOutSec) {
            throw "Job : $JobName Run over $JobRunTimeOutSec Sec. force break"
            break
        }
    }
}

Write-Host "Ending SQL Agent Job $($JobName) on Server $($SQLServerName) $(Get-Date -format 'yyyy-MM-dd HH:mm:ss')"

其實關鍵在 $job.CurrentRunStatus 這裡。
這裡所取得的就是目前對應 SQL Job 的 Status。
做法簡單講就是利用這個資訊來判斷。

寫一個 While Loop 來判斷指定的 SQL Job 是不是已經執行完畢,進入 Idle 狀態。
如果沒有,就等 1 秒鐘 Refresh() 再判斷一次。
直到 SQL Job 執行完畢,進入 Idle Status。

這邊我另外再宣告了一個 $JobRunTimeOutSec 變數,
用來記錄所指定的 Job 執行多久要做 TimeOut。
(因為你知道,迴圈很多時候都是 Bug 的來源阿。)

以上面的 Code 來看的話,
當執行時間超過 60 Sec. 時,
我會強制跳出迴圈,並 throw Error Message 出來。

Reference

https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.agent.job?view=sqlserver-2016
https://docs.microsoft.com/zh-tw/dotnet/api/microsoft.sqlserver.management.smo.database.refresh?view=sqlserver-2016

追蹤 Nav 程式

| Comments

最近在 Debug Nav 程式時,遇到一個問題。
我在 Codeunit 中發現了某一個 Gobal 函式是個關鍵,
但是我不知道這個函式究竟被那些其他物件或是其他程式所呼叫使用到?

Who is Calling the Function ?
到底有哪些地方用到這個函式?

簡單講,我必需要能夠 tracing Nav 的程式。
But!但是!人生就是這個 But。
光要在 Nav 的 IDE 中做搜尋都不容易了,還想做 Tracing ? 作夢...

冷靜之後思考了一會兒,
這麼大的一個系統,不可能大家都不會遇到類似的問題吧?
於是 Goolge 了一下,發現了這個可行方案。

其實步驟蠻簡單的。
原理一句話就可以講完。(自己Google... 開玩笑的 哈哈~)
1.將所有的物件程式匯出成 .txt 檔
2.運用 Split-NAVApplicationObject 這個函式,將檔案 By 物件類別各別切割成小檔案
3.使用檔案搜尋工具(NotePad++等...),來做搜尋追蹤即可。

整體程式片段如下:

Export-NAVApplicationObject D:\Aaron_test\MyAppSrc.txt -DatabaseName MyApp -ExportTxtSkipUnlicensed | Split-NAVApplicationObject -Destination D:\Aaron_test\App_Bak

上述程式範例指的是,

1.將所有的物件程式匯出成 .txt 檔

將 MyAPP DB 中的物件匯出至檔案 MyAppSrc.txt,並自動略過那些沒有授權的物件。

2.運用 Split-NAVApplicationObject 這個函式,將檔案 By 物件類別各別切割成小檔案

|(Pipe) 後面那段,則是重點,
意思是將匯出的 MyAppSrc.txt 檔,By 物件類別各別切割成小檔案,並放置在 D:\Aaron_test\App_Bak 資料夾中。

3.使用檔案搜尋工具(NotePad++等...),來做搜尋追蹤即可。

Reference

https://docs.microsoft.com/en-us/powershell/module/microsoft.dynamics.nav.ide/export-navapplicationobject?view=businesscentral-ps

程式語言註解表

| Comments

Language Comment Block Comment
Shell Script #......
PowerShell #...... <#......#>
SQL --...... /*......*/
C/AL //...... {......}
PHP
Java
JavaScript
C++
Go
//...... /*......*/
Ruby #...... =begin......=end
HTML <!--......-->
CSS /*......*/
Python #...... """......"""
'''......'''

匯出 SQL Server Job Log (2/2)

| Comments

在前一篇 匯出 SQL Server Job Log (1/2) 提到透過用 job history size 搭配匯出成 .csv 檔案的功能,來達到 SQL Server Job Log 維運的功能。

後來想想,這種用 Rows Size 的方式來控制 Job Log 似乎並非最佳解。
因為要是 Job 有新增或調整,可能 Rows Size 就必須跟著變動。
好像還是必須要透過自寫 T-SQL 來達成客製化需求。

所以,第一步,先大膽的將你的 SQL Server Job Log Rows Size 限制移除吧!(結界解放~)
再來直接看 Code

-- BCP - Export query
DECLARE @sql_str VARCHAR(1000) = 
    'SELECT j.name JobName, h.step_name StepName, '+
    'CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate, '+
    'STUFF(STUFF(RIGHT(''000000'' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,'':''),3,0,'':'') RunTime, '+
    'h.run_duration StepDuration, '+
    'case h.run_status when 0 then ''failed'' '+
    'when 1 then ''Succeded'' '+
    'when 2 then ''Retry'' '+ 
    'when 3 then ''Cancelled'' '+
    'when 4 then ''In Progress'' '+
    'end as ExecutionStatus, '+
    'h.message MessageGenerated '+
    'FROM msdb.dbo.sysjobhistory h inner join msdb.dbo.sysjobs j '+
    'ON j.job_id = h.job_id '+
    'ORDER BY j.name,  h.run_date,  h.run_time';

DECLARE @bcp_cmd4 VARCHAR(1000);
DECLARE @output INT;
DECLARE @file_name VARCHAR(24) = 'SQL_Job_log_'+CONVERT(varchar(100), GETDATE(), 112)+'.csv';
DECLARE @del_file VARCHAR(24) = 'SQL_Job_log_'+CONVERT(varchar(100), DATEADD(DAY, -14, GETDATE()), 112)+'.csv';


--export today SQL Job log
SET @bcp_cmd4 =  
    ' cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn\ & ' + 
    ' BCP.EXE "'+@sql_str+'" queryout ' +
    ' "D:\SQL Job Log\'+@file_name+'" -T -c -q -t, -r\n';
EXEC master..XP_CMDSHELL @bcp_cmd4;


--delete file 2 weeks ago
SET @bcp_cmd4 = 'DIR "D:\SQL Job Log\'+@del_file+'" /B';
EXEC @output = master..XP_CMDSHELL @bcp_cmd4, NO_OUTPUT
IF @output = 0
    SET @bcp_cmd4 = 'del "D:\SQL Job Log\'+@del_file+'"';
    EXEC master..XP_CMDSHELL @bcp_cmd4

-- delete job history older than yesterday
DECLARE @Target_date datetime = CONVERT(date,DATEADD(DAY,0,GETDATE()))
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date=@Target_date

程式直接將 Log 檔儲存在 D:\SQL Job Log\ *.csv,
並 Rota 2 Weeks。

主要的重點在最後一段,
這邊呼叫了 SQL Server 內建的 stored procedure: msdb.dbo.sp_purge_jobhistory
這個 Procedure 主要就是用來刪 Job Log 的。
他有一個參數 @oldest_date 這個就是用來控制要刪除多久之後的 Log.

PS: 注意 @oldest_date 這個是 datetime 格式。

Reference

https://docs.microsoft.com/zh-tw/sql/relational-databases/system-stored-procedures/sp-purge-jobhistory-transact-sql?view=sql-server-2017

匯出 SQL Server Job Log (1/2)

| Comments

正常在 SQL Server 的 Job 中,可以點選 View History 的功能,來查看 Job 執行的 Log。
系統預設的 Log 儲存規則可以到 SQL Server Agent 中,按右鍵查看。
系統預設值是,job history size 最大為 1000 Rows,每個 Job 最多為 100 Rows

SELECT j.name JobName, h.step_name StepName, 
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT(''000000'' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then 'failed'
when 1 then 'Succeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as ExecutionStatus,
h.message MessageGenerated
FROM msdb.dbo.sysjobhistory h inner join msdb.dbo.sysjobs j
ON j.job_id = h.job_id
ORDER BY j.name,  h.run_date,  h.run_time

用上面這段 SQL 語法就可以 Query 出目前 SQL Server 中所有的 Job Log.

再來我們需要把這些資料匯出成 .csv 檔案存儲起來。
這裡需要用到 BCP 來達成。

PS: 如果你的 BCP 指令還沒開啟,可以用下面這個指令開啟

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

開啟之後就可以匯出成檔案拉!
來看看完整的 Code

-- BCP - Export query
DECLARE @sql_str VARCHAR(1000) = 
    'SELECT j.name JobName, h.step_name StepName, '+
    'CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate, '+
    'STUFF(STUFF(RIGHT(''000000'' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,'':''),3,0,'':'') RunTime, '+
    'h.run_duration StepDuration, '+
    'case h.run_status when 0 then ''failed'' '+
    'when 1 then ''Succeded'' '+
    'when 2 then ''Retry'' '+ 
    'when 3 then ''Cancelled'' '+
    'when 4 then ''In Progress'' '+
    'end as ExecutionStatus, '+
    'h.message MessageGenerated '+
    'FROM msdb.dbo.sysjobhistory h inner join msdb.dbo.sysjobs j '+
    'ON j.job_id = h.job_id '+
    'ORDER BY j.name,  h.run_date,  h.run_time';


DECLARE @bcp_cmd4 VARCHAR(1000);
DECLARE @file_name VARCHAR(24) = 'SQL_Job_log_'+CONVERT(varchar(100), GETDATE(), 112)+'.csv';


--export today SQL Job log
SET @bcp_cmd4 =  
    ' cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn\ & ' + 
    ' BCP.EXE "'+@sql_str+'" queryout ' +
    ' "D:\SQL Job Log\'+@file_name+'" -T -c -q -t, -r\n';
EXEC master..XP_CMDSHELL @bcp_cmd4;

主要就是利用 XP_CMDSHELL 搭配 BCP 來做到 Query 後匯出。

PS: 需注意的是,盡量不要把檔案存到 user 桌面上,容易有權限上的問題,導致匯出不成功。重點是,還沒有錯誤訊息...

Reference

https://docs.microsoft.com/en-us/sql/ssms/agent/set-up-the-job-history-log?view=sql-server-2017
https://www.mssqltips.com/sqlservertip/2850/querying-sql-server-agent-job-history-data/
https://docs.microsoft.com/zh-tw/sql/tools/bcp-utility?view=sql-server-2017#C
https://stackoverflow.com/questions/18619061/unable-to-open-bcp-host-data-file

檢查 SQL Server Restoring 的進度

| Comments

SQL Server Restore 時,有時候檔案很大不知道到底做到哪裡了,
這時可以使用下面這個指令,查詢 SQL Server Restore 的進度。

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time 
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

Reference

https://www.mssqltips.com/sqlservertip/2343/how-to-monitor-backup-and-restore-progress-in-sql-server/

使用 PowerShell 上傳檔案至 AWS S3

| Comments

要用 PowerShell 上傳檔案至 AWS S3,
首先要先安裝 AWS Tools for PowerShell SDK。

安裝完之後,在 PowerShell 中載入 AWS PowerShell 模組。

//載入 AWS PowerShell 模組
Import-Module "C:\Program Files (x86)\AWS Tools\PowerShell\AWSPowerShell\AWSPowerShell.psd1"

PS: 首次載入時,會需要輸入AccessKey & SecretKey。

接者直接看 Code 吧!

//載入 AWS PowerShell 模組
Import-Module "C:\Program Files (x86)\AWS Tools\PowerShell\AWSPowerShell\AWSPowerShell.psd1"

//測試是否可正常存取 MyBucket,回傳 true|false。
Test-S3Bucket -BucketName 'MyBucket'

//上傳 D:\local_test.zip 檔案至 S3 'MyBucket',並將檔案放置在 folder 底下,檔案名稱為 test.zip
Write-S3Object -BucketName 'MyBucket' -Key 'folder/test.zip' -File 'D:\local_test.zip' -EndpointUrl 'HTTPS://s3-ap-southeast-1.amazonaws.com'

//將 S3 上 MyBucket 裡的,folder 底下的 test.zip 檔案刪除。
Remove-S3Object -BucketName 'MyBucket' -Key 'folder/test.zip' -EndpointUrl 'HTTPS://s3-ap-southeast-1.amazonaws.com'

幾個須注意的地方
1. -EndpointUrl 這個如果不知道的話,可以到 AWS DOC 上查。
2. 如果想要移除 S3 上的 folder 的話。將 folder 中的檔案都刪除後,folder 就會自動消失了。
3. 在 S3 中沒有提供 Rename Object 的功能。要 Rename 的話,要先複製之後,在刪除即可。

Reference

https://docs.aws.amazon.com/powershell/latest/userguide/pstools-welcome.html
https://aws.amazon.com/tw/powershell/
https://docs.aws.amazon.com/powershell/latest/reference/Index.html

利用 PowerShell 匯出 Dynamics NAV Company

| Comments

Dynamics NAV 其實從系統上可以點選匯出 Company 資料。
但是為了要做自動排程備份 Company 資料的話,
每次都要手動去做,其實很不 Make Sence。

好家在 Dynamics NAV 提供了 PowerShell 匯出的指令 Export-NAVData

#Setup Backup filename

$Now = Get-Date
$bak_filename = "D:\DB_backup\MyCompany_" + $Now.ToString('yyyMMdd') + ".navdata"

#import NAVAdminTool model

import-module 'C:\Program Files\Microsoft Dynamics NAV\90\Service\NavAdminTool.ps1' | out-null

#Export Nav MyCompany data

Export-NAVData -ServerInstance "MyNAV" -CompanyName "MyCompany" -IncludeGlobalData -FilePath $bak_filename -Verbose

#Import to MyNAV_TEST ServerInstance

Import-NAVData -ServerInstance "MyNAV_TEST" -CompanyName "MyCompany" -IncludeGlobalData -FilePath $bak_filename -Verbose -Force

重點在於,要記得先 Import NAVAdminTool.ps1 model,
否則他找不到對應的 NAV 指令。

然後這邊我多下了一個 -IncludeGlobalData 指令,
這是表示匯出的資料要包含 NAV 的 GlobalData。
如果備份檔有包含 GlobalData ,相對應的匯入時,也要記得下 -IncludeGlobalData 指令喔。

Reference

https://docs.microsoft.com/en-us/powershell/module/microsoft.dynamics.nav.management/export-navdata?view=dynamicsnav-ps-2018

使用 PowerShell 執行 SQL Server Job.

| Comments

如何能夠用 PowerShell 的指令來執行 SQL Server 中,已設定好的 Job 呢?
來~ 直接看 Code。

#Start SQL Job My_Job

$ServerName = 'My_SQL_Server'
$JobName = 'My_Job'
$StepName = 'Step_one'

Write-Host "Starting SQL Agent Job $($JobName) on Server $($ServerName)"

$date = Get-Date
Write-Host "It is now: $($date)"

#Load the SMO Model

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$srv = New-Object Microsoft.SqlServer.Management.SMO.Server("$ServerName")
$job = $srv.jobserver.jobs["$JobName"] 

if ($job) { 
    if($StepName -ne '') {
        $job.Start($StepName)
    } else {
        $job.Start()
    }
    Write-Verbose "Job $($JobName) on Server $($ServerName) started"
}

#wait for SQL Job

Start-Sleep -s 5

主要的重點在於,利用 SQL Server 提供的 SMO 模組來操作 SQL Server.
不過這段程式碼要注意的點是,他只是發指令出去叫 SQL Server 執行指定 Job,
並不會等待 Job 執行完才接續執行後面的程式碼。
所以後面放了一個 Start-Sleep 給 Job 時間執行。

Reference

https://community.spiceworks.com/topic/1752564-start-a-sql-agent-job-with-powershell

https://docs.microsoft.com/zh-tw/sql/relational-databases/server-management-objects-smo/sql-server-management-objects-smo-programming-guide?view=sql-server-2017