Sqlcmd mode что это
Изменение скриптов SQLCMD при помощи редактора запросов
Используя редактор запросов Компонент Database Engine в SQL Server Management Studio, можно писать и редактировать запросы в виде скриптов SQLCMD. Скрипты SQLCMD применяются в тех случаях, когда необходимо обработать системные команды Windows и инструкции Transact-SQL в одном и том же скрипте.
Режим SQLCMD
Система управления версиями
синтаксический анализ скриптов;
Включение режима скриптов SQLCMD в редакторе запросов
Включить режим скриптов SQLCMD для активного окна редактора запросов компонента Компонент Database Engine можно при помощи следующей процедуры.
Переключение окна редактора запросов компонента Database Engine в режим скриптов SQLCMD
В меню Запрос выберите команду Режим SQLCMD.
Редактор запросов выполняет инструкции sqlcmd в контексте редактора запросов.
На панели инструментов Редактора SQL в списке Доступные базы данных выберите пункт AdventureWorks2012.
Нажмите клавишу F5, чтобы выполнить весь раздел, составленный из инструкций Transact-SQL и MS-DOS.
Обратите внимание на две панели с результатами выполнения первой и третьей инструкций SQL.
(Обработано строк: 6)
(Обработано строк: 4)
При выполнении из командной строки служебная программа sqlcmd позволяет добиться полного взаимодействия с операционной системой. Используя редактор запросов в Режиме SQLCMD, будьте внимательны, чтобы не запустить интерактивные инструкции. Редактор запросов не может моментально ответить операционной системе.
Дополнительные сведения о выполнении инструкции SQLCMD см. в разделе Служебная программа sqlcmdили изучите учебник по SQLCMD.
Включение режима скриптов SQLCMD по умолчанию
Создание и изменение скриптов SQLCMD
команда SQLCMD должна быть первой инструкцией в строке;
в каждой строке разрешается только одна команда SQLCMD;
перед командами SQLCMD могут идти комментарии или пробелы;
команды SQLCMD внутри символов комментария не выполняются;
чтобы четко различать команды SQLCMD и Transact-SQL, ко всем командам SQLCMD необходимо добавлять префикс в виде двоеточия ( : );
SQL Server Management Studio использует Microsoft.NET FrameworkSqlClient для выполнения в обычном режиме и в режиме SQLCMD. При вызове из командной строки SQLCMD использует поставщика OLE DB. Так как могут применяться различные параметры по умолчанию, выполнение одного и того же запроса в среде SQL Server Management Studio в режиме SQLCMD и в программе SQLCMD может проходить по-разному.
Поддерживаемый синтаксис SQLCMD
Редактор запросов компонента Компонент Database Engine поддерживает следующие ключевые слова сценариев SQLCMD:
:connect server[\instance] [-l login_timeout] [-U user [-P password]]
:on error [ignore|exit]
Команды SQLCMD, не перечисленные выше, редактором запросов не поддерживаются. Если выполняется скрипт, содержащий неподдерживаемые ключевые слова SQLCMD, для каждого неподдерживаемого ключевого слова редактор запросов отправляет в целевой объект сообщение «Команда не учитывается». Скрипт будет выполнен успешно, но неподдерживаемые команды не будут учитываться.
Так как команды SQLCMD запускаются не из командной строки, при запуске редактора запросов в режиме SQLCMD действуют некоторые ограничения. Нельзя передавать параметры командной строки, например переменные. Кроме того, поскольку редактор запросов не поддерживает возможности реагировать на приглашения операционной системы, не следует выполнять интерактивные инструкции.
Выделение цветом в скриптах SQLCMD
В режиме скриптов SQLCMD программный код скрипта выделяется разными цветами. Выделение цветом ключевых слов Transact-SQL остается таким же. Команды SQLCMD представлены с затененным фоном.
Использование программы sqlcmd
Пользователь вводит инструкции на языке Transact-SQL точно так же, как при работе в командной строке. Результаты выводятся в окно командной строки. Чтобы открыть окно командной строки, введите «cmd» в поле поиска Windows и выберите пункт Командная строка. В окне командной строки введите sqlcmd и необходимые параметры. Полный перечень параметров, поддерживаемых программой sqlcmd, см. в разделе Программа sqlcmd.
Режим 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
Соединение с экземпляром по умолчанию с использованием проверки подлинности Windows для запуска инструкций Transact-SQL в интерактивном режиме:
ПРИМЕЧАНИЕ. В предыдущем примере параметр -E не указывается, так как он используется по умолчанию, и программа sqlcmd подключается к экземпляру по умолчанию, используя проверку подлинности Windows.
Соединение с именованным экземпляром с использованием проверки подлинности Windows для запуска инструкций Transact-SQL в интерактивном режиме:
или диспетчер конфигурации служб
Соединение с именованным экземпляром с использованием проверки подлинности Windows и указанием входного и выходного файла:
Соединение с экземпляром по умолчанию на локальном компьютере с использованием проверки подлинности Windows, выполнение запроса и продолжение выполнения программы sqlcmd после завершения запроса:
Соединение с экземпляром по умолчанию на локальном компьютере с использованием проверки подлинности Windows, выполнение запроса, запись в файл выходных данных и выход из программы sqlcmd после завершения запроса:
Соединение с именованным экземпляром с использованием проверки подлинности SQL Server для выполнения инструкций языка Transact-SQL в интерактивном режиме, при этом программа sqlcmd ожидает ввода пароля:
Интерактивный запуск инструкций Transact-SQL с помощью программы sqlcmd
Строки в кавычках
Символы, заключенные в кавычки, используются без какой-либо дополнительной предварительной обработки, за исключением кавычек, которые вставляются в строку путем ввода двух последовательных кавычек. SQL Server рассматривает такую последовательность символов как одни кавычки. однако на сервере выполняется преобразование. Переменные скрипта при появлении в строке не раскрываются.
Многострочные символьные строки
Программа sqlcmd поддерживает скрипты, в которых одна строка занимает несколько строк. Например, следующая инструкция SELECT занимает несколько строк, но является одной символьной строкой, которая выполняется после ввода команды GO и нажатия клавиши ВВОД.
Пример интерактивной команды sqlcmd
При открытии окна командной строки там отображается только одна строка:
Changed database context to ‘AdventureWorks2012’.
SELECT TOP (3) BusinessEntityID, FirstName, LastName
BusinessEntityID FirstName LastName
Выполнение файлов скрипта Transact-SQL с использованием программы sqlcmd
Существуют различные способы создания файлов скрипта базы данных:
можно в интерактивном режиме создать и отладить набор инструкций Transact-SQL в среде SQL Server Management Studio и сохранить содержимое окна запроса в файл скрипта;
Примеры
A. Запуск скрипта с помощью программы sqlcmd
Откройте «Блокнот» и введите следующие инструкции Transact-SQL :
SELECT TOP (3) BusinessEntityID, FirstName, LastName
Changed database context to ‘AdventureWorks2012’.
BusinessEntityID FirstName LastName
Б. Использование программы sqlcmd с выделенным административным соединением
В следующем примере программа sqlcmd используется для подключения к серверу, на котором возникла проблема с блокировкой, с помощью выделенного административного соединения.
1> SELECT session_id, blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;
С помощью sqlcmd завершите блокирующий процесс.
В. Использование программы sqlcmd для выполнения хранимых процедур
IF OBJECT_ID ( ‘ dbo.ContactEmailAddress, ‘P’ ) IS NOT NULL
DROP PROCEDURE dbo.ContactEmailAddress;
CREATE PROCEDURE dbo.ContactEmailAddress
WHERE FirstName = @FirstName
AND LastName = @LastName;
После приглашения sqlcmd введите следующее:
1> :Setvar FirstName Gustavo
1> :Setvar LastName Achong
Г. Использование программы sqlcmd для обслуживания базы данных
В следующем примере кода показано, как использовать программу sqlcmd для задач обслуживания базы данных. Создайте C:\BackupTemplate.sql со следующим кодом.
BACKUP DATABASE [$(db)] TO DISK=’$(bakfile)’;
После приглашения sqlcmd введите следующее:
1> :setvar bakfile c:\msdb.bak
1> :r c:\BackupTemplate.sql
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 перед подключением ко второму экземпляру.
Д. Возврат выходных XML-данных
Следующий пример показывает, как выходные данные XML возвращаются неформатированными, в виде непрерывного потока.
1> SELECT TOP 3 FirstName + ‘ ‘ + LastName + ‘, ‘
2> FROM Person.Person
Syed Abbas, Catherine Abel, Kim Abercrombie,
Е. Использование программы sqlcmd в файлах скриптов Windows
Сначала создайте следующие четыре файла.
Затем из командной строки запустите C:\windowsscript.bat :
SQLCMD returned 100 to the command shell
Ж. Использование программы sqlcmd для включения шифрования в Базе данных SQL Azure
Программа sqlcmd может работать на соединении с данными База данных SQL для определения шифрования и отношения доверия сертификата. Доступны два параметра sqlcmd:
Следующие параметры SET не вызывают исключений, но использовать их нельзя. Они являются устаревшими.
Синтаксис
Подключение с использованием учетных данных Windows и шифрование соединения:
Подключение с использованием учетных данных Windows и доверие сертификату сервера:
Подключение с использованием учетных данных Windows, шифрование соединения и доверие сертификату сервера:
Подключение с использованием учетных данных Windows, шифрование соединения и доверие сертификату сервера:
Подключение с использованием учетных данных Windows, шифрование соединения и доверие сертификату сервера:
Подключение с использованием учетных данных Windows, шифрование соединения и доверие сертификату сервера:
Подключение с использованием учетных данных Windows, шифрование соединения и доверие сертификату сервера:
In SQL Server Management Studio what is SQLCMD mode?
In SQL Server Management Studio I would like to know what is SQLCMD mode?
4 Answers 4
I did some more research, so here’s my understanding of this to extend what has been written so far:
What is SQLCMD
It is a simple scripting environment that allows automation of tasks related to SQL server. For example, you can write and execute a script that will login to a specific instance of SQL Server, execute scripts from a given directory on this connection and store the output in a specified file.
Invoke-Sqlcmd cmdlet was introduced with SQL Server 2008 as a mean to replace this tool with a standardized, Powershell-based approach, preserving most of the original syntax and functionality.
What is SQLCMD mode in SSMS
Executing scripts in SQLCMD mode allows to use commands typical to sqlcmd.exe environment. However, there’s no IntelliSense or debugging support for SQLCMD mode, so maintaining scripts that mix clean T-SQL with SQLCMD-specific code can be a pain. Therefore, it should be used only when it’s necessary.
Example use case
Let’s suppose that a company has a naming convention for databases that include environment in the name, eg: MyDb_Prod, MyDb_Test, MyDb_Dev. This convention might be used to minimize chance of mistakes.
When a developer writes a T-SQL script, it will have to be executed in different environments in deployment/testing process, which would require many versions of the code:
Instead, we can assume that database name will be provided as a SQLCMD variable in deployment process and have exactly the same file deployed to all environments:
(in this simple example database name could be omitted altogether, but if you have cross-database joins, using database name is necessary)
sqlcmd Utility
The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files through a variety of available modes:
The utility uses ODBC to execute Transact-SQL batches.
Download the latest version of sqlcmd Utility
Download Microsoft Command Line Utilities 15 for SQL Server (x64) (2.6 MB)
Download Microsoft Command Line Utilities 15 for SQL Server (x86) (2.3 MB)
The command line tools are General Availability (GA), however they are being released with the installer package for SQL Server 2019 (15.x).
Version Information
Release number: 15.0.2
Build number: 15.0.2000.5
Release date: September 11, 2020
The new version of SQLCMD supports Azure AD authentication, including Multi-Factor Authentication (MFA) support for SQL Database, Azure Synapse Analytics, and Always Encrypted features. The new BCP supports Azure AD authentication, including Multi-Factor Authentication (MFA) support for SQL Database and Azure Synapse Analytics.
You can try the sqlcmd utility from Azure Cloud Shell as it is pre-installed by default: 
To run sqlcmd statements in SSMS, select SQLCMD Mode from the top navigation Query Menu dropdown.
Currently, sqlcmd doesn’t require a space between the command-line option and the value. However, in a future release, a space may be required between the command-line option and the value.
Syntax
Command-line Options
Login-Related Options
-d db_name
Issues a USE db_name statement when you start sqlcmd. This option sets the sqlcmd scripting variable SQLCMDDBNAME. This parameter specifies the initial database. The default is your login’s default-database property. If the database does not exist, an error message is generated and sqlcmd exits.
-l login_timeout
Specifies the number of seconds before a sqlcmd login to the ODBC driver times out when you try to connect to a server. This option sets the sqlcmd scripting variable SQLCMDLOGINTIMEOUT. The default time-out for login to sqlcmd is eight seconds. When using the -G option to connect to SQL Database or Azure Synapse Analytics and authenticate using Azure Active Directory, a timeout value of at least 30 seconds is recommended. The login time-out must be a number between 0 and 65534. If the value supplied is not numeric or does not fall into that range, sqlcmd generates an error message. A value of 0 specifies time-out to be infinite.
-E
Uses a trusted connection instead of using a user name and password to sign in to SQL Server. By default, without -E specified, sqlcmd uses the trusted connection option.
The -E option ignores possible user name and password environment variable settings such as SQLCMDPASSWORD. If the -E option is used together with the -U option or the -P option, an error message is generated.
Azure Active Directory Username and Password:
When you want to use an Azure Active Directory user name and password, you can provide the -G option and also use the user name and password by providing the -U and -P options.
Azure Active Directory Integrated
For Azure Active Directory Integrated authentication, provide the -G option without a user name or password. AAD Integrated Authentication requires Microsoft ODBC Driver 17 for SQL Server version 17.6.1 or higher and a properly configured Kerberos environment..
This will generate the following connection string in the backend:
Azure Active Directory Interactive
The Azure AD Interactive authentication for Azure SQL Database and Azure Synapse Analytics, allows you to use an interactive method supporting multi-factor authentication. For more information, see Active Directory Interactive Authentication.
Azure AD interactive requires sqlcmd version 15.0.1000.34 or later as well as ODBC version 17.2 or later.
The following example exports data using Azure AD interactive mode indicating username where user represents an AAD account. This is the same example used in the previous section: Azure Active Directory Username and Password.
Interactive mode requires a password to be manually entered, or for accounts with multi-factor authentication enabled, complete your configured MFA authentication method.
The previous command generates the following connection string in the backend:
In case an Azure AD user is a domain federated user using a Windows account, the user name required in the command-line, contains its domain account (for example, joe@contoso.com see below):
If guest users exist in a specific Azure AD and are part of a group that exists in SQL Database that has database permissions to execute the sqlcmd command, their guest user alias is used (for example, keith0@adventureworks.com).
-H workstation_name
A workstation name. This option sets the sqlcmd scripting variable SQLCMDWORKSTATION. The workstation name is listed in the hostname column of the sys.sysprocesses catalog view and can be returned using the stored procedure sp_who. If this option is not specified, the default is the current computer name. This name can be used to identify different
sqlcmd sessions.
-j Prints raw error messages to the screen.
-K application_intent
Declares the application workload type when connecting to a server. The only currently supported value is ReadOnly. If -K is not specified, the sqlcmd utility will not support connectivity to a secondary replica in an Always On availability group. For more information, see Active Secondaries: Readable Secondary Replica (Always On Availability Groups)
-M multisubnet_failover
Always specify -M when connecting to the availability group listener of a SQL Server availability group or a SQL Server Failover Cluster Instance. -M provides for faster detection of and connection to the (currently) active server. If -M is not specified, -M is off. For more information about Listeners, Client Connectivity, Application Failover, Creation and Configuration of Availability Groups (SQL Server), Failover Clustering and Always On Availability Groups (SQL Server), and Active Secondaries: Readable Secondary Replicas(Always On Availability Groups).
-N
This switch is used by the client to request an encrypted connection.
We recommend that you use a strong password.
Use a strong password!
The password prompt is displayed by printing the password prompt to the console, as follows: Password:
User input is hidden. This means that nothing is displayed and the cursor stays in position.
The SQLCMDPASSWORD environment variable lets you set a default password for the current session. Therefore, passwords do not have to be hard-coded into batch files.
The following example first sets the SQLCMDPASSWORD variable at the command prompt and then accesses the sqlcmd utility. At the command prompt, type:
SET SQLCMDPASSWORD= p@a$$w0rd
At the following command prompt, type:
If the user name and password combination is incorrect, an error message is generated.
NOTE! The OSQLPASSWORD environment variable was kept for backward compatibility. The SQLCMDPASSWORD environment variable takes precedence over the OSQLPASSWORD environment variable. Now that OSQLPASSWORD is no longer shared, the utilities sqlcmd and osql can be used next to each other without interference. Old scripts will continue to work.
If the -P option is used with the -E option, an error message is generated.
If the -P option is followed by more than one argument, an error message is generated and the program exits.
-S [protocol:]server[\instance_name][,port]
Specifies the instance of SQL Server to which to connect. It sets the sqlcmd scripting variable SQLCMDSERVER.
Specify server_name to connect to the default instance of SQL Server on that server computer. Specify server_name [ \instance_name ] to connect to a named instance of SQL Server on that server computer. If no server computer is specified, sqlcmd connects to the default instance of SQL Server on the local computer. This option is required when you execute sqlcmd from a remote computer on the network.
protocol can be tcp (TCP/IP), lpc (shared memory), or np (named pipes).
If you do not specify a server_name [ \instance_name ] when you start sqlcmd, SQL Server checks for and uses the SQLCMDSERVER environment variable.
The OSQLSERVER environment variable has been kept for backward compatibility. The SQLCMDSERVER environment variable takes precedence over the OSQLSERVER environment variable; this means that sqlcmd and osql can be used next to each other without interference and that old scripts will continue to work.
-U login_id
Is the login name or contained database user name. For contained database users, you must provide the database name option (-d).
The OSQLUSER environment variable is available for backward compatibility. The SQLCMDUSER environment variable takes precedence over the OSQLUSER environment variable. This means that sqlcmd and osql can be used next to each other without interference. It also means that existing osql scripts will continue to work.
If neither the -U option or the -P option is specified, sqlcmd tries to connect by using Microsoft Windows Authentication mode. Authentication is based on the Windows account of the user who is running sqlcmd.
If the -U option is used with the -E option (described later in this article), an error message is generated. If the -U option is followed by more than one argument, an error message is generated and the program exits.
-z new_password
Change password:
-Z new_password
Change password and exit:
Input/Output Options
-f codepage | i:codepage[,o:codepage] | o:codepage[,i:codepage]
Specifies the input and output code pages. The codepage number is a numeric value that specifies an installed Windows code page.
Code-page conversion rules:
If no code pages are specified, sqlcmd will use the current code page for both input and output files, unless the input file is a Unicode file, in which case no conversion is required.
sqlcmd automatically recognizes both big-endian and little-endian Unicode input files. If the -u option has been specified, the output will always be little-endian Unicode.
If no output file is specified, the output code page will be the console code page. This approach enables the output to be displayed correctly on the console.
Multiple input files are assumed to be of the same code page. Unicode and non-Unicode input files can be mixed.
Enter chcp at the command prompt to verify the code page of Cmd.exe.
File paths that contain spaces must be enclosed in quotation marks.
This option may be used more than once: -iinput_file -II input_file.
-o output_file
Identifies the file that receives output from sqlcmd.
If -u is specified, the output_file is stored in Unicode format. If the file name is not valid, an error message is generated, and sqlcmd exits. sqlcmd does not support concurrent writing of multiple sqlcmd processes to the same file. The file output will be corrupted or incorrect. See the -f switch is also relevant to file formats. This file will be created if it does not exist. A file of the same name from a prior sqlcmd session will be overwritten. The file specified here is not the stdout file. If a stdout file is specified, this file will not be used.
File paths that contain spaces must be enclosed in quotation marks.
-R
Causes sqlcmd to localize numeric, currency, date, and time columns retrieved from SQL Server based on the client’s locale. By default, these columns are displayed using the server’s regional settings.
-u
Specifies that output_file is stored in Unicode format, regardless of the format of input_file.
Query Execution Options
-e
Writes input scripts to the standard output device (stdout).
-I
Sets the SET QUOTED_IDENTIFIER connection option to ON. By default, it is set to OFF. For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).
-q « cmdline query «
Executes a query when sqlcmd starts, but does not exit sqlcmd when the query has finished running. Multiple-semicolon-delimited queries can be executed. Use quotation marks around the query, as shown in the following example.
At the command prompt, type:
Do not use the GO terminator in the query.
If -b is specified together with this option, sqlcmd exits on error. -b is described later in this article.
-Q « cmdline query «
Executes a query when sqlcmd starts and then immediately exits sqlcmd. Multiple-semicolon-delimited queries can be executed.
Use quotation marks around the query, as shown in the following example.
At the command prompt, type:
Do not use the GO terminator in the query.
If -b is specified together with this option, sqlcmd exits on error. -b is described later in this article.
-t query_timeout
Specifies the number of seconds before a command (or SQL statement) times out. This option sets the sqlcmd scripting variable SQLCMDSTATTIMEOUT. If a time_out value is not specified, the command does not time out. The query time_out must be a number between 1 and 65534. If the value supplied is not numeric or does not fall into that range, sqlcmd generates an error message.
The actual time out value may vary from the specified time_out value by several seconds.
-v var = value[ var = value. ]
Creates a sqlcmdscripting variable that can be used in a sqlcmd script. Enclose the value in quotation marks if the value contains spaces. You can specify multiple var=«values« values. If there are errors in any of the values specified, sqlcmd generates an error message and then exits.
Formatting Options
-h headers
Specifies the number of rows to print between the column headings. The default is to print headings one time for each set of query results. This option sets the sqlcmd scripting variable SQLCMDHEADERS. Use -1 to specify that headers not be printed. Any value that is not valid causes sqlcmd to generate an error message and then exit.
-k [1 | 2]
Removes all control characters, such as tabs and new line characters from the output. This parameter preserves column formatting when data is returned. If 1 is specified, the control characters are replaced by a single space. If 2 is specified, consecutive control characters are replaced by a single space. -k is the same as -k1.
-s col_separator
Specifies the column-separator character. The default is a blank space. This option sets the sqlcmd scripting variable SQLCMDCOLSEP. To use characters that have special meaning to the operating system such as the ampersand (&), or semicolon (;), enclose the character in quotation marks («). The column separator can be any 8-bit character.
-w column_width
Specifies the screen width for output. This option sets the sqlcmd scripting variable SQLCMDCOLWIDTH. The column width must be a number greater than 8 and less than 65536. If the specified column width does not fall into that range, sqlcmd generates an error message. The default width is 80 characters. When an output line exceeds the specified column width, it wraps on to the next line.
-W
This option removes trailing spaces from a column. Use this option together with the -s option when preparing data that is to be exported to another application. Cannot be used with the -y or -Y options.
varchar(max)
nvarchar(max)
varbinary(max)
UDT (user-defined data types)