powershell запуск sql скрипта
Подключение к MS SQL Server из скрипта PowerShell
Подключение к базе данных из скрипта PowerShell и выполнение запроса к базе можно сделать несколькими способами. Приведу 2 из них, которые на мой взгляд самые простые.
1-й способ. Подключение к базе данных MS SQL Server с помощью класса SqlConnection
Подключение к MS SQL Server с учетными данными пользователя, вошедшего в Windows, т.е. так называемая Windows-авторизация, выполняется так:
Подключение к MS SQL Server со стандартной авторизацией, т.е. по логину и паролю в базе данных:
Выполнить sql-запрос и обработать результаты теперь можно, например, таким способом:
Выполнить sql-запрос на изменение/добавление/удаление данных можно следующим образом:
В последней строке «Out-Null» используется для подавления вывода результатов выполнения запроса. Дело в том, что для запросов типа INSERT, UPDATE, DELETE команда ExecuteNonQuery возвращает количество обработанных записей, а в остальных случаях возвращает «-1». Поэтому, если происходит массовое изменение данных, то вывод лучше подавить.
Закрыть подключение к базе можно командой:
Как видите, всё довольно просто.
2-й способ. Подключение к базе данных MS SQL Server с помощью библиотеки ADO
Библиотека ADO (Microsoft ActiveX Data Object) позволяет выполнять подключение не только к базам MS SQL Server, но и ко множеству других видов баз данных. Она очень удобна в использовании и универсальна. Написав программу для работы с одним типом базы данных, вам не придется переделывать всю программу для другой базы, а всего-лишь сделать другую строку подключения к базе. Приведу несколько примеров работы с базой данных MS SQL Server в PowerShell.
Подключение к базе данных:
Выполнение sql-запроса и обработка результатов:
Выполнение sql-запроса на изменение/добавление данных:
Думаю, приведенных 2-х способов вполне достаточно, чтобы начать работать с БД SQL Server из скриптов PowerShell, читать данные, менять их и добавлять. В следующей статье будет рассмотрено подключение к базе данных Access.
Запуск Windows PowerShell из среды SQL Server Management Studio
Сеансы Windows PowerShell можно запустить из обозревателя объектов в SQL Server Management Studio (SSMS). SSMS запускает Windows PowerShell, загружает модуль SqlServer и задает контекст пути для связанного узла в дереве обозревателя объектов.
Существует два модуля SQL Server PowerShell — SqlServer и SQLPS.
Самым актуальным модулем PowerShell является модуль SqlServer.
Модуль SQLPS входит в состав установки SQL Server (для обеспечения обратной совместимости), но больше не обновляется.
Модуль SqlServer содержит обновленные версии командлетов в SQLPS и новые командлеты для поддержки последних функций SQL.
Установите модуль SqlServer из коллекции PowerShell.
Подробные сведения см. в статье SQL Server PowerShell.
Когда вы указываете, что PowerShell необходимо вызвать для объекта в обозревателе объектов, SQL Server Management Studio начинает сеанс Windows PowerShell, в котором были загружены и зарегистрированы оснастки SQL Server PowerShell. Путем для сеанса становится расположение объекта, выбранного правой кнопкой мыши в обозревателе объектов.
Например, если щелкнуть правой кнопкой мыши объект базы данных AdventureWorks в обозревателе объектов, а затем выбрать Запустить PowerShell, путь Windows PowerShell будет выглядеть так:
Запуск PowerShell
Запуск PowerShell из среды SQL Server Management Studio
Откройте обозреватель объектов.
Перейдите к узлу для объекта, с которым выполняется работа.
Щелкните объект правой кнопкой мыши и выберите команду Запустить PowerShell.
Разрешения
При открытии из среды SQL Server Management Studio PowerShell не запускается с правами администратора, и это может стать причиной того, что некоторые действия не будут выполнены, например вызовы WMI.
Как выполнить SQL запрос к MSSQL Server из PowerShell?
В этой статье мы рассмотрим все рабочие способы подключения к SQL Server и выполнения SQL запросов из PowerShell. Обычно такая задача стоит перед системными администраторами, которые занимаются написанием скриптов и автоматизацией на PowerShell.
Есть много разных способов работы с SQL Server через PowerShell, и глядя на статьи в интернете в них легко запутаться, потому что в разных статьях описаны разные способы, и даже у опытного администратора может возникнуть вопросы.
T-SQL запросы в PowerShell через System.Data.OleDb
Пример PowerShell скрипта с использованием System.Data.OleDb. Выполним SELECT запрос к таблице в базе данных MS SQL:
Пример PowerShell скрипта для выполнения INSERT/UPDATE/DELETE запроса к базе MSSQL:
SQL запрос в PowerShell к MSSQL через System.Data.SqlClient
Пример INSERT/DELETE/UPDATE запроса:
SQL запрос в PowerShell через модуль SQL Server Management Studio
Для использования классов Microsoft.SqlServer.Smo (SMO), в системе должна быть установлена SQL Server Management Studio.
Загружаем модуль SMO и создаём новый объект сервера, затем выполняем SELECT запрос:
Для insert/update/delete запрос выполняем ExecuteNonQuery:
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 с правами администратора и введите
(Несколько раз примите уведомления инсталлятора, нажав Y и enter).
После установки можно проверить что модуль корректно установился, набрав:
Командлет Invoke-Sqlcmd более простой и интуитивный в использовании чем другие способы подключения к SQL Server. Invoke-Sqlcmd использует один и тот же синтаксис для SELECT и INSERT/UPDATE/DELETE запросов.
Пример SELECT запроса:
Пример INSERT запроса:
Какой вариант подключения к SQL выбрать?
Выбирать между oledb/smo/sqlclient/invoke-sqlcmd нужно с учетом задачи которая перед вами стоит, и в зависимости от окружения, где планируется выполнять скрипт.
Если вы собираетесь распространять скрипт (например, ваш скрипт локально собирает данные для мониторинга) на множество серверов, то варианты c использованием SMO и плагина SqlServer (invoke-sqlcmd) стоит рассматривать в последнюю очередь, так как для отработки скрипта нужно будет устанавливать дополнительные пакеты в систему, чего хотелось бы избежать, при большом количестве серверов.
Работа с MS SQL из Powershell на Linux
Эта статья чисто практическая и посвящена моей грустной истории
Готовясь к Zero Touch PROD для RDS (MS SQL), про который нам прожужжали все уши, я сделал презентацию (POC — Proof Of Concept) автоматизации: набора powershell скриптов. После презентации, когда стихли бурные, продолжительные аплодисменты, переходящие в несмолкаемые овации, мне сказали — все это хорошо, но вот только по идеологическим причинам у нас все Jenkins slaves работают под Linux!
Разве так можно? Взять такого теплого, лампового DBA из под Windows и сунуть его в самое пекло powershell под Linux? Разве это не жестоко?
sqlcmd vs Invoke-SqlCmd
Если в конце не будет EXIT, то sqlcmd перейдет к ожиданию ввода, а если перед EXIT не будет GO, то последняя команда не отработает. В файл вывода попадает весь вывод, selects, сообщения, print итд.
Invoke-SqlCmd выдает результат в виде DataSet, DataTables или DataRows. Поэтому, если обработать результат простого select вы можете и через sqlcmd, разобрав его вывод, то вывести что-то сложное практически нереально: для этого есть Invoke-SqlCmd. Но есть у этой команды и свои приколы:
Подстановка переменных
В скрипте на SQL мы используем подстановки:
Так вот. В *nix подстановки переменных не работают. Параметр -v игнорируется. У Invoke-SqlCmd игнорируется -Variables. Хотя параметр, который задает сами переменные, игнорируется, сами подстановки работают — вы можете использовать любые переменные из Shell. Однако я обиделся на переменные и решил от них вообще не зависеть, и поступил грубо и примитивно, благо скрипты на sql короткие:
Это, как вы поняли, тест уже с юниксовой версии.
Загрузка файлов
В виндовой версии у меня любая операция сопровождалась аудитом: выполнили sqlcmd, получили какую-то ругань в output file, приложили этот файл к табличке аудита. Благо SQL server работал на том же сервере, что и Jenkins, это делалось примерно так:
Таким образом мы заглатываем файл BCP целиком, и пихаем в поле nvarchar(max) таблицы аудита. Разумеется, вся эта система рассыпалась, так как вместо SQL server я получил RDS, а BULK INSERT вообще по \\UNC не работает из-за попытки взять эксклюзивный лок на файл, а с RDS это вообще изначально обречено. Так что я решил изменить дизайн системы, храня аудит построчно:
И писать в эту таблицу так:
Для выбора содержимого надо делать select по ID, выбирая в порядке n (identity).
В следующей статье я более подробно остановлюсь на том, как это все взаимодействует с Jenkins.
SQL Server PowerShell
Самым актуальным модулем PowerShell является модуль SqlServer.
Модуль SQLPS входит в состав установки SQL Server (для обеспечения обратной совместимости), но больше не обновляется.
Модуль SqlServer содержит обновленные версии командлетов в SQLPS и новые командлеты для поддержки последних функций SQL.
Предыдущие версии модуля SqlServer входили в состав среды SQL Server Management Studio (SSMS), но только с SSMS версий 16.x.
Для работы PowerShell с SSMS версии 17.0 и более поздних установите модуль SqlServer из коллекции PowerShell.
Почему модуль SQLPS изменился на SqlServer?
Для поставки обновлений SQL PowerShell было необходимо изменить удостоверение модуля SQL PowerShell, а также программу-оболочку, известную как SQLPS.exe. В связи с эти изменением теперь существует два модуля SQL PowerShell — модуль SqlServer и модуль SQLPS.
Обновите скрипты PowerShell, если вы импортируете модуль SQLPS.
Рекомендуется запустить сценарий с помощью Import-Module SQLServer, чтобы избежать проблем параллельной установки, если модуль SQLPS установлен на том же компьютере.
Этот раздел относится к скриптам, выполняемым из PowerShell, а не агента SQL. Новый модуль можно использовать с шагами задания агента SQL с помощью #NOSQLPS.
Компоненты SQL Server PowerShell
Компоненты модуля SqlServer:
Набор командлетов, которые поддерживают такие действия, как запуск скрипта sqlcmd, содержащего инструкции Transact-SQL или XQuery.
Поставщик AS и командлеты, которые ранее устанавливались отдельно.
Версии SQL Server
Командлеты SQL PowerShell можно использовать для управления экземплярами базы данных SQL Azure, Azure Synapse Analytics и во всех поддерживаемых продуктах SQL Server.
Идентификаторы SQL Server, содержащие символы, не поддерживаемые в путях Windows PowerShell
Командлеты Encode-Sqlname и Decode-Sqlname помогают указать идентификаторы SQL Server, содержащие символы, не поддерживаемые в путях Windows PowerShell. Дополнительные сведения см. в статье SQL Server Identifiers in PowerShell.
Используйте командлет Convert-UrnToPath, чтобы преобразовать уникальное имя ресурса для объекта ядра СУБД в путь для поставщика SQL Server PowerShell. Дополнительные сведения см. в статье Convert URNs to SQL Server Provider Paths.
Выражения запросов и уникальные имена ресурсов
Выражения запроса — это строки, которые используют синтаксис, напоминающий XPath для указания набора условий, перечисляющих один или несколько объектов в иерархии объектной модели. Уникальное имя ресурса (URN) — это определенный тип строки выражения запроса, который уникально определяет один объект. Дополнительные сведения см. в статье Query Expressions and Uniform Resource Names.
Агент SQL Server
Устранение неполадок с SQLPS
Если шаги задания агента (подсистема PowerShell) завершаются со следующей ошибкой, этот раздел поможет найти и устранить проблему.
Для шага задания возникла ошибка в строке 1 в скрипте PowerShell. Соответствующая строка — import-module SQLPS. Исправьте скрипт и запланируйте задание заново. PowerShell возвращает сведения об ошибке: The specified module ‘SQLPS’ was not loaded because no valid module file was found in any module directory (Указанный модуль SQLPS не был загружен, так как в каталоге модуля не найден допустимый файл модуля).
Модуль SQLPS должен быть доступен в переменной среды PSModulePath. При удалении SSMS 16.x можно удалить SQLPS из PSModulePath. Чтобы проверить текущие значения, хранящиеся в PSModulePath, выполните следующую команду PowerShell:
Модуль SQLServer с агентом SQL
Если вы хотите использовать модуль SqlServer в шаге задания агента SQL, можно поместить этот код в первые две строки скрипта.