-i input_file[,input_file2...]Identifies the file that contains a batch of SQL statements or stored procedures. Multiple files may be specified that will be read and processed in order. Don't use any spaces between file names. sqlcmd will first check to see whether all the specified files exist. If one or more files don't exist, sqlcmd will exit. The -i and the -Q/-q options are mutually exclusive.
Error Reporting Options-bSpecifies that sqlcmd exits and returns a DOS ERRORLEVEL value when an error occurs. The value that is returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity level greater than 10; otherwise, the value returned is 0. If the -V option has been set in addition to -b, sqlcmd will not report an error if the severity level is lower than the values set using -V. Command prompt batch files can test the value of ERRORLEVEL and handle the error appropriately. sqlcmd does not report errors for severity level 10 (informational messages).
-XDisables commands that might compromise system security when sqlcmd is executed from a batch file. The disabled commands are still recognized; sqlcmd issues a warning message and continues. If the optional parameter 1 is specified, sqlcmd generates an error message and then exits. The following commands are disabled when the -X option is used:
The file will be read and executed after a batch terminator is encountered. You can issue multiple :r commands. The file may include any sqlcmd command. This includes the batch terminator GO.
If an input file that is located on a remote server is called from sqlcmd on a local computer and the file contains a drive file path such as :Out c:\OutputFile.txt. The output file is created on the local computer and not on the remote server.
In this article, we will learn the notions and usage details of the SQL variable. In SQL Server, local variables are used to store data during the batch execution period. The local variables can be created for different data types and can also be assigned values. Additionally, variable assigned values can be changed during the execution period. The life cycle of the variable starts from the point where it is declared and has to end at the end of the batch. On the other hand, If a variable is being used in a stored procedure, the scope of the variable is limited to the current stored procedure. In the next sections, we will reinforce this theoretical information with various examples
For example, the following DECLARE statement creates three local variables named @LastName, @FirstName and @StateProvince, and initializes each to NULL:
Variables have local scope and are only visible within the batch or procedure where they are defined. In the following example, the nested scope created for execution of sp_executesql does not have access to the variable declared in the higher scope and returns and error.
To assign a variable a value by using the SET statement, include the variable name and the value to assign to the variable. This is the preferred method of assigning a value to a variable. The following batch, for example, declares two variables, assigns values to them, and then uses them in the WHERE clause of a SELECT statement:
Running a batch file is a simple matter of just clicking on it. Batch files can also be run in a command prompt or the Start-Run line. In such case, the full path name must be used unless the file's path is in the path environment. Following is a simple example of a Batch Script. This Batch Script when run deletes all files in the current directory.
Typically, to create a batch file, notepad is used. This is the simplest tool for creation of batch files. Next is the execution environment for the batch scripts. On Windows systems, this is done via the command prompt or cmd.exe. All batch files are run in this environment.
In order to run batch files from the command prompt, you either need to go to the location to where the batch file is stored or alternatively you can enter the file location in the path environment variable. Thus assuming that the batch file is stored in the location C:\Application\bin, you would need to follow these instructions for the PATH variable inclusion.
By default, a batch file will display its command as it runs. The purpose of this first command is to turn off this display. The command "echo off" turns off the display for the whole script, except for the "echo off" command itself. The "at" sign "@" in front makes the command apply to itself as well.
Very often batch files also contains lines that start with the "Rem" command. This is a way to enter comments and documentation. The computer ignores anything on a line following Rem. For batch files with increasing amount of complexity, this is often a good idea to have comments.
Batch scripts support the concept of command line arguments wherein arguments can be passed to the batch file when invoked. The arguments can be called from the batch files through the variables %1, %2, %3, and so on.
In any programming language, there is an option to mark variables as having some sort of scope, i.e. the section of code on which they can be accessed. Normally, variable having a global scope can be accessed anywhere from a program whereas local scoped variables have a defined boundary in which they can be accessed.
DOS scripting also has a definition for locally and globally scoped variables. By default, variables are global to your entire command prompt session. Call the SETLOCAL command to make variables local to the scope of your script. After calling SETLOCAL, any variable assignments revert upon calling ENDLOCAL, calling EXIT, or when execution reaches the end of file (EOF) in your script. The following example shows the difference when local and global variables are set in the script.
If you have variables that would be used across batch files, then it is always preferable to use environment variables. Once the environment variable is defined, it can be accessed via the % sign. The following example shows how to see the JAVA_HOME defined on a system. The JAVA_HOME variable is a key component that is normally used by a wide variety of applications.
One common practice in batch files is sending the output of a program to a log file. The > operator sends, or redirects, stdout or stderr to another file. The following example shows how this can be done.
Environment variable %ERRORLEVEL% contains the latest errorlevel in the batch file, which is the latest error codes from the last command executed. In the batch file, it is always a good practice to use environment variables instead of constant values, since the same variable get expanded to different values on different computers.
Windows driver developers and testers can use DevCon to verify that a driver is installed and configured correctly, including the proper INF files, driver stack, driver files, and driver package. You can also use the DevCon commands (enable, disable, install, start, stop, and continue) in scripts to test the driver. DevCon is a command-line tool that performs device management functions on local computers and remote computers.
The following command uses the DevCon status operation to find the status of all devices on the local computer. It then saves the status in the status.txt file for logging or later review. The command uses the wildcard character (*) to represent all devices and the redirection character (>) to redirect the output to the status.txt file.
The following command uses the DevCon Install operation to install a keyboard device on the local computer. The command includes the full path to the INF file for the device (keyboard.inf) and a hardware ID (*PNP030b).
Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement. Cursor variables can be declared with this statement and used with other cursor-related statements. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration.
In the batch file, we can create variable two types, one is using the set commands and another is for the parameters which can be passed when the batch file is called. First I will describe how we can create a variable using the set command.
Local variables have a defined boundary in which only they can be accessed. In the batch script, we can create a local variable using the SETLOCAL command. The scope of the local variable only between the SETLOCAL and ENDLOCAL command and it is destroyed as soon as the ENDLOCAL statement is executed.
The batch file can read the command-line argument with a special syntax. If you want to read the command line arguments you need to write % with command line argument position. Suppose if you want to read the 1st argument of the command line, you need to write %1 in the batch file.
Connecting to the default instance on the local computer by using Windows Authentication, executing a query, directing the output to a file, and having sqlcmd exit after the query has finished running:
You can use sqlcmd to execute database script files. Script files are text files that contain a mix of Transact-SQL statements, sqlcmd commands, and scripting variables. For more information about how to script variables, see Use sqlcmd with Scripting Variables. sqlcmd works with the statements, commands, and scripting variables in a script file in a manner similar to how it works with statements and commands that are entered interactively. The main difference is that sqlcmd reads through the input file without pause instead of waiting for a user to enter the statements, commands, and scripting variables. 2b1af7f3a8