(c) 2012 Kevin Languedoc (klanguedoc)
SQL Server’s Transact-SQL provides a function to execute SQL shell scripts directly from SQL. This function is called SQL Server xp_cmdshell. The function works the same way as a prompt command.
This tutorial will walk you through the process of configuring the SQL Server to allow SQL to execute SQL shell scripts and SQL prompt commands directly from SQL. In addition, the returned results can be stored in a table and can be combined with other SQL script functions and commands like any other SQL script.
How To Execute SQL Prompt Commands
Before you can execute the xp_cmdshell function in SQL Server, you will need to enable it on the SQL Server. To enable the xp_cmdshell you will need to execute the sp_Configure SQL system command while providing the proper parameters. The general syntax for the sp_Configure command is:
To execute the sp_Configure command to enable the xp_cmdshell, open a new query in Sql Server Management Studio and enter the following command to enable the xp_cmdshell followed by the Reconfigure statement to install the new configuration:
You will need to run the xp_cmdshell using the credential that has access to the Windows server processes, like an administrator, otherwise the store procedure will not run or will issue an error.
The sp_Configure creates a new SQL Server configuration and displays the results in the SQL output. The first option is the name of the stored procedure that must be enabled on the SQL Server. The second option either enables or disables the stored procedure on the server. To enable, pass the value of '1' as a char value. To load the new configure, execute the Reconfigure SQL command.
This command alters server settings for all databases on that particular SQL Server. To alter database level settings, use the Alter Database command instead.
If you get the following message: “The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.” it is because the Advanced Options aren’t configured and you will to configure these first. To do so, issue the Advanced Options Command followed by the xp_cmdshell command as follows:
General xp_cmdshell Syntax
xp_cmdshell can return an error code of either success or failure. To capture this code to be used for further query processing such as a condition to either exit the query or to continue, define a integer variable such as:
xp_cmdshell Return Codes
If you don’t want any output to the SSMS query screen, just append the NO_OUTPUT directive to the end of the command as the following code snippet demonstrates:
The xp_cmdshell stored procedure runs with the same credentials as the SQL Server services account. However these credentials may not be sufficient to access the far reaches of the network and individual computers or file resources on local or network accounts. To override this constraint, you can use the variant stored function, sp_xp_cmdshell_proxy_account, which can be used to provide a valid Windows Administrator account and password with the proper accesses. This function can be executed prior to xp_cmdshell to create the proxy account settings. To create a proxy account execute the function as follows:
Command Runs Synchronously
Like any SQL script or query, the xp_cmdshell runs synchronously. Meaning that the other query statements, processes or yourself cannot interact with the query while it is running. Of course you can stop the execution if the stored procedure is running in SSMS (SQL Server Management Studio) using the stop command in the toolbar. Additionally, you can use the output as any other SELECT statement and the output can be stored in tables and variables.
For each question, choose the best answer. The answer key is below.
- What is the proper syntax to execute commands with xp_cmdshell
- xp_cmshell dir *.*
- exec xp_cmdshell dir *.*
- exec xp_cmdshell 'dir *.*'
- xp_cmshell dir *.*
Store Returned Results in Tables
Like any other SELECT output, the returned result from the xp_cmdshell can be stored in temporary tables, table variables or physical tables in a SQL database. Here are the general syntax of the three types of tables and some code snippets to illustrate.
In the following temporary table example, the xp_cmdshell executes the Net Config Server DOS Network command. This command returns information on the configuration of the current server. The other options would be to gather information on a workstation if the query was running on a workstation (a computer running on a network).
To use a table variable is very similar to the previous table example, except for the syntax of course. A table variable is only created during the the execution of the query and it is dropped once the query is complete.
To create a table variable for the xp_cmdshell output, first declare the table variable and any columns that are required as the following example demonstrates:
Naturally for this query to work, the java program would have to output the results using the System.out.println(output); statement. The example above is only a fictitious java app, but it demonstrates the syntax and the strength of the xp_cmdshell function. Virtually any executable that can be launched from the command line can also be executed from the xp_cmdshell function.
Of course Windows Applications mustn’t present an UI (user interface) since these scripts run on the server, away from prying eyes, so you cannot, say launch Microsoft Excel, unless it be for a background processing job like to refresh its contains from a web service or database without having to present an UI to the user.
The following screenshot demonstrates how to use a DOS NET command to query the server where the SQL Server is installed to return information on its configuration.
Another form of queries that can be executed using the xp_cmdshell is to store the returned output to a physical table in a database that resides on the servers HDD. As before the table needs to be created beforehand. You cannot do a direct INSERT INTO from another table. So here is the syntax and example
SQL queries aren't case sensitive, you can mix and match UPPER and lower or ProperCase and it all means the same thing to the compiler.
The following query will extract information on the machine's memory and store the information in a physical table. Notice the output is divided into several columns for display but is stored in one physical column. To store each piece of information in its own table column would require extra query processing.
Running Windows Processes
Virtually any Microsoft Windows process can be run with the xp_cmdshell function if you have the right credentials. For best results, it is best to run processes with no user interface or that can run minimize or hidden.
I have found it very useful to run Microsoft WMI (Windows Machine Instrumentation) scripts from the command line (CLI). The WMI can query every aspect of a local machine or any other machine on a local area network or wide area network. WMI is used to obtain information on every aspect of Windows based machines and to be able to act upon that information.
WMI is a great API for doing audits on machines on the network which then can be stored in tables and used for reporting purposes, like knowing how many Microsoft Word licenses the company has versus the number of copies installed in the computers.
Here are some examples of running WMI queries from the xp_cmdshell SQL function using the wmic.exe WMI Windows process.
VMware Accelerated AMD PCNet Adapter
RAS Async Adapter
WAN Miniport (L2TP)
WAN Miniport (PPTP)
WAN Miniport (PPPOE)
WAN Miniport (IP)
Capture information of disk drives
3 1/2 Inch Floppy Drive
Local Fixed Disk
Local Fixed Disk
xp_cmdshell is a very powerful tool in Microsoft BI - SQL Server Tooling.
Bhuwan Maitra on November 07, 2019:
I want to know more about command shell any references for me.
Kevin Languedoc (author) from Canada on December 15, 2012:
Wow thanks Perspycacious
Demas W Jasper from Today's America and The World Beyond on December 15, 2012:
This was a tour de force of a fine effort. I don't wonder that you have over 100,000 views! You are providing quality, useful materials.
Kevin Languedoc (author) from Canada on September 22, 2012:
Thanks Kaili, I appreciate it.
Kaili Bisson from Canada on September 22, 2012:
Excellent Hub Kevin. It is easy to follow too. Voted up and more and pinned.