- Global And Local Variables In C++
- Batch File Local Variable Sqlcmd
- Batch In Sql
- Local Variables In C++
- Cmd Batch File Variables
I'm attempting to run a (Windows) batch file from within a SQL Server agent job under SQL Server 2008 R2. In the Job Steps, I've created a new step, set its type to 'Operating system (CmdExec)'. I initially tried to put just the batch file name in the 'Command' field. It gives an error that it can't find the file.
Then I tried: cmd.exe c:test.bat
Use environment variables; Store the output results of executed queries in a specified text file; sqlcmd gives us the functionality (and more) of the osql and isql command line utitlities that were present in SQL 2000 but have now been deprecated. It can also connect to previous versions of SQL Server such as SQL Server 2000.
This starts, but never completes (and it doesn't look like it's actually working anyway).
I tried wrapping the batch file name in quotes: cmd.exe 'C:test.bat' but that also doesn't make any difference.
What is the magical combination to get this to work?
dezso4 Answers
If memory serves me correctly:
Quotes are needed if there are any spaces in the path or file name
As well check the permissions used for the Job Agent Service. Could be that the user running the service does not have permission to see the file.
Good Luck
Check that you have permissions to run the batch file on that drive and folder location.
Don't forget when you run it manually your credentials are used but when it runs automatically by SQL Agent, the agent's credentials and permissions are used.
Max VernonNot the answer you're looking for? Browse other questions tagged sql-serversql-server-2008 or ask your own question.
- Batch Script Tutorial
- Batch Script Resources
- Selected Reading
There are two types of variables in batch files. One is for parameters which can be passed when the batch file is called and the other is done via the set command.
Global And Local Variables In C++
Command Line Arguments
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.
Batch File Local Variable Sqlcmd
The following example shows a batch file which accepts 3 command line arguments and echo's them to the command line screen.
If the above batch script is stored in a file called test.bat and we were to run the batch as
Following is a screenshot of how this would look in the command prompt when the batch file is executed.
The above command produces the following output.
If we were to run the batch as
The output would still remain the same as above. However, the fourth parameter would be ignored.
Set Command
The other way in which variables can be initialized is via the ‘set' command. Following is the syntax of the set command.
Syntax
where,
variable-name is the name of the variable you want to set.
value is the value which needs to be set against the variable.
/A – This switch is used if the value needs to be numeric in nature.
The following example shows a simple way the set command can be used.
Batch In Sql
Example
In the above code snippet, a variable called message is defined and set with the value of 'Hello World'.
To display the value of the variable, note that the variable needs to be enclosed in the % sign.
Output
The above command produces the following output.
Working with Numeric Values
In batch script, it is also possible to define a variable to hold a numeric value. This can be done by using the /A switch.
The following code shows a simple way in which numeric values can be set with the /A switch.
We are first setting the value of 2 variables, a and b to 5 and 10 respectively.
We are adding those values and storing in the variable c.
Finally, we are displaying the value of the variable c.
The output of the above program would be 15.
All of the arithmetic operators work in batch files. The following example shows arithmetic operators can be used in batch files.
The above command produces the following output.
Local vs Global Variables
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.
Local Variables In C++
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.
Example
Few key things to note about the above program.
The ‘globalvar' is defined with a global scope and is available throughout the entire script.
The ‘var‘ variable is defined in a local scope because it is enclosed between a ‘SETLOCAL' and ‘ENDLOCAL' block. Hence, this variable will be destroyed as soon the ‘ENDLOCAL' statement is executed.
Output
Cmd Batch File Variables
The above command produces the following output.
You will notice that the command echo %var% will not yield anything because after the ENDLOCAL statement, the ‘var' variable will no longer exist.
Working with Environment Variables
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.
The output would show the JAVA_HOME directory which would depend from system to system. Following is an example of an output.
