Use sqlcmd in combination with script variables

Document directory

• A. Use the setvar command in the script
• B. interactive use of the setvar command
• C. Use the command prompt environment variable in sqlcmd
• D. Use user-level environment variables in sqlcmd
• E. Use the Startup Script
• F. variable extension

The variables used in the script are called script variables. Using script variables, a script can be applied to multiple scenarios. For example, if you need to run a single script on multiple servers, you can use the script variable to indicate the server name, without having to modify the script for each server. You can run the same script on different servers by changing the name of the server represented by the script variable.

AvailableSetvarYou can also useSqlcmd -VOptions implicitly define script variables.

This topic also containsSetAn example of defining environment variables at the cmd.exe command prompt.

Use the setvar command to set the script variable

SetvarCommand is used to define script variables. UseSetvarThe variables defined by the command are stored internally. Script variables and usage should not beSetThe environment variables defined in the command prompt are mixed. If the variables referenced by the script are not environment variables or are not usedSetvarThe error message is returned and the script execution is stopped. For more information, see-B.

Variable priority (from low to high)

If multiple variables have the same name, the variables with the highest priority are used.

1. System Environment Variables
2. User-level environment variables
3. StartSqlcmdThe command shell (SetX = y)
4. Sqlcmd -VX = y
5. : SetvarX Y

Note:
To view environment variables, go"Control Panel"Open"System"And then click"Advanced"Tab.

Implicitly set script variables

If you startSqlcmdRelatedSqlcmdVariable optionsSqlcmdThe variable is implicitly set to the value specified by this option. In the following example De> sqlcmd De> used De>-l De> option. This implicitly sets the sqllogintimeout variable.

De> C :\> sqlcmd-L 60 De>

You can also use-VSet the script variables in the script. In the following script (the file name is De> testscript. SQL De>, De> columnname De> is a script variable.

De> Use adventureworks; De>

De> select X. $ (columnname) De>

De> from person. Contact x De>

De> where c. contactid <5; De>

Then, you can use De>-V De> option specifies the name of the column to be returned:

De> sqlcmd-V columnname = "firstname"-I c: \ testscript. SQL De>

To use the same script to return other columns, change De> columnname De> the value of the script variable.

De> sqlcmd-V columnname = "lastname"-I c: \ testscript. SQL De>

Principles for script variable names and variable values

When naming script variables, consider the following principles:

• Variable names cannot contain space characters or quotation marks.
• The variable name cannot be used with the variable expression (for example$ (VAR)) Has the same form.
• Script variables are case insensitive.

  Note:
  IfSqlcmdIf any value is assigned to an environment variable, the variable is deleted. Use an exclusive value: Setvar varnameThis variable can be cleared.

When specifying a value for a script variable, consider the following principles:

• If the string value contains spaces, the quotation marks must be used.SetvarOr-VThe variable value defined by the option.
• If the quotation marks are part of the variable value, they must be escaped. Example :: De> setvar myvar "SPAC" "E" De>.
  

Principles of cmd.exe set variable values and variable names

Variables defined using set are part of the cmd.exe environment and are availableSqlcmdReference. Consider the following principles:

• Variable names cannot contain space characters or quotation marks.
• Variable values can contain spaces or quotation marks.

Sqlcmd script variable

BySqlcmdThe Defined variables are called script variables. The following table listsSqlcmdScript variable.

Variable	Related options	R/W	Default Value
Sql1_user *	-U	R	""
Sql1_password *	-P	--	""
Sqlbench server *	-S	R	"Defaultlocalinstance"
Sqlbench Workstation	-H	R	"Computername"
Sqlcmddbname	-D	R	""
Sqlcmdlogintimeout	-L	R/W	"8" (seconds)
Sql1_stattimeout	-T	R/W	"0" = waiting indefinitely
Sql1_headers	-H	R/W	"0"
Sqlbench colsep	-S	R/W	""
Sql1_colwidth	-W	R/W	"0"
Sqlcmdpacketsize	-	R	"4096"
Sqlcmderrorlevel	-M	R/W	"0"
Sqlcmdmaxvartypewidth	-Y	R/W	"256"
Sqlcmdmaxfixedtypewidth	-Y	R/W	"0" = Unlimited
Sqlcmdeditor		R/W	"Edit.com"
Sqlcmdini		R	""

* Sql1_user, sql1_password, and sql1_server are used.: Connect.

R indicates that only one value can be set during program initialization.

R/W indicates thatSetvarCommand to reset the value, and subsequent commands will use the new value.

Example
A. Use the setvar command in the script

ManySqlcmdYou can useSetvarCommand to control. In the following example, a script is created De> test. SQL De>, where De> sqlcmdlogintimeout De> set the variable De> 60 De> second, another script variable De> Server De> set De> testserver De>. Below is De> test. SQL De>.

De>: setvar sql1_logintimeout 60 De>

De>: setvar server "testserver" De>

De>: connect $ (server)-L $ (sql1_logintimeout) De>

De> Use adventureworks; De>

De> select firstname, lastname De>

De> from person. contact; De>

De> the script is then called by using sqlcmd: De>

De> sqlcmd-I c: \ test. SQL De>

B. interactive use of the setvar command

The following example shows how to use De> setvar De> interactive setting of script variables with commands.

De> sqlcmd De>

De>: setvar mydatabase adventureworks De>

De> use $ (mydatabase ); De>

De> go De>

The following is the result set:

De> changed database context to 'adventureworks' De>

De> 1> De>

C. Use the command prompt environment variable in sqlcmd

In the following example, four environment variables are set. De> De> De> sqlcmd De>.

De> C: \> set tablename = person. Contact De>

De> C: \> set col1 = firstname De>

De> C: \> set col2 = lastname De>

De> C: \> set Title = Ms. De>

De> C :\> sqlcmd-D adventureworks De>

De> 1> select top 5 $ (col1) + ''+ $ (col2) as name De>

De> 2> from $ (tablename) De>

De> 3> where title = '$ (title )' De>

De> 4> go De>

D. Use user-level environment variables in sqlcmd

In the following example, a user-level environment variable is set at the command prompt. De> % Temp % De>, and pass it De> sqlcmd De> input file. To obtain user-level environment variables, go "Control Panel"Double-click "System". Click "Advanced"Tab, and then click "Environment variable".

The following are the input files ( De> C: \ testscript.txt Code in de>:

De>: out $ (mytempdirectory) De>

De> Use adventureworks; De>

De> select firstname De>

De> from adventureworks. Person. Contact De>

De> where contactid <5; De>

The following code is entered at the command prompt:

De> C :\> set mytempdirectory = % Temp % \ output.txt De>

De> C :\> sqlcmd-I c: \ testscript.txt De>

The following are the results sent to the output file (C: \ Documents and Settings \ <user> \ Local Settings \ temp \ output.txt.

De> changed database context to 'adventureworks '. De>

De> firstname De>

De> -------------------------------------------------- De>

De> Gustavo De>

De> Catherine De>

De> Kim De>

De> Humberto De>

De> (4 rows affected) De>

E. Use the Startup Script

SqlcmdThe STARTUP script isSqlcmdThe script executed at startup. The following example sets the environment variable De> sqlcmdini De>. Below is De> init. SQL. De> content

De> set nocount on De>

De> go De>

De> declare @ nt_username nvarchar (128) De>

De> set @ nt_username = (select rtrim (convert (nvarchar (128), nt_username )) De>

De> from SYS. dm_exec_sessions where spid = @ spid) De>

De> select @ nt_username + 'is connected to' + De>

De> rtrim (convert (nvarchar (20), serverproperty ('servername') + De>

De> '(' + De>

De> rtrim (convert (nvarchar (20), serverproperty ('productversion') + De>

De> ')' De>

De>: setvar sql1_maxfixedtypewidth 100 De>

De> set nocount off De>

De> go De>

De>: setvar sql1_maxfixedtypewidth De>

This will be in De> sqlcmd De> called at startup De> init. SQL De> file.

De> C :\> set sqliniini = c: \ init. SQL De>

De> 1 sqlcmd De>

This is the output.

De> 1 <user> is connected to <Server> (9.00.2047.00) De>

Note:-XThe START Script Function is disabled.

F. variable extension

The following example shows howSqlcmdProcessing data in the form of variables.

De> Use adventureworks; De>

De> Create Table adventureworks. DBO. variabletest De>

De> ( De>

De> col1 nvarchar (50) De>

De> ); De>

De> go De>

In De> DBO. variabletest De> (including value De> $ (tablename) De>) De> col1 Insert a row in de>.

De> insert into adventureworks. DBO. variabletest (col1) De>

De> values ('$ (tablename )'); De>

De> go De>

In De> sqlcmd De> at the prompt, if no variable is set De> $ (tablename) De>, the following statement is used to return the row.

De> C :\> sqlcmd De>

De> 1 select col1 from DBO. variabletest where col1 = '$ (tablename )'; De>

De> 2 go De>

De> 3 select col1 from DBO. variabletest where col1 = n' $ (tablename )'; De>

De> 4 go De>

The following is the result set:

De> 1 col1 De>

De> 2 ------------------ De>

De> 3 $ (tablename) De>

De> 4 De>

De> 5 (1 rows affected) De>

Assume that De> myvar De> set De> $ (tablename) De>.

De> 6: setvar myvar $ (tablename) De>

These statements return this row and the message "The 'tablename' script variable is not defined" is returned ".

De> 6 select col1 from DBO. variabletest where col1 = '$ (tablename )'; De>

De> 7 Go De>

De> 1 select col1 from DBO. variabletest where col1 = n' $ (tablename )'; De>

De> 2 go De>

These statements return this row.

De> 1 select col1 from DBO. variabletest where col1 = '$ (myvar )'; De>

De> 2 go De>

De> 1 select col1 from DBO. variabletest where col1 = n' $ (myvar )'; De>

De> 2 go De>

Reference from Microsoft msdn: http://msdn.microsoft.com/zh-cn/library/ms188714.aspx