Использование программы sqlcmd

Статья
2017-06-13

Служебная sqlcmd программа командной строки для нерегламентированного интерактивного выполнения инструкций и скриптов Transact-SQL, а также для автоматизации задач скриптов Transact-SQL. Чтобы использовать sqlcmd в интерактивном режиме или создавать файлы скриптов для запуска с помощью sqlcmd, пользователи должны понимать Transact-SQL. Программа sqlcmd обычно применяется следующим образом.

Пользователи в интерактивном режиме вводит инструкции Transact-SQL так же, как и в командной строке. Результаты выводятся в окно командной строки. Чтобы открыть окно командной строки, в меню Пускпоследовательно выберите команды Все программы, Стандартныеи Командная строка. В окне командной строки введите sqlcmd и необходимые параметры. Полный список параметров, поддерживаемых sqlcmd, см. в разделе Программа sqlcmd.
Пользователи передают sqlcmd задание либо путем указания одной инструкции Transact-SQL для выполнения, либо путем указания служебной программы на текстовый файл, содержащий выполняемые инструкции Transact-SQL. Вывод обычно перенаправляется в текстовый файл, но может также быть отображен в окне командной строки.
Режим SQLCMD в редакторе запросов среды SQL Server Management Studio.
Управляющие объекты SQL Server (SMO)
Задания CmdExec агента SQL Server.

Часто используемые параметры sqlcmd

Чаще всего используются следующие параметры.

Параметр сервера (-S), определяющий экземпляр Microsoft SQL Server, к которому sqlcmd подключается.
Параметры проверки подлинности (-E, -U и -P), указывающие учетные данные, которые sqlcmd используются для подключения к экземпляру SQL Server.

Примечание

Параметр -E используется по умолчанию, и нет необходимости его указывать.
Параметры ввода (-Q, -q и -i), определяющие расположение входных данных в sqlcmd.
Выходной параметр (-o), указывающий файл, в который sqlcmd будут помещены выходные данные.

Соединение с программой sqlcmd

Ниже перечислены наиболее частые способы использования служебной программы sqlcmd.

Подключение к экземпляру по умолчанию с использованием проверки подлинности Windows для выполнения инструкций Transact-SQL в интерактивном режиме:
```
sqlcmd -S <ComputerName>  
```
Примечание

В предыдущем примере параметр -E не указан, так как он используется по умолчанию и sqlcmd подключается к экземпляру по умолчанию с помощью проверки подлинности Windows.
Подключение к именованному экземпляру с использованием проверки подлинности Windows для выполнения инструкций Transact-SQL в интерактивном режиме:
```
sqlcmd -S <ComputerName>\<InstanceName>  
```
или диспетчер конфигурации служб
```
sqlcmd -S .\<InstanceName>  
```
Соединение с именованным экземпляром с использованием проверки подлинности Windows и указанием входного и выходного файла:
```
sqlcmd -S <ComputerName>\<InstanceName> -i <MyScript.sql> -o <MyOutput.rpt>  
```
Соединение с экземпляром по умолчанию на локальном компьютере с использованием проверки подлинности Windows, выполнение запроса и продолжение выполнения программы sqlcmd после завершения запроса:
```
sqlcmd -q "SELECT * FROM AdventureWorks2012.Person.Person"  
```
Соединение с экземпляром по умолчанию на локальном компьютере с использованием проверки подлинности Windows, выполнение запроса, запись в файл выходных данных и выход из программы sqlcmd после завершения запроса:
```
sqlcmd -Q "SELECT * FROM AdventureWorks2012.Person.Person" -o MyOutput.txt  
```
Подключение к именованным экземплярам с помощью проверки подлинности SQL Server для интерактивного выполнения инструкций Transact-SQL с sqlcmd запросом пароля:
```
sqlcmd -U MyLogin -S <ComputerName>\<InstanceName>  
```
Примечание

Для просмотра полного перечня параметров, поддерживаемых служебной программой sqlcmd, введите sqlcmd -?.

Интерактивный запуск инструкций Transact-SQL с помощью программы sqlcmd

Служебную sqlcmd программу можно использовать в интерактивном режиме для выполнения инструкций Transact-SQL в окне командной строки. Для интерактивного выполнения инструкций Transact-SQL с помощью sqlcmdзапустите служебную программу без использования параметров -Q, -q, -Z или -i , чтобы указать какие-либо входные файлы или запросы. Например:

sqlcmd -S <ComputerName>\<InstanceName>

Когда команда выполняется без входных файлов или запросов, sqlcmd подключается к указанному экземпляру SQL Server, а затем отображает новую строку с 1> мигающими символами подчеркивания с именем командной sqlcmd строки. Означает 1 , что это первая строка инструкции Transact-SQL, а sqlcmd запрос — это точка, с которой инструкция Transact-SQL будет начинаться при ее вводе.

В командной строке sqlcmd можно ввести как инструкции Transact-SQL, так и sqlcmd команды, такие как GO и EXIT. Каждая инструкция Transact-SQL помещается в буфер, называемый кэш инструкций. Эти инструкции отправляются в SQL Server после ввода команды и нажатия GO клавиши ВВОД. Чтобы выйти из sqlcmd, введите EXIT или QUIT в начале новой строки.

Чтобы очистить кэш инструкций, введите :RESET. При вводе ^Csqlcmd приводит к выходу. Кроме того, с помощью команды ^C можно останавливать выполнение кэша инструкций после запуска команды GO.

Инструкции Transact-SQL, введенные в интерактивном сеансе, можно изменить с помощью команды :ED и командной sqlcmd строки. Откроется редактор и после изменения инструкции Transact-SQL и закрытия редактора измененная инструкция Transact-SQL отобразится в окне командной строки. Введите GO , чтобы выполнить инструкцию Transact-SQL.

Строки в кавычках

Символы, заключенные в кавычки, используются без какой-либо дополнительной предварительной обработки, за исключением кавычек, которые вставляются в строку путем ввода двух последовательных кавычек. SQL Server рассматривает такую последовательность символов как одни кавычки. однако на сервере выполняется преобразование. Переменные скрипта при появлении в строке не раскрываются.

Пример:

sqlcmd

PRINT "Length: 5"" 7'";

GO

Результирующий набор:

Length: 5" 7'

Многострочные символьные строки

Программа sqlcmd поддерживает скрипты, в которых одна символьная строка занимает несколько строк. Например, следующая инструкция SELECT занимает несколько строк, но является одной символьной строкой, которая выполняется после ввода команды GOи нажатия клавиши ВВОД.

SELECT First line

FROM Second line

WHERE Third line;

GO

Пример интерактивной команды sqlcmd

Ниже приведен пример интерактивного выполнения программы sqlcmd.

При открытии окна командной строки там отображается только одна строка:

C:\> _

Это означает, что текущей папкой является C:\ , и если задать имя файла, то ОС Windows будет искать его в этой папке.

Введите sqlcmd , чтобы подключиться к экземпляру SQL Server по умолчанию на локальном компьютере, и содержимое окна командной строки будет выглядеть следующим образом:

C:\>sqlcmd

1> _

Это означает, что вы подключились к экземпляру SQL Server и что программа sqlcmd готова обрабатывать инструкции Transact-SQL и команды sqlcmd. Мерцающий знак подчеркивания после 1> является приглашением sqlcmd , отмечающим местоположение вводимых инструкций и команд. Теперь введите USE AdventureWorks2012 и нажмите клавишу ВВОД, а затем введите GO и нажмите клавишу ВВОД. Содержимое окна командной строки будет выглядеть следующим образом.

sqlcmd

USE AdventureWorks2012;

GO

Результирующий набор:

Changed database context to 'AdventureWorks2012'.

1> _

После ввода команды USE AdventureWorks2012 и нажатия клавиши ВВОД программа sqlcmd получила команду начать новую строку. Нажатие клавиши ENTER после ввода GO, дает сигнал программе sqlcmd к отправке инструкции USE AdventureWorks2012 экземпляру SQL Server. sqlcmd возвращает сообщение, указывающее, что инструкция USE успешно завершена, и отображает новое приглашение 1> , которое служит сигналом готовности к вводу новой

В следующем примере показано содержимое окна командной строки после ввода инструкции SELECT , команды GO для выполнения SELECTи команды EXIT для завершения программы sqlcmd:

sqlcmd

USE AdventureWorks2012;

GO

SELECT TOP (3) BusinessEntityID, FirstName, LastName

FROM Person.Person;

GO

Результирующий набор:

BusinessEntityID FirstName LastName

----------- -------------------------------- -----------

1 Syed Abbas

2 Catherine Abel

3 Kim Abercrombie

(3 rows affected)

1> EXIT

C:\>

Строки после 3> GO — это выходные данные инструкции SELECT . Когда выходные данные сформированы, программа sqlcmd сбрасывает приглашение sqlcmd и отображает 1>. После ввода команды EXIT в строке 1>командная строка приобретает первоначальный вид. Это означает завершение сеанса sqlcmd . Теперь можно закрыть окно командной строки. Для этого введите еще одну команду EXIT .

Выполнение файлов скрипта Transact-SQL с использованием программы sqlcmd

Можно использовать команду sqlcmd для выполнения файлов скриптов базы данных. Файлы скриптов — это текстовые файлы, содержащие сочетание инструкций Transact-SQL, sqlcmd команд и переменных скрипта. Дополнительные сведения об использовании переменных скрипта см. в разделе Использование программы sqlcmd с переменными скрипта. Программа sqlcmd работает с инструкциями, командами и переменными скрипта, помещенными в файл скрипта, подобно тому как она работает с инструкциями и командами, вводимыми в интерактивном режиме. Главное отличие заключается в том, что программа sqlcmd без остановок считывает входной файл, а не ждет, пока пользователь введет инструкции, команды или переменные скрипта.

Существуют различные способы создания файлов скрипта базы данных:

Можно в интерактивном режиме создать и отладить набор инструкций Transact-SQL в среде SQL Server Management Studio и затем сохранить содержимое окна запроса в файл скрипта.
Можно создать текстовый файл с инструкциями Transact-SQL, используя текстовый редактор, например Блокнот.

Примеры

A. Запуск скрипта с помощью программы sqlcmd

Откройте Блокнот и введите следующие инструкции Transact-SQL:

USE AdventureWorks2012;

GO

SELECT TOP (3) BusinessEntityID, FirstName, LastName

FROM Person.Person;

GO

Создайте папку с именем MyFolder и сохраните скрипт в виде файла MyScript.sql в папке C:\MyFolder. Введите следующий текст в командной строке, чтобы запустить скрипт, и поместите выходные данные MyOutput.txt в папку MyFolder:

sqlcmd -i C:\MyFolder\MyScript.sql -o C:\MyFolder\MyOutput.txt

Файл MyOutput.txt , просматриваемый в «Блокноте», содержит следующие сведения:

Changed database context to 'AdventureWorks2012'.

BusinessEntityID FirstName LastName

---------------- ----------- -----------

1 Syed Abbas

2 Catherine Abel

3 Kim Abercrombie

(3 rows affected)

Б. Использование программы sqlcmd с выделенным административным соединением

В следующем примере программа sqlcmd используется для подключения к серверу, на котором возникла проблема с блокировкой, с помощью выделенного административного соединения.

C:\>sqlcmd -S ServerName -A

1> SELECT blocked FROM sys.dm_exec_requests WHERE blocked <> 0;

2> GO

Результирующий набор:

spid blocked

------ -------

62 64

(1 rows affected)

С помощью sqlcmd завершите блокирующий процесс.

1> KILL 64;

2> GO

В. Использование программы sqlcmd для выполнения хранимых процедур

В следующем примере представлено действие, выполняющее хранимую процедуру с помощью sqlcmd. Создайте следующую хранимую процедуру.

USE AdventureWorks2012;

IF OBJECT_ID ( ' dbo.ContactEmailAddress, 'P' ) IS NOT NULL

DROP PROCEDURE dbo.ContactEmailAddress;

GO

CREATE PROCEDURE dbo.ContactEmailAddress

(

@FirstName nvarchar(50)

,@LastName nvarchar(50)

)

AS

SET NOCOUNT ON

SELECT EmailAddress

FROM Person.Person

WHERE FirstName = @FirstName

AND LastName = @LastName;

SET NOCOUNT OFF

После приглашения sqlcmd введите следующее:

C:\sqlcmd

1> :Setvar FirstName Gustavo

1> :Setvar LastName Achong

1> EXEC dbo.ContactEmailAddress $(Gustavo),$(Achong)

2> GO

EmailAddress

-----------------------------

gustavo0@adventure-works.com

Г. Использование программы sqlcmd для обслуживания базы данных

В следующем примере кода показано, как использовать программу sqlcmd для задач обслуживания базы данных. Создайте C:\BackupTemplate.sql со следующим кодом.

USE master;

BACKUP DATABASE [$(db)] TO DISK='$(bakfile)';

После приглашения sqlcmd введите следующее:

C:\ >sqlcmd

1> :connect <server>

Sqlcmd: Successfully connected to server <server>.

1> :setvar db msdb

1> :setvar bakfile c:\msdb.bak

1> :r c:\BackupTemplate.sql

2> GO

Changed database context to 'master'.

Processed 688 pages for database 'msdb', file 'MSDBData' on file 2.

Processed 5 pages for database 'msdb', file 'MSDBLog' on file 2.

BACKUP DATABASE successfully processed 693 pages in 0.725 seconds (7.830 MB/sec)

Д. Использование программы sqlcmd для выполнения кода на нескольких экземплярах

Следующий код представляет собой скрипт для соединения двух экземпляров. Обратите внимание на команду GO перед подключением ко второму экземпляру.

:CONNECT <server>\,<instance1>

EXEC dbo.SomeProcedure

GO

:CONNECT <server>\,<instance2>

EXEC dbo.SomeProcedure

GO

Д. Возврат выходных XML-данных

Следующий пример показывает, как выходные данные XML возвращаются неформатированными, в виде непрерывного потока.

C:\>sqlcmd -d AdventureWorks2012

1> :XML ON

1> SELECT TOP 3 FirstName + ' ' + LastName + ', '

2> FROM Person.Person

3> GO

Syed Abbas, Catherine Abel, Kim Abercrombie,

Е. Использование программы sqlcmd в файлах скриптов Windows

Команда sqlcmd, например , sqlcmd -i C:\InputFile.txt -o C:\OutputFile.txt, может выполняться в .bat файле вместе с VBScript. В этом случае интерактивные параметры не используются. Программа sqlcmd должна быть установлена на компьютере, на котором выполняется файл BAT.

Сначала создайте следующие четыре файла.

C:\badscript.sql

SELECT batch_1_this_is_an_error  
GO  
SELECT 'batch #2'  
GO

C:\goodscript.sql

SELECT 'batch #1'  
GO  
SELECT 'batch #2'  
GO

C:\returnvalue.sql

:exit(select 100)  
@echo off  
C:\windowsscript.bat  
@echo off  

echo Running badscript.sql  
sqlcmd -i badscript.sql -b -o out.log  
if not errorlevel 1 goto next1  
echo == An error occurred   

:next1  

echo Running goodscript.sql  
sqlcmd -i goodscript.sql -b -o out.log  
if not errorlevel 1 goto next2  
echo == An error occurred   

:next2  
echo Running returnvalue.sql  
sqlcmd -i returnvalue.sql -o out.log  
echo SQLCMD returned %errorlevel% to the command shell  

:exit

C:\windowsscript.bat

@echo off  

echo Running badscript.sql  
sqlcmd -i badscript.sql -b -o out.log  
if not errorlevel 1 goto next1  
echo == An error occurred   

:next1  

echo Running goodscript.sql  
sqlcmd -i goodscript.sql -b -o out.log  
if not errorlevel 1 goto next2  
echo == An error occurred   

:next2  
echo Running returnvalue.sql  
sqlcmd -i returnvalue.sql -o out.log  
echo SQLCMD returned %errorlevel% to the command shell  

:exit

Затем из командной строки запустите C:\windowsscript.bat:

C:\>windowsscript.bat

Running badscript.sql

== An error occurred

Running goodscript.sql

Running returnvalue.sql

SQLCMD returned 100 to the command shell

Ж. Использование программы sqlcmd для включения шифрования в Базе данных SQL Azure

Можно sqlcmdвыполнить в подключении для База данных SQL данных, чтобы указать шифрование и доверие к сертификату. Доступны два параметра sqlcmd:

Ключ -N используется клиентом для запроса зашифрованного соединения. Этот параметр аналогичен параметру ADO.net ENCRYPT = true.
C помощью переключателя -C клиент настраивает неявное доверие к сертификату сервера без проверки. Этот параметр аналогичен параметру ADO.net TRUSTSERVERCERTIFICATE = true.

Служба База данных SQL не поддерживает все параметры SET , которые доступны в экземпляре SQL Server. Следующие параметры вызывают ошибку, если соответствующий параметр SET имеет значение ON или OFF:

SET ANSI_DEFAULTS
SET ANSI_NULLS
SET REMOTE_PROC_TRANSACTIONS
SET ANSI_NULL_DEFAULT

Следующие параметры SET не вызывают исключений, но использовать их нельзя. Они являются устаревшими.

SET CONCAT_NULL_YIELDS_NULL
SET ANSI_PADDING
SET QUERY_GOVERNOR_COST_LIMIT

Синтаксис

Следующий пример относится к случаям, когда поставщик для собственного клиента SQL Server имеет следующие параметры: ForceProtocolEncryption = False, Trust Server Certificate = No

Подключение с использованием учетных данных Windows и шифрование соединения:

SQLCMD -E -N

Подключение с использованием учетных данных Windows и доверие сертификату сервера:

SQLCMD -E -C