Как выполнить SQL запрос к MSSQL Server из PowerShell?

В этой статье мы рассмотрим все рабочие способы подключения к SQL Server и выполнения SQL запросов из PowerShell. Обычно такая задача стоит перед системными администраторами, которые занимаются написанием скриптов и автоматизацией на PowerShell.

Есть много разных способов работы с SQL Server через PowerShell, и глядя на статьи в интернете в них легко запутаться, потому что в разных статьях описаны разные способы, и даже у опытного администратора может возникнуть вопросы.

T-SQL запросы в PowerShell через System.Data.OleDb

Так как PowerShell имеет доступ к классам .NET, то для выполнения T-SQL можно использовать классы, находящиеся в System.Data.OleDb.

Пример PowerShell скрипта с использованием System.Data.OleDb. Выполним SELECT запрос к таблице в базе данных MS SQL:

$dataSource = “server\instance”
$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

OleDb запросы к mssql server из powershell

Пример PowerShell скрипта для выполнения INSERT/UPDATE/DELETE запроса к базе MSSQL:

$dataSource = “server\instance”
$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 содержит в себе количество добавленных или измененных строк. Чтобы выполнить update или delete запрос, нужно просто изменить строку SQL запроса в переменной $sql.

SQL запрос в PowerShell к MSSQL через System.Data.SqlClient

Как и в случае с OleDb для обращения к SQL Server через System.Data.SqlClient, мы используем встроенные классы .NET. Пример SELECT запроса в скрипте PowerShell:

$server = "server\instance"
$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]

select запрос из powershell к базе данных Microsoft SQL Server

Пример INSERT/DELETE/UPDATE запроса:

$server = "server\instance"
$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()

Заметка. Обратите внимание, что код с классами SqlClient очень похож на код с OleDB. У этих классов схожий принцип работы:

  1. Создается объект соединения с MSSQL сервером;
  2. Создается объект с SQL запросом, и ему присваивается объект соединения;
  3. Затем в случае выполнения SELECT запроса создается объект адаптера и в контексте этого адаптера выполняется запрос;
  4. В случае выполнения INSERT/UPDATE/DELETE запроса объект с запросом (уже содержащий в себе объект соединения) выполняет метод ExecuteNonQuery().

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') "devsrv\devsrv"
$results = $serverInstance.Databases['test'].ExecuteWithResults('select * from test_table')
foreach ($res in $results.Tables) {
$nbsp;echo $res
}

sql запрос powershell через SQL Server Management Studio

Для insert/update/delete запрос выполняем ExecuteNonQuery:

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

Заметка. SMO библиотеки также можно установить через пакетный менеджер NuGet.

  1. Скачайте nuget.exe https://www.nuget.org/downloads;
  2. Запустите PowerShell с правами администратора и перейдите в директорию с файлом nugget.exe;
  3. Выполните: .\nuget.exe Install Microsoft.SqlServer.SqlManagementObjects;
    установка модуля SqlManagementObjects для powershell
  4. В той же директории где лежит nuget.exe появится папка Microsoft.SqlServer.SqlManagementObjects со всеми нужными DLL;
  5. Загрузите библиотеку SMO из DLL файла. Добавьте в ваш скрипт:

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

После этого классы SMO станут доступны для использования.

Командлет Invoke-Sqlcmd из модуля SQLServer для PowerShell

Для работы с командлетом Invoke-Sqlcmd нужно установить модуль SqlServer для PowerShell. Запустите PowerShell с правами администратора и введите

Install-Module -Name SqlServer

(Несколько раз примите уведомления инсталлятора, нажав Y и enter).

После установки можно проверить что модуль корректно установился, набрав:

Get-Module SqlServer -ListAvailable

установка модуля SqlServer для powershell

Командлет Invoke-Sqlcmd более простой и интуитивный в использовании чем другие способы подключения к SQL Server. Invoke-Sqlcmd использует один и тот же синтаксис для SELECT и INSERT/UPDATE/DELETE запросов.

Пример SELECT запроса:

Invoke-Sqlcmd -ServerInstance "server\instance " -Query "sp_who"

командлет Invoke-Sqlcmd

Пример INSERT запроса:

Invoke-Sqlcmd -ServerInstance "server\instance" -Database "test" -Query "insert into test_table (test_col) Values (‘Test123’)"

В отличие от других способов, запрос в Invoke-Sqlcmd всегда задается в параметре –Query.

Какой вариант подключения к SQL выбрать?

Выбирать между oledb/smo/sqlclient/invoke-sqlcmd нужно с учетом задачи которая перед вами стоит, и в зависимости от окружения, где планируется выполнять скрипт.

Если вы собираетесь распространять скрипт (например, ваш скрипт локально собирает данные для мониторинга) на множество серверов, то варианты c использованием SMO и плагина SqlServer (invoke-sqlcmd) стоит рассматривать в последнюю очередь, так как для отработки скрипта нужно будет устанавливать дополнительные пакеты в систему, чего хотелось бы избежать, при большом количестве серверов.

В свою очередь модуль SqlServer для PowerShell предоставляет множество других командлетов для работы с SQL Server (можно ознакомиться тут https://docs.microsoft.com/ru-ru/powershell/module/sqlserver/). Этот модуль содержит больше всего команд для администрирования самого SQL Server.

Если ваш скрипт будет выполнять не административные задачи (а, например, отвечать за какую-то часть бизнес-логики), то стоит рассмотреть System.Data.SqlClient/SMO, так как они предоставляют более удобные инструменты для разработки. Плюс OleDb в том, что он может работать не только с SQL Server, но и, например, с Access.


Предыдущая статья Следующая статья


Комментариев: 0 Оставить комментарий

Оставить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Я не робот( Обязательно отметьте)