DataDemon

 

Installing

 

DataDemon does not require an installer and can be setup via “xcopy deployment,” so you can install it simply by extracting the zip file located in the DataDemon folder on the CD to a folder on your hard drive.   To start the application, run DataDemon.exe at the prompt.

 

Overview

 

The purpose of DataDemon is to bulk load osql/sqlcmd-formatted query output into a database.  For example, you can use it to import SQL 2000 Blocking Script or the SQL 2005 Perf Stats Script.  Importing the raw script output into a database makes it easier to do many types of analysis.  You can also use DataDemon to import arbitrary DMV/DMF query output or any other rowset data into a database; for details, see the "Adding New Rowsets" section, below.

 

A summary of some of DataDemon's features:

 

Using DataDemon

 

DataDemon.exe is built on the .NET Framework Version 1.1, so you must have this version of the framework installed.  After launching DataDemon, all you need to do is select the text file you want to import and provide the name of the SQL Server 2000/2005 instance you want to import the data into. 

 

 

Adding New Rowsets

 

The default rowsets that DataDemon knows how to import are listed in the TextRowsets.xml file.  Many common DBCCs, DMVs, and catalog views are recognized by default, but you can easily add to this list if you need to:

  1. Rename TextRowsetsCustom_Template.xml to TextRowsetsCustom.xml (this is only necessary before your first new rowset).
  2. Open TextRowsetsCustom.xml for editing in an editor (notepad will work fine). 
  3. Add the following lines as a new child element within "<KnownRowsets>": 

      <Rowset name="tablename" enabled="true" identifier="identifier" type="DataDemonEngine.TextRowset">
      </Rowset>

To make DataDemon recognize a new rowset you need to replace the tablename and identifier strings in the above sample.  The identifier value is the string that DataDemon will use to differentiate your rowset from the other rowsets in the file.  The tablename value is the name of the table that the utility will import the data into when it encounters the identifier. 

 

If you're wondering why this is necessary, the utility does recognize each rowset in the input file, but without this information it wouldn't know what table name each rowset should be imported into.  The info above is needed to associate an unnamed rowset in the input file with a particular table name in the database.

 

 

An Example of Adding a Rowset

 

The identifier can be any unique string from the column header line of the resultset, or from the line immediately preceding the column headers.  For example, suppose that you wanted to import the results of this batch:

 

     while (1=1) begin

        print '-- sysprocesses'
        select getdate() as runtime, * from sysprocesses

        waitfor delay'0:0:5'

     end

 

The output of this query looks like this:

 

     -- sysprocesses
     runtime                   spid   kpid   ...
     ------------------------- ------ ------ ...
     2006-04-29 12:20:55.593   1      0      ...

 

You could use either a string from the column headers (e.g. identifier="runtime                   spid   kpid") as the identifier, or a unique string from the preceding line (e.g. identifier="-- sysprocesses"). 

 

     <Rowset name="tbl_SYSPROCESSES" enabled="true" identifier="-- sysprocesses" type="DataDemonEngine.TextRowset">
     </Rowset>

 

 

Specifying Column Datatypes

 

By default, every column is typed as varchar.  The column width is determined by the observed width of the column in the input file.  You must tell DataDemon what type to use if you want a particular column to have a type other than varchar.  You do this by editing the TextRowsets.xml file and adding <Column> elements within your <Rowset>.  Here's an example:

 

     <Rowset name="tbl_VIRTUALFILESTATS" enabled="true" identifier="-- fn_virtualfilestats" type="DataDemonEngine.TextRowset">

          <Column name="runtime" type="DataDemonEngine.DateTimeColumn" rowsetlevel="False" />
          <Column name="DbId" type="DataDemonEngine.IntColumn" rowsetlevel="False" />

     </Rowset>

The supported data types and the SQL data types they map to are:

DataDemonEngine.VarCharColumn

varchar(*)

DataDemonEngine.NVarCharColumn

nvarchar(*)

DataDemonEngine.IntColumn

int

DataDemonEngine.BigIntColumn

bigint

DataDemonEngine.DateTimeColumn

datetime

DataDemonEngine.FloatColumn

float(53)

DataDemonEngine.DecimalColumn

decimal(38,10)

DataDemonEngine.VarBinaryColumn

varbinary(*)

 

  * width determined dynamically

It is OK if a column defined in TextRowsetsCustom.xml may or may not show up in your rowset (for example, because of differences between various SQL versions). Only the columns present in the input file will be created in the destination table; any columns defined in TextRowsets that do not appear in the input file will be ignored. 

 

 

How to ensure that your script output can be imported

Troubleshooting

 

Column headers in script output may be garbled, preventing proper import. 

This can be caused by a SNAC native OLEDB provider bug (sqlcmd.exe uses the SNAC OLEDB provider).  There's a bug filed on the issue that is currently scheduled for an SP2 fix.  The workaround prior to SP2 is to add a RAISERROR WITH NOWAIT immediately following each select statement in your script to force a network packet flush.  See above for an example of RAISERROR WITH NOWAIT.