ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software

How-To Use VBScript with T-SQL (Transact-SQL) | xp_cmdshell

Updated on September 13, 2012

Microsoft T-SQL (Transact-SQL) has some amazing stored procedures. Besides being ANSI-SQL 92 compliant, Transact-SQL provides many specialized stored procedures to interface with other parts of the Microsoft platform. For instance the xp_cmdshell stored procedure query the Windows Operating System (NTFS).

In a nutshell the xp_cmdshell provides an interface with the OS Command Shell or DOS shell and allows the user the same possibilities as running commands from a shell window or a batch file. Imagine being able to query different parts of the file System or a remote computer’s configuration and storing this information in a table for reporting purposes. Of course in order to perform these operations, you need administrative access. You also need to enable the stored procedure and for a production environments your security needs to be tightly controlled so not to allow access to any undesirables.

Configure xp_cmdshell on Microsoft SQL Server

Using the xpcmdshell Stored Procedure requires enabling it on the SQL Server. You can accomplish this by using the sp_Configure Stored Procedure followed by the Reconfigure statement to install the new configuration. The general syntax is :

sp_Configure OptionName, ConfigValue

To enable the xpCmdShell Stored Procedure you would need to execute the stored procedure as follows:

Exec sp_configure ‘xp_cmdshell’, 1

If you get an error message that the advanced options aren't available, run this command first followed by the previous command:

Exec sp_configure 'Show Adavanced Options', 1

Once you have given access to the stored procedure you can run all kinds of queries into different parts of the Windows Server or a Computer on the network, File System , Active Directory. To demonstrate, I will run a query of the different file folders and stored them in a Microsoft T-SQL Temporary table


Folder List Example

To list the folders on a computer you enter the flowwing command

exec xp_cmdshell 'DIR /ad /b'


exec xp_cmdshell 'DIR c: /A:D'

The output would be similar to this:



Volume in drive C has no label.

Volume Serial Number is 9CBD-D644


Directory of C:\WINDOWS\system32


09/02/2010 08:24 PM <DIR> .

09/02/2010 08:24 PM <DIR> ..

02/20/2009 07:27 PM <DIR> 1025

02/20/2009 07:27 PM <DIR> 1028

02/20/2009 07:27 PM <DIR> 1031

06/23/2009 10:25 AM <DIR> 1033

02/20/2009 07:27 PM <DIR> 1037

02/20/2009 07:27 PM <DIR> 1041

02/20/2009 07:27 PM <DIR> 1042

02/20/2009 07:27 PM <DIR> 1054

02/20/2009 07:27 PM <DIR> 2052

02/20/2009 07:27 PM <DIR> 3076

02/20/2009 07:27 PM <DIR> 3com_dmi

05/08/2010 08:08 AM <DIR> appmgmt

02/21/2009 10:06 AM <DIR> bits

02/22/2009 08:13 PM <DIR> Cache

05/25/2010 08:11 AM <DIR> CatRoot

08/31/2010 10:23 AM <DIR> CatRoot2

02/21/2009 10:05 AM <DIR> Com


Storing Information in a Table

Taking this example one step further, we could store this information in a table for reporting purposes. If the table wasn't created, you would need to create first and then use the insert into command to fill the table.

Create Table FSInformation(varchar(8000))

insert into FSInformation
exec xp_cmdshell 'DIR /ad /b'

Copying Files

Copying files is just as easy. As previous stated you would write and execute a query similar to this one:

exec xp_cmdshell 'Copy c:\aFolder\fileA.txt c:\bFolder\bFile.txt' or

exec xp_cmdshell 'Copy c:\aFolder\fileA.txt c:\bFolder\aFile.txt'




More Advanced Example

Suppose you needed to get a list of all software installed on a computer. This wouldn't be possible or at least easy with a command line command but it would be with WMI (Windows Management Instrumentation) API and VBScript. The following example will show how to execute VBscript with xp_cmdshell.

First you will need to create a VBScript script file. I will call it SoftwareInventory.vbs and I will place it in the c:\\temp\\folder. It is important to note that the System or Proxy account must have access to the folder where the script is located.

To get a list of installed software the following would do the trick. This is rudementary script but it is only demonstration purposes.

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set resSoftware = objWMIService.ExecQuery _
("Select * from Win32_Product")

For Each objSoftware in resSoftware
Wscript.echo vbTab & objSoftware.Description & vbtab & _
objSoftware.IdentifyingNumber & vbtab & _
objSoftware.InstallDate2 & vbtab & _
objSoftware.InstallLocation & vbtab & _
objSoftware.InstallState & vbtab & _
objSoftware.Name & vbtab & _
objSoftware.PackageCache & vbtab & _
objSoftware.SKUNumber & vbtab & _
objSoftware.Vendor & vbtab & _

The command of course be as follows:

Exec xp_cmdshell 'cscript.exe c:\temp\InstalledSoftware.vbs'

and the output (abbreviated) would be something like this but would reflect the software on the target competer.



Microsoft (R) Windows Script Host Version 5.7

Copyright (C) Microsoft Corporation. All rights reserved.


Microsoft Office 2007 Primary Interop Assemblies




Microsoft Office 2007 Primary Interop Assemblies


Microsoft Corporation


Microsoft Software Update for Web Folders (English) 12




Microsoft Software Update for Web Folders (English) 12


Microsoft Corporation


Compatibility Pack for the 2007 Office system




Other examples

These are only a brief outline of what is possible with the xp_cmdshell stored procedure. More vbscript examples can be found here


    0 of 8192 characters used
    Post Comment

    No comments yet.