使用 PowerShell 腳本來執行 T-SQL

 

使用 PowerShell 腳本來執行 T-SQL 

觸發查詢並將結果使用 bcp (Bulk Copy Program) 命令導出到 .log 文件

# 定義 SQL Server 的連線資訊
$serverName = "YOUR_SERVER_NAME"
$databaseName = "YOUR_DATABASE_NAME"
$username = "sa"
$password = "YOUR_PASSWORD"
$query = "SELECT * FROM YOUR_TABLE WHERE CONDITION = 'VALUE'"  # 替換為您的 T-SQL 查詢

# 設定 bcp 命令所需的查詢和導出文件路徑
$queryFile = "C:\path\to\yourQuery.sql"
$outputFile = "C:\path\to\output.log"

# 將 SQL 查詢寫入一個文件中
Set-Content -Path $queryFile -Value $query

# 建立 bcp 命令
$bcpCommand = "bcp `"$query`" queryout $outputFile -c -t, -S $serverName -d $databaseName -U $username -P $password"

# 執行 bcp 命令
Invoke-Expression $bcpCommand

# 檢查輸出文件
if (Test-Path $outputFile) {
    Write-Host "文件已成功導出到: $outputFile"
} else {
    Write-Host "導出失敗"
}

• 定義連線參數，包括伺服器名稱、資料庫名稱、用戶名和密碼。
• 設定要執行的查詢和結果輸出文件的路徑。
• 使用 bcp 命令從 SQL Server 導出數據到指定的 .log 文件。

$query 如果很複雜是很多table join 加上cte組合 中間穿插了雙引號、單眼號的話

當您的 T-SQL 查詢相當複雜，包括多個表的連接（joins）、公用表達式（CTE）以及包含特殊字符（如雙引號或單引號）時，處理這種查詢最好的做法是將 SQL 查詢存儲在一個外部文件中，然後讓 bcp 工具直接從該文件中讀取查詢。這樣可以避免在命令行中遇到的許多引號和特殊字符的轉義問題。

創建 SQL 查詢文件： 將您的 SQL 查詢寫入一個 .sql 文件中。假設文件名為 complexQuery.sql

C:\path\to\complexQuery.sql

-- complexQuery.sql
WITH CteExample AS (
    SELECT ColumnA, ColumnB
    FROM Table1
    WHERE ColumnC > 100
)
SELECT t2.ColumnD, cte.ColumnA
FROM Table2 t2
JOIN CteExample cte ON t2.ColumnB = cte.ColumnB
WHERE t2.ColumnE = "SomeValue"

修改 PowerShell 腳本以使用外部 SQL 文件： 修改您的 PowerShell 腳本，讓 bcp 直接從這個 SQL 文件讀取查詢。

# 定義 SQL Server 的連線資訊
$serverName = "YOUR_SERVER_NAME"
$databaseName = "YOUR_DATABASE_NAME"
$username = "sa"
$password = "YOUR_PASSWORD"
$queryFile = "C:\path\to\complexQuery.sql"
$outputFile = "C:\path\to\output.log"

# 建立 bcp 命令
$bcpCommand = "bcp `"" + $(Get-Content -Path $queryFile -Raw) + "`" queryout $outputFile -c -t, -S $serverName -d $databaseName -U $username -P $password"

# 執行 bcp 命令
Invoke-Expression $bcpCommand

# 檢查輸出文件
if (Test-Path $outputFile) {
    Write-Host "文件已成功導出到: $outputFile"
} else {
    Write-Host "導出失敗"
}

Get-Content -Path $queryFile -Raw 用來讀取整個 SQL 文件的內容，作為查詢的一部分傳遞給 bcp。這種方法使得管理複雜的 SQL 查詢變得更加容易，並且減少了因直接在命令行中處理複雜查詢時可能出現的錯誤。

根據日期將輸出文件命名並且每次運行腳本時清除超過兩個月舊的 .log 文件

使用日期動態生成文件名：將輸出文件命名為當天的日期。

定期清除超過兩個月的文件：在腳本開始時添加一段代碼，刪除兩個月之前的 .log 文件。

# 定義基本參數
$serverName = "YOUR_SERVER_NAME"
$databaseName = "YOUR_DATABASE_NAME"
$username = "sa"
$password = "YOUR_PASSWORD"
$queryFile = "C:\path\to\complexQuery.sql"
$logDirectory = "C:\path\to\logs"

# 今天的日期，用於命名日誌文件
$today = Get-Date -Format "yyyy-MM-dd"
$outputFile = "$logDirectory\$today.log"

# 清除兩個月前的日誌文件
$cutOffDate = (Get-Date).AddMonths(-2)
Get-ChildItem -Path $logDirectory -Filter "*.log" | Where-Object { $_.LastWriteTime -lt $cutOffDate } | Remove-Item -Force

# 建立 bcp 命令
$bcpCommand = "bcp `"" + $(Get-Content -Path $queryFile -Raw) + "`" queryout $outputFile -c -t, -S $serverName -d $databaseName -U $username -P $password"

# 執行 bcp 命令
Invoke-Expression $bcpCommand

# 檢查輸出文件
if (Test-Path $outputFile) {
    Write-Host "文件已成功導出到: $outputFile"
} else {
    Write-Host "導出失敗"
}

• $logDirectory 指定了存儲日誌文件的目錄。
• $today 生成了一個格式為 yyyy-MM-dd 的日期字符串，用於構建輸出文件的名稱。
• Get-ChildItem 搜索該目錄下所有 .log 文件，Where-Object 判斷文件的最後寫入時間是否早於兩個月前的日期，並通過 Remove-Item 刪除這些文件。

在 PowerShell 腳本中直接嵌入明文的使用者名稱和密碼是一個安全風險。

1.使用加密的 PowerShell 認證檔案

可以將認證保存在一個加密的檔案中，並在腳本中加載這個檔案。

如何創建和使用加密的認證檔案

# 提示使用者輸入密碼
$credential = Get-Credential -Message "Enter your SQL Server SA credentials"
$credential | Export-Clixml -Path "C:\path\to\your\credentials.xml"

將提示使用者輸入他們的使用者名稱和密碼，並將其保存在一個加密的 XML 檔案中。只有創建檔案的同一個使用者帳戶才能解密該檔案。

在腳本中使用加密的認證檔案

# 從加密檔案加載認證
$credential = Import-Clixml -Path "C:\path\to\your\credentials.xml"
$username = $credential.UserName
$password = $credential.GetNetworkCredential().Password

# 其餘的腳本不變，使用 $username 和 $password 變量

2.使用環境變數

在環境變數中存儲使用者名稱和密碼。這仍然需要一定的安全措施，因為環境變數可能會被同一系統上的其他process讀取。

在 Windows 中，您可以在系統的環境設置中添加環境變數，或使用命令行：

setx SQLUSER "sa" /M
setx SQLPASS "yourpassword" /M

/M 參數表示將變數設置為系統範圍內的環境變數。

在 PowerShell 中讀取環境變數

$username = [System.Environment]::GetEnvironmentVariable("SQLUSER", "Machine")
$password = [System.Environment]::GetEnvironmentVariable("SQLPASS", "Machine")