ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

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

Updated on September 13, 2012
Source

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
Reconfigure

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

Exec sp_configure ‘xp_cmdshell’, 1
Reconfigure

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
Reconfigure

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'

or

exec xp_cmdshell 'DIR c: /A:D'

The output would be similar to this:

output

--------------------------------------------------------------

Volume in drive C has no label.

Volume Serial Number is 9CBD-D644

NULL

Directory of C:\WINDOWS\system32

NULL

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 & _
objSoftware.Version
Next

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.

output

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Microsoft (R) Windows Script Host Version 5.7

Copyright (C) Microsoft Corporation. All rights reserved.

NULL

Microsoft Office 2007 Primary Interop Assemblies

{50120000-1105-0000-0000-0000000FF1CE}

20100713000000.000000-000

5

Microsoft Office 2007 Primary Interop Assemblies

C:\WINDOWS\Installer\16272df9.msi

Microsoft Corporation

12.0.4518.1014

Microsoft Software Update for Web Folders (English) 12

{90120000-0010-0409-0000-0000000FF1CE}

20100428000000.000000-000

5

Microsoft Software Update for Web Folders (English) 12

C:\WINDOWS\Installer\64aca5.msi

Microsoft Corporation

12.0.6425.1000

Compatibility Pack for the 2007 Office system

{90120000-0020-0409-0000-0000000FF1CE}

20100502000000.000000-000

.....

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

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com 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: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    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 googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    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)
    Marketing
    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.
    Statistics
    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)