How-To Execute CLI Commands from T-SQL

Updated on June 12, 2020
SQL Shell Commands
SQL Shell Commands | Source

(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:

sp_Configure OptionName, ConfigValue

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:

Exec sp_configure 'xp_cmdshell', 1


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:

EXEC sp_configure 'show advanced options', 1;




EXEC sp_configure 'xp_cmdshell',1





General xp_cmdshell Syntax

return codes
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:

DECLARE @returnCode int

Then assign the variable to the xp_cmdshell function as follows

EXEC @returnCode = xp_cmdshell âSC Start SomeWindowsServiceâ

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:

EXEC @returnCode = xp_cmdshell 'SC Stop SomeWindowsService', NO_OUTPUT

Security Considerations

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:

EXEC sp_xp_cmdshell_proxy_account 'WINDOWS_DOMAIN\username','password'

To remove the proxy account, execute the same function using the NULL keyword like this:

EXEC sp_xp_cmdshell_proxy_account NULL 'WINDOWS_DOMAIN\username','password'

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.

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.

Temporary Table

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).

Temporary Table

--Create the table
create table #tmpTable(outputText varchar(3000))

insert the the current server configuration into the #tmpTable by issuing the Net Config DOS command and passing it the Server parameter. The results will be inserted the outputText column. You don't need to specify the columns in the insert or select (in this case the EXEC) if the source and target match.

insert into #tmpTable
exec xp_cmdshell 'NET CONFIG Server'

--To view the results in the #tmpTable, perform a simple select
select * from #cmdTable

--Always drop (delete) the table after use, to free memory.
drop table #cmdTable
table #cmdTable

Do you plan using the xp_cmdshell stored procedure in the future

See results

Variable Tables

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:

Variable Tables

--Create the table variable
DECLARE @servercfg TABLE(serverdetails VARCHAR(3000))

--Populate the table variable using an INSERT
INSERT INTO @servercfg
	EXEC xp_cmdshell 'c:\java\java.exe -jar javaprogram.jar'

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.

Storing xp_cmdshell output in a table variable
Storing xp_cmdshell output in a table variable | Source

Physical Tables

CREATE TABLE cmdtable(cmd_output varchar(4000))
INSERT INTO cmdtable
	exec xp_cmdshell 'wmic MEMLOGICAL get /all'

SELECT * FROM dbo.cmdtable

Physical Table

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.

BIOS memory output using Microsoft WMI and xp_cmdshell
BIOS memory output using Microsoft WMI and xp_cmdshell | Source

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.

WMI queries on the machines system for the NIC

exec xp_cmdshell 'wmic /namespace:\\root\cimv2 path Win32_NetworkAdapterConfiguration get Caption, DNSDomain, DNSHostName'
VMware Accelerated AMD PCNet Adapter
RAS Async Adapter
WAN Miniport (L2TP)
WAN Miniport (PPTP)
WAN Miniport (PPPOE)
Direct Parallel
WAN Miniport (IP)
Teefer2 Miniport
Teefer2 Miniport
(12 row(s)
Here is the output of the previous WMI query.

Capture information of disk drives

exec xp_cmdshell 'wmic /namespace:\\root\cimv2 path Win32_LogicalDisk get FileSystem, FreeSpace, Size, VolumeSerialNumber, VolumeName, caption, description'
3 1/2 Inch Floppy Drive
Local Fixed Disk
Local Fixed Disk
(7 row(s)
This WMI query will gather information on the physical drives on a machine where it is executed.

In Conclusion

xp_cmdshell is a very powerful tool in Microsoft BI - SQL Server Tooling.


    0 of 8192 characters used
    Post Comment
    • profile image

      Bhuwan Maitra 

      9 months ago

      I want to know more about command shell any references for me.

    • klanguedoc profile imageAUTHOR

      Kevin Languedoc 

      7 years ago from Canada

      Wow thanks Perspycacious

    • Perspycacious profile image

      Demas W Jasper 

      7 years ago from Today's America and The World Beyond

      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.

    • klanguedoc profile imageAUTHOR

      Kevin Languedoc 

      7 years ago from Canada

      Thanks Kaili, I appreciate it.

    • Kaili Bisson profile image

      Kaili Bisson 

      7 years ago from Canada

      Excellent Hub Kevin. It is easy to follow too. Voted up and more and pinned.


    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at:

    Show Details
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the or domains, for performance and efficiency reasons. (Privacy Policy)
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
    ClickscoThis is a data management platform studying reader behavior (Privacy Policy)