sql安装弹出sqlcmd_SQL Server中SQLCMD实用工具概述

sql安装弹出sqlcmd

This article is aimed at helping you understand the sqlcmd utility. Of course, this is a tool most of us have used it at several occasions given that the tool has been around for a decade now. However, to be a solid starting point, this article contains some examples that help you understand the tool from different viewpoints.

本文旨在帮助您了解sqlcmd实用程序。当然，考虑到该工具已经使用了十年，我们大多数人都多次使用它。但是，作为一个坚实的起点，本文包含一些示例，可以帮助您从不同的角度理解该工具。

This article discusses the following:

本文讨论以下内容：

How to enable SQLCMD
如何启用SQLCMD
A few important points to remember
需要记住的一些要点
SQLCMD with scripting variables in SSMS
在SSMS中具有脚本变量SQLCMD
Passing variables (or argument) as T-SQL and or a SQL Script file
将变量（或参数）作为T-SQL和/或SQL脚本文件传递
SQLCMD with scripting variables and Windows scripting in SSMS
在SSMS中具有脚本变量和Windows脚本SQLCMD

什么是SQLCMD？ (What is SQLCMD?)

sqlcmd is a command line utility that is part of the standard installation of SQL Server 2005 or higher, which enables interactive interface with any SQL Server instance to help perform the following tasks:

sqlcmd是一个命令行实用程序，是SQL Server 2005或更高版本的标准安装的一部分，它启用与任何SQL Server实例的交互界面以帮助执行以下任务：

Execute Transact-SQL (T-SQL) statements in SSMS
在SSMS中执行Transact-SQL（T-SQL）语句
Call a T-SQL script file
调用T-SQL脚本文件
Use of environment variables in command mode
在命令模式下使用环境变量
Store the output results of executed queries in a specified text file
将执行的查询的输出结果存储在指定的文本文件中

为什么选择SQLCMD？ (Why SQLCMD?)

sqlcmd is a simple, yet powerful scripting environment that helps with the automation of several tasks related to SQL Server. For example, you can write and execute a script that logs you into a specific instance of SQL Server, executes a script from a specified path, and redirects the output to a certain file.

sqlcmd是一个简单但功能强大的脚本环境，可帮助自动化与SQL Server相关的多个任务。例如，您可以编写和执行脚本，以将您登录到SQL Server的特定实例，从指定路径执行脚本，然后将输出重定向到特定文件。

Most of us SQL database administrators are already using PowerShell. The Invoke-SqlCmd cmdlet, from the sqlserver module that’s part of every SQL Server distribution since 2008, packs most of the capabilities of sqlcmd. Also, in SQL Server Management Studio, we have a SQLCMD mode, that simulates the capabilities of sqlcmd, and accepts some of the commands that are not part of the T-SQL language.

我们大多数SQL数据库管理员已经在使用PowerShell。自2008年以来，每个SQL Server发行版中都包含sqlserver模块的Invoke-SqlCmd cmdlet包含了sqlcmd的大多数功能。另外，在SQL Server Management Studio中，我们有一个SQLCMD模式，该模式模拟sqlcmd的功能，并接受不属于T-SQL语言的某些命令。

sqlcmd was an extension of the osql and isql command line utilities, that contains a rich set of parameters and options to enable automation of various administrative tasks.

sqlcmd是osql和isql命令行实用程序的扩展，其中包含一组丰富的参数和选项，可实现各种管理任务的自动化。

It’s simple to bind SQL in a .bat file.
将SQL绑定到.bat文件很简单。
It runs interactively across various OS platforms.
它可以跨各种OS平台交互运行。
It’s easy to pass command line arguments to a SQL file using sqlcmd.
使用sqlcmd将命令行参数传递给SQL文件很容易。

如何启用SQLCMD (How to enable SQLCMD)

OK, so sqlcmd is a great tool; of that, we’re all convinced. Let’s now talk about how the query editor in SSMS can be used to write statements as sqlcmd scripts. The following example talks about the integration of Windows system commands and T-SQL statements into a single script.

好的，因此sqlcmd是一个很好的工具；对此，我们都深信不疑。现在让我们讨论一下如何使用SSMS中的查询编辑器将语句编写为sqlcmd脚本。以下示例讨论将Windows系统命令和T-SQL语句集成到单个脚本中的情况。

It’s importatnt to remember that sqlcmd mode is not enabled by default. To enter into the sqlcmd mode in the SSMS query editor, follow the steps below:

请记住，默认情况下未启用sqlcmd模式，这一点很重要。要在SSMS查询编辑器中进入sqlcmd模式，请执行以下步骤：

OR
要么
Open a new Database Engine Query Editor window.
打开一个新的数据库引擎查询编辑器窗口。
SQLCMD Mode. SQLCMD模式 。

To enable SQLCMD scripting mode:

要启用SQLCMD脚本模式：

Tools menu, select 工具”菜单，选择“ Options. 选项” 。
Query Execution tab, and select 查询执行”选项卡，然后选择“ SQL Server, click the SQL Server” ，然后单击“ General page. 常规”页面。
By default open new queries in SQLCMD Mode. 默认情况下，以SQLCMD模式打开新查询 ”复选框。

Some important points to remember 要记住的一些重要点

SQLCMD commands must be prefixed with a colon. This makes the difference between SQLCMD commands and Transact-SQL clear.
SQLCMD命令必须以冒号作为前缀。这使SQLCMD命令和Transact-SQL之间的区别清晰可见。
same session context. 相同的会话上下文连接到其他服务器。
The variables that are used as part of the SQLCMD script are case-sensitive. For example, if we query the environment variables, we can see that COMPUTERNAME and computername are two different variables in the output. In the first, the environment variable is queried to return the name of the computer, where as in the second, SSMS isn’t able to resolve the variable and reports an error.
用作SQLCMD脚本一部分的变量区分大小写。例如，如果查询环境变量，我们可以看到COMPUTERNAME和computername是输出中的两个不同变量。在第一个环境变量中，查询环境变量以返回计算机的名称，在第二个环境中，SSMS无法解析该变量并报告错误。

:connect hqdbsp18
PRINT '$(COMPUTERNAME)'  
GO
:connect hqdbsp17
PRINT '$(computername)'

SQLCMD的示例 (Examples of SQLCMD)

The following example defines an integration of operating system commands and SQL statements together.

以下示例定义了操作系统命令和SQL语句的集成。

Variable are defined using SETVAR, connection is built using CONNECT the keyword, operating system commands are defined using !!, the output file is declared using the OUT keyword, and SQL Statements are placed to fetch the results based on the connection string.

使用SETVAR定义变量，使用关键字CONNECT建立连接，使用!!定义操作系统命令，使用OUT关键字声明输出文件，并放置SQL语句以基于连接字符串获取结果。

--variable declartion
:setvar subscriber1 HQMESRP01   
:setvar subscriber2 HQMESRP02
:setvar file1 c:\Data1.txt
:setvar file2 c:\Data2.txt--check for the file and delete if exists
!!if exist $(file1) del $(file1)
!!if exist $(file2) del $(file2)--connect to to the subscription 1  
:Connect $(subscriber1)
--redirect the SQL output to file
:out $(file1)
select * from [MES_REPL_Rpt_AP]..tb_F4801_woheader where wadoco=2520079
select * from [MES_REPL_Rpt_AP]..tb_F4801_woheader_MES where WorkOrderNumber=2520079
GO--connect to to the subscription 2  
:Connect $(subscriber2)--redirect the SQL output to file
:out $(file2)
select * from [MES_HIST_AP]..tb_F4801_woheader where wadoco=2520079
select * from [MES_HIST_AP]..tb_F4801_woheader_MES where WorkOrderNumber=2520079

SQLCMD with scripting variables, and Windows scripting in SSMS

具有脚本变量SQLCMD和SSMS中的Windows脚本

In this example, login is created across multiple servers. T-SQL, sqlcmd and Windows scripting are all integrated, and run using the SSMS interface to create logins.

在此示例中，登录是在多个服务器上创建的。 T-SQL，sqlcmd和Windows脚本均已集成，并使用SSMS界面运行以创建登录名。

Enable XP_CMDSHELL
启用XP_CMDSHELL
Define the T-SQL in a SQL file
在SQL文件中定义T-SQL
Build the dynamic SQL
建立动态SQL
Verify the output
验证输出

STEP 1: In this step, xp_cmdshell is enabled. It is not a recommended option, though. This has only been enabled for the purpose of the demo.

步骤1：在此步骤中，启用了xp_cmdshell。但是，不建议使用此选项。仅在演示中启用了此功能。

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'xp_cmdShell', 1;  
GO  
RECONFIGURE;  
GO

STEP 2: A SQL file, SysAdminLoginCreation.sql is created with the create login SQL statement.

步骤2：使用create login SQL语句创建一个SQL文件SysAdminLoginCreation.sql。

USE [master]
GO
CREATE LOGIN [SQLShackTest] WITH PASSWORD=N'SQLShackDemo123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

The file looks like below

该文件如下所示

We create a for loop to loop through each SQL instances.

我们创建一个for循环来遍历每个SQL实例。

Iterate through the input file.
遍历输入文件。
FOR Parameter %j defines the name of the SQL instance.
FOR参数％j定义SQL实例的名称。
Call sqlcmd using the parameters as part of the command.
使用参数作为命令的一部分来调用sqlcmd。
Repeat for each data item.
对每个数据项重复上述步骤。

MASTER..XP_CMDSHELL 'for /f %j in (\\networkshare\IsDba\server.txt) do sqlcmd -S %j -i \\networkshare \IsDba\SysAdminLoginCreation.sql -E'

sqlcmd executed successfully!

sqlcmd执行成功！

Let’s check whether the logins are created on the those servers

让我们检查登录名是否在这些服务器上创建

:connect hqdbsp18
select * from sys.syslogins where name='SQLShackTest'
GO
:connect hqdbsp17
select * from sys.syslogins where name='SQLShackTest'

The logins have also been created for the listed servers.

还为列出的服务器创建了登录名。

Passing variables (or argument) as T-SQL and SQL Script file

将变量（或参数）作为T-SQL和SQL脚本文件传递

We can pass the variables as an input to the T-SQL or the SQL script file in the sqlcmd command console. The scripting variables are declared using :SETVAR keyword. The corresponding parameter values passed to T-SQL or SQL script file. The values are enclosed in $(variable name) are fed to the SQLs during the run time. Using -v (version) switch, the parameters are declared and fed to the script. We can list any number of variables with no delimiter in between the parameters. Lets follow the example to understand the mechanism better:

我们可以将变量作为输入传递给T-SQL或sqlcmd命令控制台中SQL脚本文件。脚本变量使用：SETVAR关键字声明。相应的参数值传递给T-SQL或SQL脚本文件。 $（变量名）中包含的值在运行时被馈送到SQL。使用-v（版本）开关，可以声明参数并将其输入脚本。我们可以列出任意数量的变量，并且在参数之间没有定界符。让我们按照示例更好地了解该机制：

salesorderdetails is the name of table assigned to salesorderdetails是分配给tablename variable tablename变量的表的名称
The select statement is prepared along with the tablename variable
select语句与tablename变量一起准备

C:\Users\ccov648>sqlcmd -S hqdbt01\SQL2017 -E
1> use Python2017
2> :SETVAR tablename salesorderdetail
2> select top(10) * from $(tablename);
3> go

We can assume that database name will be provided as a SQLCMD variable during the deployment process; we can have exactly the same file deployed to all environments. The following example has two parameters, db and tablename. The input parameters are separated by a space in between.

我们可以假设数据库名称将在部署过程中作为SQLCMD变量提供；我们可以将完全相同的文件部署到所有环境。以下示例具有两个参数，即db和tablename。输入参数之间用空格隔开。

C:\Users\ccov648>sqlcmd -S hqdbt01\SQL2017 -E -i n:\IsDba\SalesOrderDetails.sql -v db=Python2017  tablename=salesorderdetail

摘要 (Summary)

SQLCMD mode in SSMS (SQL Server Management Studio) provides an interface to run SQL statements across multiple servers and across platforms. This is really helpful in many cases. In this article, we discussed the creation of logon across different SQL Server instance versions. Similarly, we performed data validation between the two subscribers. We also saw that, if there is a complex SQL query which requires multiple inputs, the values can be passed as parameters to the T-SQL or the SQL Script file.

SSMS（SQL Server Management Studio）中SQLCMD模式提供了一个界面，可在多个服务器和平台之间运行SQL语句。在许多情况下，这确实很有帮助。在本文中，我们讨论了跨不同SQL Server实例版本的登录的创建。同样，我们在两个订户之间执行了数据验证。我们还看到，如果有一个复杂SQL查询需要多个输入，则可以将这些值作为参数传递给T-SQL或SQL Script文件。

Having said all that, we need to remember that SQLCMD mode only allows the use of sqlcmd environment commands within SSMS, however, without the typical IntelliSense support, or support for debugging. Maintenance of scripts that mix pure T-SQL and SQLCMD can require effort. Use it wisely!

说了这么多，我们需要记住，SQLCMD模式仅允许在SSMS中使用sqlcmd环境命令，但是没有典型的IntelliSense支持或调试支持。混合纯T-SQL和SQLCMD的脚本的维护可能会很费力。明智地使用它！