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

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

More by this Author


Comments

No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working