Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 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:

 

Name

Size (Bytes)

Date Modified

Date Created

Date Accessed

Boot.ini

210

2004-08-12 16:10:42.000

2002-11-26 22:12:54.000

2004-11-27 11:19:43.997

Hiberfil.sys

536449024

2004-11-27 11:17:15.000

2004-06-07 21:41:48.997

2004-11-27 11:17:15.000

NTDETECT.COM

47564

2004-08-12 16:05:05.000

2002-08-28 20:08:53.997

2004-08-12 16:05:05.000

Ntldr

250032

2004-08-12 16:05:05.000

2002-08-29 00:05:19.997

2004-08-12 16:05:05.000

Pagefile.sys

1207959552

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_SRVBIGVARCHAR = 0xA7;

var XPDT_SRVBIGCHAR = 0xAF;

var XPDT_SRVTEXT = 0x23;

var XPDT_SRVVARCHAR = 0x27;

var XPDT_SRVNTEXT = 0x63;

var XPDT_SRVNVARCHAR = 0xe7;

var XPDT_SRVINT4 = 0x38;

var XPDT_SRVDATETIME = 0x3d;

 

//Validate our parameter count

if (SQL.Parameters.Count != 3)

{

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

  SQL.Quit(false);

}

 

//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");

  SQL.Quit(false);

}

 

//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

  SQL.SendRow();

}
 


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

 http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm
 

 

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