Call now: (800) 766-1884  


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA

 SQL Server Scripts
 Scripts Menu




  SQL Server Tips by Gama and Naughter

Testing the Code 

Since XP_RUNSCRIPT is a standard COM object, you should immediately register it after copying the DLL to the SQL Binn directory. This can be achieved using the “regsvr32” command line utility. To test the XP support, a SQL Script called “Test.sql” is located in a “Test” directory underneath the XP_RUNSCRIPT code directory. The initial parts of the script contain the usual code to register the three XP’s in the DLL with SQL Server. It then tests out calling the three XP’s using a sample JScript, VBScript and Visual Basic 6 COM object.


The COM object is implemented in the project “TestRunActiveX.vbp” also contained in the same “Test” directory. Before you run the script you may need to change the paths used for the script files. You should also register the pre-built “TestRunActiveX.dll” project using the standard RegSvr32 command line utility or alternatively build the TestRunActiveX project in Visual Basic 6. This sample COM object uses the ProgID “TestRunActiveX.Test1” and includes a single function called “Function1” to exercise some of the XP_RUNSCRIPT object model. To call this COM object from TSQL you would use the following code:


EXEC master..XP_RUN_ACTIVEX 'TestRunActiveX.Test1', 'Function1'


Another set of scripts are also located in the “Test” directory which provide a much more interesting example which prior to XP_RUNSCRIPT would have required quite a bit of custom C++ code to achieve. The two files are “TestEnumFiles.sql” and “EnumFiles.js”. This JScript file and the sample TSQL code to run it, enumerates a specified directory on the file system and returns the files contained in this directory as a recordset. Running this script on the root of my system partition returned the following recordset:



Size (Bytes)

Date Modified

Date Created

Date Accessed



2004-08-12 16:10:42.000

2002-11-26 22:12:54.000

2004-11-27 11:19:43.997



2004-11-27 11:17:15.000

2004-06-07 21:41:48.997

2004-11-27 11:17:15.000



2004-08-12 16:05:05.000

2002-08-28 20:08:53.997

2004-08-12 16:05:05.000



2004-08-12 16:05:05.000

2002-08-29 00:05:19.997

2004-08-12 16:05:05.000



2004-11-27 11:17:07.997

2002-11-26 22:08:44.997

2004-11-27 11:17:07.997


These are the standard hidden files you would expect to find on the root partition of a Windows machine.


The “EnumFiles.js” script demonstrates verifying the number and type of parameters passed to it, creating other objects via JScript’s “ActiveXObject” support, describing a recordset and returning all the rows in that recordset. The code for this script is as follows:


//ODS Defines we need for the script



var XPDT_SRVTEXT = 0x23;


var XPDT_SRVNTEXT = 0x63;


var XPDT_SRVINT4 = 0x38;



//Validate our parameter count

if (SQL.Parameters.Count != 3)


  SQL.SendInfoMsg("USAGE: XP_RUNSCRIPT_DISK JScript ThisScriptFilename DirectoryToEnumerate");




//Pull out the single parameter we need and verify its data type

var DirectoryParameter = SQL.Parameters(3);

if ((DirectoryParameter.Type != XPDT_SRVBIGVARCHAR) && (DirectoryParameter.Type != XPDT_SRVBIGCHAR) && (DirectoryParameter.Type != XPDT_SRVTEXT) &&

    (DirectoryParameter.Type != XPDT_SRVVARCHAR) && (DirectoryParameter.Type != XPDT_SRVNTEXT) && (DirectoryParameter.Type != XPDT_SRVNVARCHAR))


  SQL.SendInfoMsg("The third parameter to this XP which specifies the directory to enumerate must be a string data type");




//Describe the recordset we will return

var nullValue;

SQL.Describe("Name", XPDT_SRVTEXT, 255, nullValue);

SQL.Describe("Size (Bytes)", XPDT_SRVINT4, 0, 0);

SQL.Describe("Date Modified", XPDT_SRVDATETIME, 0, 2); SQL.Describe("Date Created", XPDT_SRVDATETIME, 0, 2);

SQL.Describe("Date Accessed", XPDT_SRVDATETIME, 0, 2);


//use the built in File System Object to enumerate the specified directory's files collection

var FSO = new ActiveXObject("Scripting.FileSystemObject");

var Folder = FSO.GetFolder(DirectoryParameter.Data);

var FileEnumerator = new Enumerator(Folder.files);

for (; !FileEnumerator.atEnd(); FileEnumerator.moveNext())


  //Pull out the current file

  var File = FileEnumerator.item();


  //Set the current row data

  SQL.SetColumnData(1, File.Name);

  SQL.SetColumnData(2, File.Size);

  SQL.SetColumnData(3, File.DateLastModified);

  SQL.SetColumnData(4, File.DateCreated);

  SQL.SetColumnData(5, File.DateLastAccessed);


  //Return the current row to SQL



The above book excerpt is from:

Super SQL Server Systems
Turbocharge Database Performance with C++ External Procedures

ISBN: 0-9761573-2-2
Joseph Gama, P. J. Naughter


Burleson Consulting Remote DB Administration







Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.

Burleson Consulting
SQL Server database support


Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter