Запрос T-SQL из PowerShell
Вопрос. Хочу протестировать список соединений. Если какой-то хост недоступен, добавить его имя и время опроса в базу:
$query = "merge into tgt using (select '$h' as Server '$start' as Start from t1) src on (tgt.Server = src.Server) when not matched then insert(Server, Start) values(src.Server, src.Start);" А система ругается: Invoke-Sqlcmd : Неправильный синтаксис около конструкции «2022.11.16 11:01:53». Собственно, это дата. Если просто вставить данные:
"Insert into t1 (Server, Start) Values ('$h', '$start') Where not exists (Select * from t1 Where Server like '$h');" То все ОК. Что у меня не так в MERGE? Не удалось сюда добавить переменные, сайт ругается на неправильный код. В общем, $start = (Get-Date).ToString(‘yyyy.MM.dd HH:mm:ss’) $h = ‘Server’
Редактор запросов SQL Server Management Studio (SSMS)
В этой статье описываются возможности и функции редактора запросов SQL Server Management Studio (SSMS).
Если вы хотите узнать, как использовать справку F1 по Transact-SQL (T-SQL), ознакомьтесь с разделом Справка F1 по Transact-SQL.
Если вы хотите узнать о задачах, которые можно выполнять с помощью редактора, перейдите к разделу Задачи редактора.
Редакторы в среде SSMS совместно используют стандартную архитектуру. Текстовый редактор реализует базовые функциональные возможности и может быть использован в качестве базового редактора для текстовых файлов. Другие редакторы (редакторы запросов) расширяют эти функциональные возможности, подключая языковую службу, определяющую синтаксис одного из языков, поддерживаемых в SQL Server. В редакторах запросов также присутствуют различные уровни поддержки таких функций редактора, как IntelliSense или отладка. В набор редакторов запросов входят редактор запросов компонента ядра СУБД, используемый для создания скриптов, содержащих инструкции T-SQL и XQuery, редактор многомерных выражений для работы с языком многомерных выражений, редактор расширений интеллектуального анализа данных для работы с языком расширений интеллектуального анализа данных и редактор XML/A для работы с языком XML для аналитики. Вы можете использовать редактор запросов, чтобы создавать и выполнять скрипты, содержащие инструкции Transact-SQL.
Панель инструментов редактора SQL
Если открыт редактор запросов, отображается панель инструментов редактора SQL со следующими кнопками.
Можно также добавить панель инструментов редактора SQL, выбрав меню Вид , Панели инструментов, а затем выбрав Редактор SQL. Если добавить панель инструментов редактора SQL, когда окно редактора запросов не открыто, все кнопки будут недоступны.
Кнопка «Подключиться» на панели инструментов редактора
Открывает диалоговое окно Соединение с сервером. Используйте это диалоговое окно, чтобы установить соединение с сервером.
Подключиться к базе данных можно также с помощью контекстного меню.
Изменение подключения с помощью панели инструментов редактора
Открывает диалоговое окно Соединение с сервером. Используйте это диалоговое окно, чтобы установить соединение с другим сервером.
Изменить подключение можно также с помощью контекстного меню.
Доступные базы данных с помощью панели инструментов редактора
Изменяет подключение и соединяет с другой базой данных того же сервера.
Кнопка «Выполнить» на панели инструментов редактора
Выполняет выбранный код или, если код не выбран, выполняет весь код редактора запросов.
Запрос можно выполнить также с помощью клавиши F5 или контекстного меню.
Отмена выполнения запроса с помощью панели инструментов редактора
Отправляет на сервер запрос отмены. Отмена выполнения некоторых запросов занимает некоторое время, так как необходимо дождаться подходящих условий отмены. После отмены транзакций могут происходить задержки на время отката транзакций.
Отменить выполнение запроса можно также с помощью клавиш ALT+BREAK.
Кнопка «Анализ» на панели инструментов редактора
Проверяет синтаксис выбранного кода. Если код не выбран, проверяется весь синтаксис кода в окне редактора запросов.
Проверить код в редакторе запросов можно также с помощью клавиш CTRL+F5.
Отображение предполагаемого плана выполнения с помощью панели инструментов редактора
Запрашивает план выполнения запроса у обработчика запросов, не выполняя этот запрос, и выводит план в окне План выполнения. Этот план использует статистику индексов для оценки ожидаемого числа строк, возвращаемых на каждом этапе выполнения запроса. Фактически используемый план запроса может отличаться от предполагаемого плана выполнения. Это может происходить, если количество возвращаемых строк расходится с оценкой, поэтому обработчик запросов вносит изменения в план в целях повышения его эффективности.
Отобразить предполагаемый план выполнения можно также с помощью клавиш CTRL+L или контекстного меню.
Параметры запроса с помощью панели инструментов редактора
Открывает диалоговое окно Параметры запроса . С помощью этого диалоговое окно можно настроить параметры по умолчанию для выполнения запроса и для получения результатов запроса.
Открыть окно Параметры запроса можно также с помощью контекстного меню.
Поддержка IntelliSense с помощью панели инструментов редактора
Указывает, доступны ли функциональные возможности технологии IntelliSense в редакторе запросов ядра СУБД. По умолчанию этот параметр активирован.
Вызвать команду Функция Intellisense включена можно также с помощью клавиш CTRL+B > CTRL+I или контекстного меню.
Включение фактического плана выполнения с помощью панели инструментов редактора
Выполняет запрос, возвращает результаты запроса и использует план выполнения для запроса. Запросы отображаются в виде графического плана запроса в окне План выполнения.
Вызвать команду Включить действительный план выполнения можно также с помощью клавиш CTRL+M или контекстного меню.
Включение статистики динамических запросов с помощью панели инструментов редактора
Позволяет анализировать процесс выполнения запроса в режиме реального времени по мере передачи управления от одного оператора плана запроса другому.
Вызвать команду Включить статистику активных запросов можно также с помощью контекстного меню.
Включение статистики клиента с помощью панели инструментов редактора
Включает окно Статистика клиента , содержащее статистические данные по запросу и сетевым пакетам, а также по времени выполнения запроса.
Вызвать команду Включить статистику активных запросов можно также с помощью клавиш SHIFT+ALT+S или контекстного меню.
Результаты для текста с помощью панели инструментов редактора
Возвращает результаты запроса в текстовом виде в окне Результаты .
Вывести результаты в текст можно также с помощью клавиш CTRL+T или контекстного меню.
Результаты сетки с помощью панели инструментов редактора
Возвращает результаты запроса в виде одной или нескольких сеток в окне Результаты . Этот параметр по умолчанию включен.
Вывести результаты в сетку можно также с помощью клавиш CTRL+D или контекстного меню.
Результаты для файла с помощью панели инструментов редактора
По завершении выполнения запроса открывается диалоговое окно Сохранить результаты . В поле Сохранить ввыберите папку, в которой необходимо сохранить файл. В поле Имя файла введите имя файла, а затем нажмите кнопку Сохранить, чтобы сохранить результаты запроса в файл отчета с расширением RPT. Для настройки дополнительных параметров выберите стрелку вниз на кнопке Сохранить, а затем пункт Выбор кодировки для сохранения.
Вывести результаты в текст можно также с помощью клавиш CTRL+SHIFT+F или контекстного меню.
Кнопка «Закомментировать выделенные строки» на панели инструментов редактора
Преобразует текущую строку в комментарий, добавляя оператор комментария (—) в начало строки.
Закомментировать строку можно также с помощью клавиш CTRL+K > CTRL+C.
Кнопка «Раскомментировать выделенные строки» на панели инструментов редактора
Преобразует текущую строку в активную инструкцию исходного кода, удаляя оператор комментария (—) в начале строки.
Раскомментировать строку можно также с помощью клавиш CTRL+K > CTRL+U.
Уменьшение отступа с помощью панели инструментов редактора
Перемещает текст строки влево, удаляя пробелы в начале строки.
Увеличение отступа строки с помощью панели инструментов редактора
Перемещает текст строки вправо, добавляя пробелы в начале строки.
Указание значений параметров шаблона с помощью панели инструментов редактора
Открывает диалоговое окно, которое можно использовать, чтобы задать значения параметров в хранимых процедурах и функциях.
Контекстное меню
Чтобы вызвать контекстное меню, щелкните правой кнопкой мыши любую область редактора запросов. В контекстном меню отображаются те же команды, что и на панели инструментов редактора SQL. В контекстном меню, помимо команд Подключиться и Выполнить, отображаются и другие команды, например Вставить фрагмент кода и Заключить в.
Вставка фрагмента кода с помощью контекстного меню
Фрагменты кода Transact-SQL — это шаблоны, которые можно использовать в качестве отправной точки при написании новых инструкций Transact-SQL в Редактор запросов.
Окружение с помощью контекстного меню
Фрагмент кода, который можно использовать в качестве отправной точки при включении набора инструкций Transact-SQL в блок BEGIN, IF или WHILE.
Команда «Подключиться» в контекстном меню
В отличие от панели инструментов SSMS в контекстном меню есть больше вариантов подключения.
- Подключиться — открывает диалоговое окно «Соединение с сервером». Используйте это диалоговое окно, чтобы установить соединение с сервером.
- Отключить — отключает текущее окно редактора запросов от сервера.
- Отключить все запросы — отключает все соединения запросов.
- Изменить соединение — открывает диалоговое окно «Соединение с сервером». Используйте это диалоговое окно, чтобы установить соединение с другим сервером.
Открытие сервера в обозреватель объектов с помощью контекстного меню
В обозревателе объектов представлен иерархический пользовательский интерфейс для просмотра и управления объектами в каждом экземпляре SQL Server. Панель сведений обозревателя объектов предлагает табличное представление объектов экземпляра и возможность поиска указанных объектов. Возможности обозревателя объектов могут незначительно различаться в зависимости от типа сервера, но в общем случае включают функции разработки для баз данных, а также функции управления для всех типов серверов.
Команда «Выполнить» в контекстном меню
Выполняет выбранный код или, если код не выбран, выполняет весь код в редакторе запросов.
Отображение предполагаемого плана выполнения с помощью контекстного меню
Запрашивает план выполнения запроса у обработчика запросов, не выполняя этот запрос, и выводит план в окне План выполнения . Этот план использует статистику индексов для оценки ожидаемого числа строк, возвращаемых на каждом этапе выполнения запроса. Фактически используемый план запроса может отличаться от предполагаемого плана выполнения. Если количество возвращаемых строк отличается от оценки, а обработчик запросов изменяет план, который будет более эффективным, это может произойти.
Поддержка IntelliSense с помощью контекстного меню
Указывает, доступны ли функциональные возможности технологии IntelliSense в редакторе запросов ядра СУБД. По умолчанию этот параметр активирован.
Запрос трассировки в SQL Server Profiler с помощью контекстного меню
SQL Server Profiler — это интерфейс для создания трассировок и управления ими, а также для анализа и воспроизведения полученных результатов. События сохраняются в файле трассировки, который затем может быть проанализирован или использован для воспроизведения определенных последовательностей шагов для выявления возникших проблем.
Анализ запроса в помощник по настройке ядра СУБД с помощью контекстного меню
Помощник по настройке ядра СУБД (Майкрософт) анализирует базы данных и составляет рекомендации по оптимизации производительности запросов. Помощник по настройке ядра СУБД используется для выбора и создания оптимальных наборов индексов, индексированных представлений и секций таблицы, не обладая экспертным уровнем понимания структуры баз данных или внутренних процессов SQL Server. Помощник по настройке ядра СУБД позволяет выполнять следующие задачи.
Конструктор запросов в редакторе с помощью контекстного меню
Конструктор запросов и представлений открывается при открытии определения представления, показе результатов запроса или представления, при создании или открытии запроса.
Включение фактического плана выполнения с помощью контекстного меню
Выполняет запрос, возвращает результаты запроса и использует план выполнения для запроса. Запросы отображаются в виде графического плана запроса в окне План выполнения.
Включение статистики динамических запросов с помощью контекстного меню
Позволяет анализировать процесс выполнения запроса в режиме реального времени по мере передачи управления от одного оператора плана запроса другому.
Включение статистики клиента с помощью контекстного меню
Включает окно Статистика клиента , содержащее статистические данные по запросу и сетевым пакетам, а также по времени выполнения запроса.
Команды для вывода результатов в контекстном меню
В контекстном меню можно выбрать любой вариант вывода результатов.
- Результаты в текст — отображает результаты запроса в текстовом виде в окне Результаты.
- Результаты в сетку — отображает результаты запроса в виде одной или нескольких сеток в окне Результаты.
- Результаты в виде файла — после выполнения запроса открывается диалоговое окно Сохранить результаты. В поле Сохранить ввыберите папку, в которой необходимо сохранить файл. В поле Имя файла введите имя файла, а затем нажмите кнопку Сохранить, чтобы сохранить результаты запроса в файл отчета с расширением RPT. Для настройки дополнительных параметров выберите стрелку вниз на кнопке Сохранить, а затем пункт Выбор кодировки для сохранения.
окно свойств с помощью контекстного меню
В окно свойств описывается состояние элемента в СРЕДЕ SQL Server Management Studio, например подключение или оператор Showplan, а также сведения об объектах базы данных, таких как таблицы, представления и конструкторы.
Окно свойств используется для просмотра свойств текущего соединения. Многие свойства в окне свойств доступны только для чтения, однако могут быть изменены другими средствами Management Studio. Например, свойство «База данных» запроса в окне свойств доступно только для чтения, но может изменяться на панели инструментов.
Параметры запроса с помощью контекстного меню
Открывает диалоговое окно Параметры запроса . С помощью этого диалогового окна можно настроить параметры по умолчанию для выполнения запроса и получения результатов запроса.
Справка Transact-SQL F1
Редактор запросов поддерживает связывание с справочной статьей для конкретной инструкции Transact-SQL при выборе F1. Чтобы сделать это, выделите имя инструкции Transact-SQL и нажмите клавишу F1. Затем поисковая система справки ищет статью, которая содержит атрибут справки F1, соответствующий выделенной строке.
Если поисковая система справки не находит статью со справкой F1 ключевое слово, которая точно соответствует выделенной строке, отображается эта статья. В этом случае есть два способа поиска нужной информации:
- Скопируйте и вставьте из буфера редактора строку, выделенную на вкладке поиска электронной документации по SQL Server, и воспользоваться функцией поиска.
- Выделите только часть инструкции Transact-SQL, скорее всего, соответствует ключевое слово справке F1, примененной к статье, и снова выберите F1. Поисковая система требует точного соответствия между выделенной строкой и справкой F1 ключевое слово, назначенной статье. Если выделенная строка содержит элементы, уникальные для вашей среды, например имена столбцов или параметров, поисковая система не сможет найти совпадение. Примерами строк для выделения могут служить следующие:
- Имя инструкции Transact-SQL, например SELECT , CREATE DATABASE или BEGIN TRANSACTION .
- Имя встроенной функции, например SERVERPROPERTY или @@VERSION .
- Имя таблицы хранимых процедур системы или представлений, например sys.data_spaces или sp_tableoption .
Задачи редактора
Описание задачи Статья Описывает различные способы открытия редакторов в среде SSMS. Открытие редактора (среда SQL Server Management Studio) Настройка параметров для различных редакторов, например нумерация линий или параметры IntelliSense. Настройка редакторов (среда SQL Server Management Studio) Управление режимом просмотра, например переносом по словам, разделением окна или вкладками. Управление режимами редактирования и просмотра Настройка параметров форматирования, например скрытый текст или выступы. Управление форматированием кода Способы навигации по тексту в окне редактора, например функции добавочного поиска или перехода. Перемещение по коду и тексту Настройка параметров присвоения цветов различным классам синтаксиса, что облегчает чтение сложных инструкций. Выделение цветом в редакторах запросов Перетаскивание текста с одного места в скрипте в другое. Перетаскивание текста Установка закладок для более легкого поиска важных элементов кода. Управление закладками Вывод скриптов или результатов в окно или сетку. Печать кода и результаты Просмотр и использование базовых функций редактора запросов многомерных выражений. Создание скриптов Analysis Services Просмотр и использование базовых функций редактора запросов расширения интеллектуального анализа данных. Создание DMX-запроса Просмотр и использование базовых функций редактора запросов XML/A. Редактор XML (среда SQL Server Management Studio) Использование функций sqlcmd в редакторе запросов ядра СУБД. Изменение скриптов SQLCMD при помощи редактора запросов Использование фрагментов кода в редакторе запросов ядра СУБД. Фрагменты кода — это шаблоны часто используемых инструкций или блоков. Их можно настроить или расширить, включив в них фрагменты, специфичные для сайта. Добавление фрагментов кода Transact-SQL Использование отладчика Transact-SQL для пошагового просмотра кода и просмотра отладочных данных, например значений переменных и параметров. Отладчик Transact-SQL Связанный контент
- Настройка меню и сочетаний клавиш
- Сочетания клавиш SQL Server Management Studio
10 крутых вещей, которые можно сделать с помощью Windows PowerShell
Если PowerShell кажется вам сложным для использования его для повседневных задач, «круто» может быть не тем словом, которое у вас ассоциируете с ним. Но PowerShell является основной частью Exchange, Windows Server и SQL Server, и он обладает огромными возможностями, которые мы все должны понять, принять и использовать, чтобы облегчить и автоматизировать наши текущие дела.
Я собираюсь немного поразвлечься и показать вам несколько хитростей, которые определенно пригодятся вам в решении нудных задач на работе. Кроме того, вы будете выглядеть намного круче в глазах ваших коллег, когда сможете решить проблему из командной строки. Согласитесь, это выглядит привлекательно, чем щелкать правой кнопкой мыши и что-то исправлять.
Будьте очень осторожны так как это инструмент достойный своего названия (Power – Сила, Shell – Оболочка). PowerShell может легко вызвать массовые изменения конфигурации, как положительные, так и отрицательные, поэтому для безопасности создайте тестовую или лучше всего виртуальную среду для вашего обучения и тестирования. Если вы будете тестировать у себя на компьютере, то создайте точку восстановления системы. Чтобы во время выполнения одной из следующих команд что-то пойдет не так, вы всегда могли восстановить свой компьютер.
10 крутых вещей, которые можно сделать с помощью Windows PowerShell
1: Отчёты о подключённых USB оборудованиях
PowerShell даёт возможность работать с Windows Management Instrumentation (WMI). С помощью PowerShell, вы можете сделать WMI — запрос для получения информации о USB — устройствах, которые установлены как на локальной, так и на удаленных системах.
gwmi Win32_USBControllerDevice -computername DBSERVER1 |fl Antecedent,Dependent
В данной команде будет применен фильтр возврата предшествующих и зависимых полей с компьютера DBSERVER1 . Если вы хотите получить полную информацию о USB-устройствах в системе, вы можете убрать оператор | и fl . Это весьма удобный способ для ведения отчётов по серверам, к которым подключены USB — устройства с лицензией.
2: Выполнение ваших любимых задач CMD в PowerShell
Да, вы можете перестать использовать командную строку (CMD) и начать выполнять все те же задачи в PowerShell. Это поможет сделать процесс обучение немного проще и помочь вам лучше ознакомиться с интерфейсом. К сожалению, PowerShell невозможно вызвать через окно «выполнить» с помощью трёх букв, подобно CMD. Но вы можете назначить сочетание клавиш для быстрого запуска PowerShell, например, Ctrl + Shift + P .
3: Принудительное завершение процесса в PowerShell
Если зависла какая-то служба Windows, вы можете использовать PowerShell для завершения процесса так же, как и через Диспетчер Задач. Например, для закрытия BadThread.exe , вы делаете следующее:
get-process BadTh* Результаты выведут нам нужные данные в таком формате: Handles NPM(K) PM(K) WS(K) VM(M) CPU(s) Id ProcessName ------- ------ ----- ----- ----- ------ -- ----------- 19 5 -321955 -312219 -154 32.76 7583 BadThread
После того, как мы идентифицируем Process ID , вы можете принудительно закрыть зависший процесс введя команду:
stop-process -id 7583
В тот же момент процесс BadThread будет принудительно остановлен, и вы сможете возобновить попытку запуска службы. Которую можете сделать прямо здесь, в PowerShell.
4: Используйте PSDrive для большего, чем просто просмотр дисков
Команда PSDrive позволяет просматривать объекты Windows за пределами традиционных сетей, а также локальных или съемных дисков. Например, чтобы посмотреть диски в разделе верхнего уровня реестра HKEY_LOCAL_MACHINE , вы можете использовать HKLM PSDrive . Чтобы войти в реестр, введите следующую команду:
PS C:> cd HKLM: PS HKLM:/>
Затем вы переключаетесь в раздел регистра где сможете просмотреть список всех объектов и удалять их, если вам это нужно.
5: Экспорт NTFS разрешений папки – как обычно, так и рекурсивно
Управление разрешениями NTFS — это отдельный вопрос, но с помощью PowerShell можно экспортировать список разрешений для аудита доступов или для быстрого анализа списка ACLs для настройки политик безопасности. Это лучший вариант для создания отчётности в формате периодически запускаемого скрипта, или вы можете запускать его по требованию, например, для диагностики конкретной проблемы, связанной с доступами. Например, используя следующую команду:
PS E:>Get-Acl N:Data
Это даст вам быстрый ответ с результатами ваших прав безопасности по указанному пути N:Data (обратите внимание, что команда не даёт доступ к ресурсу). Данная команда не даст нам общую картину всех доступов всего пути, а только отчёт только об указанном пути. Но если вы хотите включить рекурсию для всего пути, вы можете использовать другую команду. Для того же пути N:Data вы должны использовать командлет Get-ChildItem (cmdlet) в PowerShell в сочетании с командлетом Get-Acl . Рассмотрим следующий пример:
PS E:>Get-ChildItem N:Data -recurse | Get-Acl
Данная команда будет отображать списки ACL для содержимого всего пути N:Data . Разберём как это работает: командлет Get-ChildItem показывает нам все объекты файловой системы по указанному пути N:Data , а дальше весь список объектов передаётся командлету Get-Acl который предоставляет результаты (списки ACL) для каждого объекта.
Если вы хотите заархивировать данные в документ (CSV), вам нужно добавить | export-csv c:filename.csv в конце команды. Кроме этого вы можете извлечь в обычный текстовый файл с помощью добавления командлета > C:filename.txt . Обратите внимание что, когда вы используете параметр -recurse, он будет применяться во всех вложенных файлах и папках. Поэтому будьте внимательны, когда используете его для инвентаризации объёмных томов или же по сети.
6: Отличия PowerShell 2.0
PowerShell 2.0 включает в себя графический интерфейс что является удобной особенностью данной системы. Скрипты PowerShell сохраняются как файлы .ps1 , что позволяет нам легко изменять, импортировать и мигрировать сценарии в различные системы. На скриншоте ниже показан пример списка разрешений NTFS в графическом режиме.
Примечание для PowerShell 2.0: Перед тем как начать использовать PowerShell 2.0 версию, необходимо настроить политику исполнения с помощью первой версии PowerShell. Введите одну из следующих команд для настройки политики исполнения под ваши нужды:
PS C:> Set-ExecutionPolicy Restricted (только проверка) PS C:> Set-ExecutionPolicy AllSigned (наиболее безопасный) PS C:> Set-ExecutionPolicy RemoteSigned (средний уровень безопасности) PS C:> Set-ExecutionPolicy Unrestricted (наименее безопасный)
При этом не забудьте, что для PowerShell 2.0 требуется пакет WS-MAN v1.1 и Microsoft.NET Framework 3.0 для графического интерфейса.
7: Горячие клавиши в графическом интерфейсе PowerShell
Если вы знакомы со средой Microsoft SQL Query Analyzer, вы по достоинству оцените некоторые из этих сочетаний клавиш. В PowerShell GUI вы можете выбрать одну или несколько строк и выполнить их разом одним нажатием клавиши F5 . Кроме того, если вы изменили скрипт, то для экономии времени при редактировании и тестировании доступны привычные Ctrl + S для сохранения, Ctrl + Z для отмены, Ctrl + C для копирования и Ctrl + V для вставки.
8: Фоновый режим для длительных задачи
Если вы собираетесь использовать команду, выполнение которого займёт некоторое время, вы можете запустить PowerShell в фоновом режиме до её завершения. Таким образом, можно отправить серию команд на автоматическое выполнение по своему собственному расписанию. Чтобы запустить команду в фоновом режиме необходимо добавить в начало параметр –psjob . А ещё можно узнать о состоянии любого из заданий с помощью следующей команды:
PS C:> get-psjob
В дополнительном окне вы увидите таблицу с результатами о текущих состояний ваших заданий, дополнительно с уникальными идентификаторами сеанса для каждой задачи отдельно. На скриншоте ниже показана одна проваленная задача.
С помощью следующей команды вы можете удалить неудачную задачу, указав ID Session в конце команды:
PS C:>remove-psjob 9
9: Вставка временных рамок для вывода команд PowerShell
Для задач PowerShell можно ввести временную метку последовательности, чтобы определить продолжительность каждого шага, к тому же можно использовать для настройки журнала вводимых скриптов. Это может оказаться удобным способом для их тестирования. Чтобы вставить метку времени, введите одну из следующих команд в виде одно строки в файле .ps1 :
Команды Вывод «$(Get-Date -format g) Start logging» 20/4/2020 7:45 AM «$(Get-Date -format F) Start logging» Friday, December 23, 2019 8:26:24 AM “$(Get-Date -format o) Start logging» 2019-11-17T19:26:24.0479860-06:00 Существует много различных форматов команды Get-Date , но обычно эти три параметра подходят для большинства целей с временными метками.
10: Вывод результатов с задержкой
В PowerShell некоторые команды выводят информацию на экран быстрым прокручиванием. Если вы не экспортируете данные в файл, будет невозможно просмотреть их на экране. Давайте ещё раз воспользуемся командлетом Get-ChildItem из предыдущих примеров. Эта команда может выводить множество результатов в зависимости от указанного пути. Для упрощения просмотра выводимых данных на экране мы воспользуемся функцией, которая называется EasyView . Данная функция позволяет нам просмотреть результаты на экране путем отображения одной строки каждые полсекунды. Функция EasyView создаётся следующим образом:
function EasyView < process < $_; Start-Sleep -seconds .5>>
Чтобы выполнить команду PowerShell с помощью функции EasyView добавьте в конце команды оператор | и название самой функции, как показано ниже:
Get-ChildItem N:Data | EasyView
Функция EasyView настроена на отображение строк с интервалом в полсекунды. Вы также можете настроить интервал в миллисекундах.
Итоги
Крутые особенности на этих 10 пунктах не заканчиваются. Есть множества функций PowerShell, которые могут упростить ваши ежедневные задачи. Я надеюсь эта статья привлечёт ваше внимание к командной строке и поможет вам в будущем использовать PowerShell.
Как выполнить SQL запрос к MSSQL Server из PowerShell?
23.03.2020
insci
PowerShell, SQL Server
комментария 2
В этой статье мы рассмотрим все рабочие способы подключения к 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
Пример 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]
Пример 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. У этих классов схожий принцип работы:
- Создается объект соединения с MSSQL сервером;
- Создается объект с SQL запросом, и ему присваивается объект соединения;
- Затем в случае выполнения SELECT запроса создается объект адаптера и в контексте этого адаптера выполняется запрос;
- В случае выполнения 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
>
Для insert/update/delete запрос выполняем ExecuteNonQuery:
$db = $serverInstance.Databases[‘test’]
$db.ExecuteNonQuery(«insert into test_table (test_col) Values (‘Test555’)»)Заметка. SMO библиотеки также можно установить через пакетный менеджер NuGet.
- Скачайте nuget.exe https://www.nuget.org/downloads;
- Запустите PowerShell с правами администратора и перейдите в директорию с файлом nugget.exe;
- Выполните: .\nuget.exe Install Microsoft.SqlServer.SqlManagementObjects ;
- В той же директории где лежит nuget.exe появится папка Microsoft.SqlServer.SqlManagementObjects со всеми нужными DLL;
- Загрузите библиотеку 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
Командлет Invoke-Sqlcmd более простой и интуитивный в использовании чем другие способы подключения к SQL Server. Invoke-Sqlcmd использует один и тот же синтаксис для SELECT и INSERT/UPDATE/DELETE запросов.
Пример SELECT запроса:
Invoke-Sqlcmd -ServerInstance «server\instance » -Query «sp_who»
Пример 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.
Предыдущая статья Следующая статья 