Использование служебной программы 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. Ввод текста ^C вызывает выход из sqlcmd. ^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 команды. Мигающий знак подчеркивания и запрос sqlcmd, после 1>, который обозначает место, где будут отображаться команды и операторы, которые вы вводите. Теперь введите USE AdventureWorks2012 и нажмите клавишу ВВОД, а затем введите GO и нажмите клавишу ВВОД. Содержимое окна командной строки будет следующим:

sqlcmd

USE AdventureWorks2012;

GO

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

Changed database context to 'AdventureWorks2012'.

1> _

Нажатие клавиши ВВОД после ввода USE AdventureWorks2012 сигнализировало sqlcmd о начале новой строки. После ввода GO, нажмите ВВОД, чтобы передать USE AdventureWorks2012 инструкцию экземпляру SQL Server через сигнал sqlcmd. 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 для выполнения файлов скриптов базы данных. Файлы скриптов — это текстовые файлы, содержащие сочетание инструкций sqlcmd , команд и переменных скриптов Transact-SQL. Дополнительные сведения об использовании переменных скрипта см. в разделе Использование программы sqlcmd с переменными скрипта. sqlcmd работает с инструкциями, командами и переменными скрипта в файле скрипта таким образом, как он работает с операторами и командами, введенными в интерактивном режиме. Основное различие заключается в том, что sqlcmd считывает входной файл без приостановки, а не ожидает ввода инструкций, команд и переменных скриптов.

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

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

Примеры

А. Выполнение скрипта с помощью sqlcmd

Запустите Блокнот и наберите следующие выражения Transact-SQL:

USE AdventureWorks2012;

GO

SELECT TOP (3) BusinessEntityID, FirstName, LastName

FROM Person.Person;

GO

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

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 используется для подключения к серверу с блокировкой с помощью выделенного подключения администратора (DAC).

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,

F. Использование 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

G. Использование sqlcmd для задания шифрования в базе данных SQL Azure

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

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

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

SET ANSI_DEFAULTS
SET ANSI_NULLS
УСТАНОВИТЬ REMOTE_PROC_TRANSACTIONS
SET ANSI_NULL_DEFAULT

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

SET CONCAT_NULL_YIELDS_NULL
Настройка ANSI_PADDING
SET QUERY_GOVERNOR_COST_LIMIT

Синтаксис

В следующих примерах приведены случаи, когда параметры поставщика собственных клиентов SQL Server включают: ForceProtocolEncryption = FalseTrust Server Certificate = No

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

SQLCMD -E -N

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

SQLCMD -E -C