ביצוע שאילתה במסד הנתונים של Microsoft SQL Server (MSSQL) באמצעות PowerShell

במאמר זה נדון בכל הדרכים היעילות להתחבר ל- Microsoft SQL Server ולהפעיל שאילתות SQL מ- PowerShell. ישנן דרכים רבות כיצד ניתן לעבוד עם SQL Server באמצעות PowerShell, וקל להתבלבל כאשר אתה לומד הרבה מאמרים באינטרנט, שכן כולם מתארים שיטות שונות, ואפילו מנהל מנוסה עשוי להיות שאלות.

שאילתות T-SQL ב- PowerShell באמצעות System.Data.OleDb

מאחר ש- PowerShell יכול לגשת למחלקות .NET Framework, באפשרותך להשתמש במחלקות מ- System.Data.OleDb כדי לבצע שאילתות T-SQL.

להלן קובץ script לדוגמה של PowerShell לחיבור SQL Server באמצעות המחלקה System.Data.OleDb. בוא נפעיל שאילתת SELECT מול טבלה במסד הנתונים MS SQL:

$dataSource = “lon-sql01\testdb”
$database = “master”
$sql = “SELECT * FROM sysdatabases”
$auth = “Integrated Security=SSPI;”
$connectionString = “Provider=sqloledb; ” +
“Data Source=$dataSource; “ +
“Initial Catalog=$database; “ +
“$auth; “
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sql,$connection
$connection.Open()
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
$connection.Close()
$rows=($dataset.Tables | Select-Object -Expand Rows)
echo $rows

להלן דוגמה של קובץ script של PowerShell לביצוע שאילתת INSERT/UPDATE/DELETE מול מסד נתונים של MSSQL:

$dataSource = “lon-sql01\testdb”
$database = “test”
$sql = "insert into test_table (test_col) Values ('Test')"
$auth = “Integrated Security=SSPI;”
$connectionString = “Provider=sqloledb; ” +
“Data Source=$dataSource; ” +
“Initial Catalog=$database; ” +
“$auth; “
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sql,$connection
$connection.Open()
$command = New-Object data.OleDb.OleDbCommand $sql
$command.connection = $connection
$rowsAffected = $command.ExecuteNonQuery()

ה

$rowsAffected

משתנה מכיל את מספר השורות שנוספו או השתנו. כדי להפעיל שאילתת עדכון או מחיקה, פשוט שנה את השורה של שאילתת SQL ב-

$sql

משתנה.

הפעלת SQL Query ב- PowerShell באמצעות המחלקה System.Data.SqlClient

כדי לגשת ל- MS SQL Server מ- PowerShell, באפשרותך להשתמש במחלקה מוכללת אחרת של .NET - System.Data.SqlClient. להלן דוגמה של שאילתת SELECT בקובץ script של PowerShell עם SqlClient:

$server = "lon-sql01\testdb"
$database = "Test"
$sql = "select * from test_table"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $sql
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

דוגמה לשאילתת INSERT/DELETE/UPDATE:

$server = "lon-sql01\testdb"
$database = "Test"
$sql = "insert into test_table (test_col) Values ('Test')"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $sql
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open()
$rowsAffected = $SqlCmd.ExecuteNonQuery();
$SqlConnection.Close()

שאילתת SQL ב- PowerShell באמצעות מודול SQL Server Management Studio

כדי להשתמש במחלקות Microsoft.SqlServer.Smo (SMO), SQL Server Management Studio חייב להיות מותקן במחשב שלך.

טען את מודול ה-SMO, צור אובייקט שרת חדש ולאחר מכן הפעל שאילתת SELECT:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "lon-sql01\testdb"
$results = $serverInstance.Databases['test'].ExecuteWithResults('select * from test_table')
foreach ($res in $results.Tables) {
$nbsp;echo $res
}

עבור שאילתת הוספה/עדכון/מחיקה, הפעל

ExecuteNonQuery

:

$db = $serverInstance.Databases['test']
$db.ExecuteNonQuery("insert into test_table (test_col) Values ('123456')")

add-type -Path "C:\Users\username\Downloads\Microsoft.SqlServer.SqlManagementObjects.150.18208.0\lib\net45\Microsoft.SqlServer.Smo.dll"

Invoke-Sqlcmd Cmdlet מ- SQLServer PowerShell Module

כדי להשתמש ב- cmdlet Invoke-Sqlcmd, התקן את מודול SqlServer for PowerShell. הפעל את PowerShell עם הרשאות מנהל המערכת ובצע את הפקודה:

Install-Module -Name SqlServer

(הקש Y ולאחר מכן ENTER כדי לקבל את הודעות תוכנית ההתקנה.)

לאחר ההתקנה, באפשרותך לוודא שהמודול הותקן כראוי על-ידי הפעלת פקודה זו:

Get-Module SqlServer -ListAvailable

ה- cmdlet Invoke-Sqlcmd קל ואינטואיטיבי יותר מדרכים אחרות לחיבור לשרת Microsoft SQL מ- PowerShell. Invoke-Sqlcmd משתמש באותו תחביר עבור שאילתות SELECT ו- INSERT/UPDATE/DELETE.

הנה דוגמה לשאילתת בחירה:

Invoke-Sqlcmd -ServerInstance "lon-sql01\testdb" -Query "sp_who"

זוהי דוגמה לשאילתת INSERT:

Invoke-Sqlcmd -ServerInstance "lon-sql01\testdb" -Database "test1" -Query "insert into test_table (test_col) Values (‘123321’)"

בניגוד לשיטות אחרות, שאילתה ב-

Invoke-Sqlcmd

מוגדר תמיד ב-

-Query

פרמטר.

באיזו אפשרות חיבור SQL עליך להשתמש?

בחירה בין oledb/smo/sqlclient/invoke-sqlcmd מבוססת על המשימה והסביבה שבה אתה עומד להפעיל קובץ script של PowerShell.

אם ברצונך לפרוס סקריפט למספר שרתים (לדוגמה, הסקריפט שלך אוסף נתוני ניטור באופן מקומי), שימוש במודול SMO או SqlServer PowerShell (Invoke-SQLcmd) אינו סביר, מכיוון שיהיה עליך להתקין חבילות נוספות במארחים המרוחקים כדי להפעיל את הסקריפט, ועדיף להימנע מכך אם יש הרבה שרתים.

בתורו, מודול SqlServer for PowerShell מציע רכיבי cmdlet רבים אחרים לעבודה עם SQL Server שלך (תוכל לקבל מידע נוסף כאן: https://docs.microsoft.com/en-us/powershell/module/sqlserver). המודול מכיל פקודות נוספות לניהול SQL Server עצמו.

אם הסקריפט שלך יבצע משימות שאינן ניהוליות (אחראי לחלק כלשהו של לוגיקה עסקית, לדוגמה), כדאי להשתמש System.Data.SqlClient/SMO, מכיוון שהם מספקים כלי פיתוח נוחים יותר. היתרון של OleDB הוא שהוא יכול לעבוד לא רק עם SQL Server, אלא גם עם Access/Oracle/Firebird/Interbase.