Thursday, September 6, 2012


"A key to addressing the data heterogeneity issue is the adoption and enforcement of standards across information systems. While XML is clearly emerging to be the lingua franca for databases and internet systems, it is encouraging to see the enforcement of standards even at the level of Microsoft® Word and Microsoft® Powerpoint® documents"
-- National Aeronautics and Space Administration (NASA)

XML is so wonderful that I created this blog to tell you about it. You don't need to be a Rocket Scientist to understand how powerful XML is but apparently it doesn't hurt. Recently I Googled "NASA Business Intelligence" and found an article posted on titled, The NASA Program Management Tool: A New Vision in Business Intelligence. So, what technology sits at the core of Business Intelligence (BI) at NASA? An elite team of Data Scientists from MIT, Berkeley, Cornell and Stanford determined that only XML would do. Of course. Below is a link to the article but let me share a few highlights:

This paper describes a novel approach to business intelligence and program management for large technology enterprises like the U.S. National Aeronautics and Space Administration (NASA). Two key distinctions of the approach are that 1) standard business documents are the user interface, and 2) a “schema-less” XML database enables flexible integration of technology information for use by both humans and machines in a highly dynamic environment. The implementation utilizes patent-pending NASA software called the NASA Program Management Tool (PMT) and its underlying “schema-less” XML database called Netmark.

...a 'schema-less' XML database enables integration and query-based document composition, eliminating the need for database administration by automating the integration of information that have diverse schemas.

...Netmark supports the XML standard for metadata and information interchange thus making it an open system that is interoperable with a wide variety of other information systems and tools.

It is worth noting that NASA uses the Xalan XSLT processor to transform their XML. Nice.


Additional Reading:

Wednesday, September 5, 2012


Wouldn't it be nice to have the ability to be able to pass more parameters to SP_WHO or SP_WHO2? Well I just wrote SP_WHO3 which allows you to pass the following parameters: @DBName, @SPID, @Status, @Login, @hostName, @Command, @CPUtime_floor, @DiskIO_floor, @ProgramName and @precision_bit.

Furthermore, you can set @precision_bit=0 to get a close match (see comments in the code). Enjoy!



(      @exact_flag          bit=1,

       @DBName              varchar(100)='*All*',

       @SPID                int=-1,

       @Status              varchar(40)='*All*',

       @Login               varchar(50)='*All*',

       @hostName            varchar(100)='*All*',

       @Command             varchar(200)='*All*',

       @CPUtime_floor       bigint=-1,

       @DiskIO_floor        bigint=-1,

       @ProgramName         varchar(100)='*All*'




 Author:      Alan Burstein

 Created on:  9/5/2012

 Updated on:  4/19/2013



 Returns the same columns as SP_WHO2 but you can pass parameters

 and filter the result set.


 With @exact_flag=1 (default) you only get records where there

 is an EXACT (=) match. For example, when @exact_flag=1 and

 @Command='TASK' nothing will be returned. If exact_match_flag=0

 and @Command='TASK' then records where Command="TASK MANAGER",

 Command="BRKR TASK", etc will be returned.





 -- Create table to hold the results of EXEC SP_WHO2


 (     SPID          int,

       [Status]      varchar(40),

       [Login]       varchar(50),

       HostName      varchar(100),

       BlkBy         varchar(4),

       DBName        varchar(100),

       Command       varchar(200),

       CPUtime       bigint,

       DiskIO        bigint,

       LastBatch     varchar(100),

       ProgramName   varchar(100),

       SPID2         int,

       REQUESTID     int);




 -- output

 IF @exact_flag=1


  SELECT * FROM @sp_who3

  WHERE (DBName=@DBName OR @DBName='*All*') AND

    ([Status]=@Status OR @Status='*All*') AND

    (SPID=@SPID OR @SPID=(-1)) AND

    ([Login]=@Login OR @Login='*All*') AND

    (HostName=@hostName OR @hostName='*All*') AND

    (Command=@Command OR @Command='*All*') AND

    (CPUtime>=@CPUtime_floor OR @CPUtime_floor=(-1)) AND

    (DiskIO>=@DiskIO_floor OR @DiskIO_floor=(-1)) AND

    (ProgramName=@ProgramName OR @ProgramName='*All*')





  SELECT @DBName='%'+CAST(@DBName as varchar(100))+'%',

    @Status='%'+CAST(@Status as varchar(40))+'%',

    @Login='%'+CAST(@Login AS varchar(50))+'%',

    @hostName='%'+CAST(@hostName AS varchar(100))+'%',

    @Command='%'+CAST(@Command AS varchar(200))+'%',

    @ProgramName='%'+CAST(@ProgramName AS varchar(100))+'%'


  SELECT * FROM @sp_who3

  WHERE (DBName LIKE @DBName OR @DBName='%*All*%') AND

    ([Status] LIKE @Status OR @Status='%*All*%') AND

    (SPID=@SPID OR @SPID=(-1)) AND

    ([Login] LIKE @Login OR @Login='%*All*%') AND

    (HostName LIKE @hostName OR @hostName='%*All*%') AND

    (Command=@Command OR @Command='%*All*%') AND

    (CPUtime>=@CPUtime_floor OR @CPUtime_floor=(-1)) AND

    (DiskIO>=@DiskIO_floor OR @DiskIO_floor=(-1)) AND

    (ProgramName LIKE @ProgramName OR @ProgramName='%*All*%')





--SP_WHO3 @exact_flag=0, @ProgramName='repl', @CPUtime_floor=10000




Before posting this I googled SP_WHO3. It turns out I was not the first to come up with this idea. Bummer. My SP_WHO3 is still great.


The new xmlsqlninja logo ('X-M-L'-'See-Quel' 'ninja')

Last Updated: 4/19/2013 (code cleanup)