Scripting an SSMS diagram

Yes, diagrams can be moved from system to system.  Any objects referenced by a diagram that do not exist where ‘restored’ will simply be deleted from the diagram.  Since diagrams might be created, saved and closed – then the underlying objects deleted or changed before the diagram is re-opened the rendering engine must be quite robust and so restoring a diagram can’t cause any issues to the underlying database.

IF OBJECT_ID('ScriptDiagram') IS NOT NULL 
  DROP PROCEDURE dbo.ScriptDiagram;
GO
-- Script a database diagram. 
-- Typically to copy it to another database that has exactly the same objects.
-- Note that diagrams only reference objects.
-- They don't contain definitions for objects so the objects must exist.
-- The references are by name not ID so the behavior most people want usually happens.
-- The required parameter is the name of the diagram sans the principal_id. 
-- You can support principal_id using the function DATABASE_PRINCIPAL_ID('dbo').
-- This tool just copies the existing ID which is usually OK.
CREATE PROCEDURE [dbo].[ScriptDiagram] (@name VARCHAR(128))
AS 
BEGIN
  DECLARE @diagram_id INT
  DECLARE @size INT
  DECLARE @chunk INT = 32
  DECLARE @index INT = 1
  DECLARE @line VARCHAR(MAX)
   
   -- Get PK diagram_id using the diagram's name.
  SELECT  @diagram_id = diagram_id
         ,@size = DATALENGTH(definition)
  FROM    sysdiagrams
  WHERE   [name] = @name

  IF @diagram_id IS NULL 
    BEGIN
      PRINT '/**<error>'
      PRINT 'Diagram name [' + @name + '] could not be found.'
      PRINT '</error>*/' 
    END
  ELSE -- Diagram exists
    BEGIN
         -- Use the diagram_id (not the name) to retrieve data for the script
      PRINT 'USE ' + DB_NAME()
      PRINT '/**'
      PRINT '<summary>'
      PRINT 'Restore diagram ''' + @name + ''''
      PRINT '</summary>'
      PRINT '<remarks>'
      PRINT 'Generated by ScriptDiagram'
      PRINT 'Will attempt to create [sysdiagrams] table if it doesn''t already exist'
      PRINT '</remarks>'
      PRINT '<generated>' + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16) + '</generated>'
      PRINT '*/'
      PRINT '    PRINT ''=== Start creation/update of diagram in ' + DB_NAME() + ' ==='''
      PRINT '    -- If the sysdiagrams table has not been created in this database, create it!
          IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''sysdiagrams'')
          BEGIN
              -- Create table script generated by Sql Server Management Studio
              -- _Assume_ this is roughly equivalent to what Sql Server/Management Studio
              -- creates the first time you add a diagram to a 2008 database
              CREATE TABLE [dbo].[sysdiagrams](
                  [name] [sysname] NOT NULL,
                  [principal_id] [int] NOT NULL,
                  [diagram_id] [int] IDENTITY(1,1) NOT NULL,
                  [version] [int] NULL,
                  [definition] [varbinary](max) NULL,
              PRIMARY KEY CLUSTERED 
              (
                  [diagram_id] ASC
              )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ,
               CONSTRAINT [UK_principal_name] UNIQUE NONCLUSTERED 
              (
                  [principal_id] ASC,
                  [name] ASC
              )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) 
              ) 
              EXEC sys.sp_addextendedproperty @name=N''microsoft_database_tools_support'',
                   @value=1 , @level0type=N''SCHEMA'',@level0name=N''dbo'',
                   @level1type=N''TABLE'',@level1name=N''sysdiagrams'';
              PRINT ''[sysdiagrams] table required creation''
          END
          -- Target table will now exist, if it didn''t before'
      PRINT 'SET NOCOUNT ON -- Hide (1 row affected) messages'
      PRINT 'DECLARE @newid INT'
      PRINT 'SELECT @newid = [diagram_id] from sysdiagrams where [name] = ''' + @name + ''';'
      PRINT ''
         -- For simplicity, the record should look the same whether we're updating or creating new
      PRINT 'BEGIN TRY'
      PRINT '    IF @newid IS NULL'
      PRINT '    BEGIN'
      PRINT '        Print ''Inserting new ''''' + @name + ''''' diagram'''
      SELECT  @line = '        INSERT INTO sysdiagrams ([name], [principal_id], [version], [definition])'
              + ' VALUES (''' + [name] + ''', ' + CAST (principal_id AS VARCHAR(100)) + ', '
              + CAST (version AS VARCHAR(100)) + ', 0x)'
      FROM    sysdiagrams
      WHERE   diagram_id = @diagram_id
      PRINT @line
      PRINT '        SET @newid = SCOPE_IDENTITY()'
      PRINT '    END'
      PRINT '    ELSE'
      PRINT '    BEGIN'
      PRINT '        PRINT ''Updating existing ''''' + @name + ''''' diagram'''
      SELECT  @line = '        UPDATE sysdiagrams SET [name] = ''' + [name] + ''', ' + '[principal_id] = '
              + CAST (principal_id AS VARCHAR(100)) + ', ' + '[version] = ' + CAST (version AS VARCHAR(100)) + ' '
              + 'WHERE diagram_id = @newid'
      FROM    sysdiagrams
      WHERE   diagram_id = @diagram_id
      PRINT @line
      PRINT '        UPDATE sysdiagrams SET [definition] .Write(null, 0, 0) WHERE diagram_id = @newid'
      PRINT '    END'
      PRINT ''
         -- Now, one way or another the record is ready to have binary data appended.
         -- Output UPDATE statements to append diagram data encoded as hex strings.
      WHILE @index < @size 
        BEGIN
          SELECT  @line = '    UPDATE sysdiagrams SET [definition] .Write (' + ' '
                  + UPPER(sys.fn_varbintohexstr(SUBSTRING(definition, @index, @chunk)))
                  + ', null, 0) WHERE diagram_id = @newid -- index:' + CAST(@index AS VARCHAR(100))
          FROM    sysdiagrams
          WHERE   diagram_id = @diagram_id
          PRINT @line
          SET @index = @index + @chunk
        END
      PRINT ''
      PRINT '    PRINT ''=== Finished writing diagram id '' + CAST(@newid AS VARCHAR(100)) + '' to ' + DB_NAME()
        + ' ==='''
      PRINT 'END TRY'
      PRINT 'BEGIN CATCH'
      PRINT '    -- If we got here, the [definition] updates didn''t complete, so delete the diagram row'
      PRINT '    -- (and hope it doesn''t fail!)'
      PRINT '    DELETE FROM sysdiagrams WHERE diagram_id = @newid'
      PRINT '    PRINT ''***** '' + Error_Message() + '' *****'''
      PRINT '    PRINT ''***** END ScriptDiagram - fix the error before running again *****'''
      PRINT '    RETURN'
      PRINT 'END CATCH'
    END
END
/*
GO
ScriptDiagram 'TestDiagram';
--*/
Advertisements

Scripting table data

Sometimes the actual data in a table might need to be saved in a way that lets one re-insert it easily.
Scripting it into XML or JSON is quite easy but requires parsing to re-insert into a table of similar structure to the original source.
Creating insert statements, while more difficult, requires no separate parsing to re-insert into a table.  In my experience, this is particularly useful in testing environments where it is either used to reset tables back to a known state or to store the ‘correct answers’ to a test.

This scripts XML and BINARY data and handles identity columns by wrapping the insert statements with identity_insert on/off.  The newest data types (geo, json…) are not yet supported and neither are variant and obsolete types like image and text.

IF OBJECT_ID('ScriptTableData', 'P') IS NULL 
  EXEC('CREATE PROCEDURE ScriptTableData AS BEGIN PRINT ''NOT IMPLEMENTED'' END');
GO
/*===============================================================================================
Description:
  
  Returns a table with the sequence of commands to insert the queried data into another table.
  
Parameters:
  
  @Table (required)
     Name your source table here.  It can be a simple table name, a 2-part, 3-part or 4-part name.
     It will be given an alias of TBL if you care to use it in @Select, @Join or @Where params.
     
  @DestTable (optional... defaults to @Table)
    The table into which the data will be inserted.  No edits are performed on the
    value supplied.  Insert commands are generated using this name exactly as supplied.
  
  @Select (optional... defaults to '*')
     Typically used to limit the columns placed in the Insert statements.  Anything
     legal between a SELECT keyword and the INTO keyword is legal here although 'top x'
     is not detected as a limiting factor when testing for @MaxRows compliance.
     NOTE: Columns of type image, text, ntext, sql_variant and timestamp are not supported.

  @Join (optional... defaults to '')
     If you want to JOIN or Apply any additional tables you can specify a JOIN clause 
     (or clauses) here.  Basically anything legal between 'FROM <name> and 'Where...'
     is legal here.
  
  @Where (optional... defaults to '1=1')
     This allows you to limit the rows of the sample. Assume the keyword WHERE
     already preceeds your clause.
  
  @MaxRows (optional... defaults to 1000)
     This insures the output isn't huge by mistake.  If the selected rows exceeds 
     @MaxRows, an error will occur.
    
  @Index (optional... defaults to 0)
     This allows you to easily identify rows of one execution as distinct from another.
  
  @Debug (optional... defaults to 0)
     By
     already preceeds your clause.
  
===============================================================================================*/
ALTER PROCEDURE ScriptTableData
    @Table NVARCHAR(500)    = NULL  --Name of the Source table for the data
   ,@DestTable NVARCHAR(500)= @Table--Name of table into which data is inserted
   ,@Select NVARCHAR(MAX)   = '*'   --Columns to SELECT from the table(s)
   ,@Join NVARCHAR(MAX)     = ''    --Optional JOINs 
   ,@Where NVARCHAR(MAX)    = '1=1' --Optional WHERE clause
   ,@MaxRows BIGINT         = 1000  --Maximum records allowed
   ,@Index INT              = 0     --Segregates output scripts
   ,@Debug BIT              = 0     --When true, only create the script but don't execute it
AS 
BEGIN
  SET nocount ON  
  /*
    DECLARE @Table NVARCHAR(500)    = '[dbo].[sysdiagrams]';
    DECLARE @DestTable NVARCHAR(500)= @Table;
    DECLARE @Select NVARCHAR(MAX)   = '*'   ;
    DECLARE @Join NVARCHAR(MAX)     = ''    ;
    DECLARE @Where NVARCHAR(MAX)    = '1=1' ;
    DECLARE @MaxRows BIGINT         = 1000  ;
    DECLARE @Index INT              = 0     ;
    DECLARE @Debug BIT              = 1     ;
  --*/

  DECLARE @EmptyString NCHAR(1)     = '';
  DECLARE @Asterisk NCHAR(1)        = '*';
  DECLARE @EOL NCHAR(1)             = CHAR(13);
  DECLARE @SqlStmt NVARCHAR(MAX);
  DECLARE @NumRows BIGINT;
  DECLARE @ErrMsg NVARCHAR(4000);
  DECLARE @ColList NVARCHAR(MAX);
  DECLARE @ColValues NVARCHAR(MAX);
  
  IF ISNULL(@Table, @EmptyString) = @EmptyString 
    BEGIN
      RAISERROR('You must pass a @Table parameter.',16,1);
      RETURN -1;
    END
  
  IF @Select = @Asterisk
    AND @Join <> @EmptyString 
    BEGIN
      SET @ErrMsg = 'You must specify columns in the @Select parameter '
        + 'if you pass something in the @Join parameter.';
      RAISERROR(@ErrMsg,16,1);
      RETURN -1;
    END
  
  SET @SqlStmt = 'select @NumRows=count_big(*) from ' + @Table + ' ' + @Join + ' where ' + @Where;
  IF @Debug = 1 PRINT @SqlStmt + @EOL;
  EXEC sp_executesql @SqlStmt, N'@NumRows bigint output', @NumRows OUTPUT;

  IF @@error <> 0 
    BEGIN
      SET @ErrMsg = 'The @Table, @Join, @Where parameter combination was invalid.' + @EOL
        + 'The statement attempted was:' + @EOL + @SqlStmt
      RAISERROR(@ErrMsg,16,1)
      RETURN -1;
    END
  IF @NumRows > @MaxRows 
    BEGIN
      SET @ErrMsg = 'Your selection produces ' + LTRIM(STR(@NumRows)) + ' rows, ' + 'which is more than the '
        + LTRIM(STR(@MaxRows)) + ' maximum allowed.' + @EOL
        + 'If you want more than the maximum please use the @MaxRows parameter';
      RAISERROR(@ErrMsg,16,1);
      RETURN -1;
    END
  
  IF OBJECT_ID('tempdb..##_InsertGenerator_TMP_Data', 'U') IS NOT NULL 
    DROP TABLE ##_InsertGenerator_TMP_Data;
  
  --Get the data into a (global) temp table so it’s visible to sp_executesql
  IF len(@Join) > 0 SET @Join = '  ' + @Join + @EOL;
  SET @SqlStmt = 'select ' + @Select + @EOL + ' into ##_InsertGenerator_TMP_Data '+ @EOL 
    + ' from ' + @Table + ' as TBL' + @EOL + @Join + ' where ' + @Where;
  IF @Debug = 1 PRINT @SqlStmt + @EOL;
  EXEC sp_executesql @SqlStmt;
  
  IF @@error <> 0 
    BEGIN
      SET @ErrMsg = 'The @Table, @Select, @Join, @Where parameter combination was invalid.' + @EOL
        + 'The SQL Statement attempted was the following:' + @EOL + @SqlStmt;
      RAISERROR(@ErrMsg,16,1);
      RETURN -1;
    END
  
  --Now pull out the column definitions.
  --This can also be used to create the table we’re creating insert statements for.
  IF OBJECT_ID('tempdb..#_InsertGeneratorTMP_Stru', 'U') IS NOT NULL 
    DROP TABLE #_InsertGeneratorTMP_Stru
  SELECT  Name = Name
         ,DataType = TYPE_NAME(System_Type_ID)
         ,Max_Length
         ,Precision
         ,Scale
         ,Column_ID
         ,is_identity
  INTO    #_InsertGeneratorTMP_Stru     -- SELECT *
  FROM    tempdb.sys.columns
  WHERE   Object_ID = OBJECT_ID('tempdb..##_InsertGenerator_TMP_Data', 'U')
          AND TYPE_NAME(System_Type_ID) IN ('bigint', 'bit', 'decimal', 'int', 'tinyint', 'numeric', 'smallint',
                                            'smallmoney', 'money', 'float', 'real', 'date', 'datetime2', 'datetime',
                                            'datetimeoffset', 'smalldatetime', 'time', 'char', 'varchar', 'nchar',
                                            'nvarchar', 'uniqueidentifier', 'xml','varbinary','binary');
  --IF @Debug = 1 SELECT * FROM #_InsertGeneratorTMP_Stru;
  
  -- Create code that will eventually pull actual data 
  -- To simplify quote nesting I've used different characters for imbedded quotes that are replaced later.
  -- The backtick ` character is used to imbed a quote 1 level.  The carat ^ character imbeds a quote 2 levels.
  SET @ColValues = @EmptyString;
  SELECT  @ColValues = @ColValues 
        + CASE WHEN @ColValues = @EmptyString 
               THEN ''
            ELSE ' + `, `' + @EOL + SPACE(8) + '+ '
          END 
        + 'ISNULL(' + CASE WHEN DataType IN ('char', 'varchar')
                           THEN 'QuoteName(' + QUOTENAME(Name) + ',`^`)'

                           WHEN DataType IN ('nchar', 'nvarchar')
                           THEN '`N` + QuoteName(' + QUOTENAME(Name) + ',`^`)'

                           -- If handling of binary data with base-64 performs too badly the commented out 
                           -- hex version below should perform better at the expense of taking more space.
                           WHEN DataType IN ('varbinary', 'binary')
                           THEN '`cast(N`^` as xml).value(^xs:base64Binary("'
                                + '`+cast(N`` as xml).value(`xs:base64Binary(xs:hexBinary(sql:column("'
                                + QUOTENAME(Name) +'")))`, `varchar(MAX)`)+`")^, ^varbinary(MAX)^)`' 
--                         WHEN DataType IN ('varbinary', 'binary')
--                         THEN '`CONVERT(varbinary(max),`^ + convert(varchar(MAX),' + QUOTENAME(Name) + ',1) + ^`, 1)`'

                           WHEN DataType IN ('xml')
                           THEN '`N`^ + REPLACE(CAST(' + QUOTENAME(Name) + ' AS NVARCHAR(MAX)),`^`,`^^`) + `^`'

                           WHEN DataType IN ('date')
                           THEN '`^` + convert(nvarchar(100),' + QUOTENAME(Name) + ',112) + `^`'

                           WHEN DataType IN ('time')
                           THEN '`^` + convert(nvarchar(100),' + QUOTENAME(Name) + ',108) + `^`'

                           WHEN DataType IN ('uniqueidentifier')
                           THEN '`^` + convert(char(36),' + QUOTENAME(Name) + ') + `^`'

                           WHEN DataType IN ('datetime', 'datetime2', 'smalldatetime','datetimeoffset')
                           THEN '`^` + convert(varchar(36),' + QUOTENAME(Name) + ',126) + `^`'

                           WHEN DataType IN ('money', 'smallmoney', 'float', 'real')
                           THEN 'convert(varchar(36),' + QUOTENAME(Name) + ',2)'

                        -- This handles bigint, int, smallint, tinyint, bit, decimal & numeric
                        ELSE 'convert(varchar(36),' + QUOTENAME(Name) + ')'
                      END + ',`NULL`)'
  FROM    #_InsertGeneratorTMP_Stru
  IF @Debug = 1 PRINT @ColValues + @EOL;

  -- This is the list of columns to specify in the insert.
  SELECT  @ColList = '(' + LEFT(x.x, LEN(x.x) - 1) + ')'
  FROM    (SELECT (SELECT '[' + Name + '],' [text()]
                   FROM   #_InsertGeneratorTMP_Stru
                  FOR
                   XML PATH('')
                  ) AS x
          ) AS x;
  IF @Debug = 1 PRINT @ColList + @EOL;

  IF @DestTable = '' OR @DestTable IS null
    SET @DestTable = @Table;
  
  -- Create the query that yields the desired output and execute it.  Notice, now that order doesn't matter it's easier
  -- to wrap the data with identity insert statements when needed.
  SET @SqlStmt = '';
  IF EXISTS(SELECT is_identity FROM #_InsertGeneratorTMP_Stru WHERE is_identity = 1) 
    AND EXISTS(SELECT * FROM ##_InsertGenerator_TMP_Data)
      SET @SqlStmt = 'select '+cast(@Index as varchar)+' as I,-1 as R, `SET IDENTITY_INSERT ' 
            + @DestTable + ' On;` as C' + @EOL
            + ' union all' + @EOL
            + 'select '+cast(@Index as varchar)+',100000000,`SET IDENTITY_INSERT ' + @DestTable + ' Off;`' + @EOL
            + ' union all' + @EOL;

  IF @Debug = 1
      SET @SqlStmt += 'select '+cast(@Index as varchar)+' as I,ROW_NUMBER() OVER(ORDER BY (SELECT 1)) as R' + @EOL
            + '      ,`SELECT * FROM (VALUES(`+' + @ColValues + '+`))X' + @ColList + ';` as C' + @EOL 
            + '  from ##_InsertGenerator_TMP_Data' + @EOL
            + ' order by I,R;'
  ELSE
      SET @SqlStmt += 'select '+cast(@Index as varchar)+' as I,ROW_NUMBER() OVER(ORDER BY (SELECT 1)) as R' + @EOL
            + '      ,`INSERT ' + @DestTable + @ColList + @EOL 
            + 'values(`+' + @ColValues + '+`);` as C' + @EOL
            + '  from ##_InsertGenerator_TMP_Data' + @EOL
            + ' order by I,R;'
  
  IF @Debug = 1 PRINT @SqlStmt + @EOL;
  SET @SqlStmt = REPLACE(REPLACE(@SqlStmt,'^','``'),'`','''');
  IF @Debug = 1 PRINT @SqlStmt + @EOL;
  EXEC sp_executesql @SqlStmt;
  
  IF @@error <> 0 
    BEGIN
      RAISERROR('Unknown error in executing final SQL Statement.',16,1);
      RETURN -1;
    END
  
  IF OBJECT_ID('tempdb..##_InsertGenerator_TMP_Data') IS NOT NULL DROP TABLE ##_InsertGenerator_TMP_Data;
  IF OBJECT_ID('tempdb..#_InsertGeneratorTMP_Stru') IS NOT NULL DROP TABLE #_InsertGeneratorTMP_Stru;
  
END
/*
GO
EXEC ScriptTableData 'dbo.sysdiagrams';                                                         -- Has an identity column
EXEC ScriptTableData 'dbo.sysdiagrams',@Debug=1;                                                -- Has an identity column
EXEC ScriptTableData 'dbo.sysdiagrams',@Select='name,principal_id,version,definition',@Debug=1; -- Leave out identity 
EXEC ScriptTableData 'sys.dm_exec_query_stats'                                                  -- Has binary data
EXEC ScriptTableData 'sys.dm_exec_query_stats','#Tmp','query_hash,query_plan',
     'CROSS APPLY sys.dm_exec_query_plan(plan_handle)'                                          -- Include XML
EXEC ScriptTableData 'sys.dm_exec_query_stats','#Tmp','query_hash,query_plan',
     'CROSS APPLY sys.dm_exec_query_plan(plan_handle)',@Debug=1;                                -- With Debug

-- This makes use of the multi-table feature by scripting the content from all the tables in a given schema
CREATE TABLE #INSERTS(f int,r int,CMD NVARCHAR(MAX));
DECLARE @CMDS VARCHAR(MAX) = '';
select @CMDS += 'INSERT INTO #INSERTS Exec ScriptTableData '''+QUOTENAME(schema_name(t.[schema_id]))
             +'.'+QUOTENAME(t.name)+''',@Index='+CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS VARCHAR)+',@Debug=1;'+char(13) -- select *
from sys.tables t
--where t.[schema_id] = SCHEMA_ID('tSQLt')
order by t.[schema_id],t.name;
print @CMDS;
exec (@CMDS);
SELECT * FROM #INSERTS order by f,r;
DROP TABLE #INSERTS;
--*/

Converting netstats output to a stream of PowerShell objects

The netstats command returns useful information that isn’t easy to collect using PowerShell functions directly.  Unfortunately using it’s output isn’t straightforward because the data isn’t completely columnar.  That is, some data ‘columns’ are returned on their own separate lines, making the data difficult to use.  This creates custom objects that are easily consumed by other PowerShell operators.

function Get-NetStatData ([switch]$Numeric)
{
    function Get-EmptyRow {''|select -Property Protocol,LocalAddress,TargetAddress,State,PID,Program,OnBehalfOf}
    $Params = $(if ($Numeric) {'-abno'} else {'-abo'})
    $ObjectRow = Get-EmptyRow

    switch -Regex(netstat $Params)
    {'^ (?<pgm>[^ ].+)$' # begins with only 1 space (owner program)
        {$ObjectRow.Program = $Matches['pgm']
        }
     '^  (?<of>\w+)$'   # starts with 2 spaces then just 1 word (caller of owner?)
        {$ObjectRow.OnBehalfOf = $Matches['of']
        }
     '^  (?<proto>\w+)  +(?<local>[^ ]+)  +(?<target>[^ ]+)  +(?<state>\w+)?  +(?<pid>\d+)$' # Main data line
        {if ($ObjectRow.LocalAddress -ne $null)
            {$ObjectRow
             $ObjectRow = Get-EmptyRow
            }
         $ObjectRow.Protocol = $Matches['proto']
         $ObjectRow.LocalAddress = $Matches['local']
         $ObjectRow.TargetAddress = $Matches['target']
         $ObjectRow.State = $Matches['state']
         $ObjectRow.PID = $Matches['pid']
        }
     default {} # ignore empty lines and the initial "Active Connections" line (which doesn't start with a space)
    }
    $ObjectRow
}
Get-NetStatData |ft 

Protocol LocalAddress                         TargetAddress               State       PID   Program                              OnBehalfOf       
-------- ------------                         -------------               -----       ---   -------                              ----------       
TCP      0.0.0.0:80                           LocalLaptopJ6K:0            LISTENING   4     Can not obtain ownership information                  
TCP      0.0.0.0:135                          LocalLaptopJ6K:0            LISTENING   536   [svchost.exe]                        RpcSs            
TCP      0.0.0.0:445                          LocalLaptopJ6K:0            LISTENING   4     Can not obtain ownership information                  
TCP      0.0.0.0:2179                         LocalLaptopJ6K:0            LISTENING   4228  [vmms.exe]                                            
TCP      0.0.0.0:2383                         LocalLaptopJ6K:0            LISTENING   5512  [msmdsrv.exe]                                         
TCP      0.0.0.0:2701                         LocalLaptopJ6K:0            LISTENING   14948 [CmRcService.exe]                                     
TCP      0.0.0.0:3389                         LocalLaptopJ6K:0            LISTENING   1300  [svchost.exe]                        TermService      
TCP      0.0.0.0:5120                         LocalLaptopJ6K:0            LISTENING   4128  [STSchedEx.exe]                                       
UDP      0.0.0.0:123                          *:*                                     1444  [svchost.exe]                        W32Time          
UDP      0.0.0.0:500                          *:*                                     3872  [svchost.exe]                        IKEEXT           
UDP      0.0.0.0:1434                         *:*                                     3528  [sqlbrowser.exe]                                      
UDP      0.0.0.0:3249                         *:*                                     17152 [msddsk.exe]                                          
UDP      0.0.0.0:3389                         *:*                                     1300  [svchost.exe]                        TermService      
...

Execute the same query on all databases in an instance (Alternative to sp_MSForEachDB)

Although many people know Microsoft includes an undocumented stored procedure (sp_MSForEachDB) that looks like an ideal solution to the problem of executing the same query in every database on an instance there are a long list of drawbacks, not the least of which is that it is undocumented and therefore ill-advised to be used on production systems. That procedure uses a deprecated compatibility view in conjunction with a cursor to iterate over the databases and a temporary table in conjunction with a while loop to perform token replacement making performance pretty bad. Some other issues that sp_MSForEachDB as well as many ‘replacements’ you find on the web also suffer from are:

  • A separate result set returns for each database making sorting or use with the Registered Servers feature of SSMS harder.
  • You can’t apply criteria to exclude databases such as read-only or offline ones.
  • The replacement token is limited to a single character that limits query content and makes testing harder.
  • The query itself is limited to 2000 characters AFTER token replacement.
  • It can be difficult to troubleshoot query failures because there is no simple way to examine the ‘after replacement’ query text.

The example below (like sp_MSForEachDB) uses the string ‘?’ as the replacement token although with this implementation you can use almost any string you want. For example I sometimes use ‘master’. This lets me test the query without the need to find and fix all the database references before I double up single quotes for stuffing into @src.

Notice my use of ‘…from [?].sys…’. I wrap ? with brackets because I want it to read ‘…from [db name].sys…’ after replacement in case a database name has a space in it. You might also notice this example shows how easy it is to resolve collation differences as results from different databases are merged together. The ‘collate database_default’ clause resolves to the default collation of the default database for your connection. Any iterating method that uses ‘Use
<DBName>;…’ risks collation changes. You must then choose a specific collation to code into your query whereas the method below doesn’t change the default database resulting in a constant database_default and insures values can’t conflict.

declare @src varchar(max);
declare @query varchar(max);
set @src ='
select ''?''[DB] ,name collate database_default [Name]
      ,protocol_desc ,type_desc, state_desc, is_admin_endpoint
  from [?].sys.endpoints
';
set @query ='';
select @query = @query +REPLACE(@src,'?',db.name)+' union all '
  from sys.databases db
 where state= 0 and is_read_only = 0;
set @query =SUBSTRING(@query,1,(len(@query)-11))+' order by 2,1 option(recompile);';
--select cast('<?q '+@query+' ?>' as xml)x; -- Uncomment this to troubleshoot.
exec (@query);

As I said above, I’m stuffing the query I want executed in every database into the varchar variable @src, similar to using sp_MSForEachDB however I can now filter the list of databases (see where I specify: where state = 0 and is_read_only = 0) to exclude some. The “select @query = @query…” statement creates one large query using ‘union all’ between databases so a single result set automatically returns without any work and, as a side effect, if the number or data type of columns returned from one database (perhaps due to a different compatibility level) is different between any 2 databases I won’t get random failures interspersed with incomplete results. The query is either valid in all databases or execution won’t occur. Another nice side effect is that the optimizer can decide to make this a parallel query and execute in multiple databases simultaneously to provide even faster results.

Since the generated query is often quite large and, personally, I’m not likely to execute it more than once, it doesn’t make sense that the huge plan take up query plan cache space. For this reason I tack on ‘ option(recompile);’ to prevent it being cached. Just prior to (or instead of) the option clause is a good place to add an ‘order by’ clause if you’d like the final result to be ordered.

Troubleshooting

If I want to see the generated statement rather than executing it I can use ‘Select @query’ rather than ‘exec (@query)’. This makes troubleshooting syntax errors quite easy. The slightly more complex select command provided in the example (but commented out) takes advantage of an SSMS feature provided so you can view XML documents returned by a query in a separate window. The ‘trick’ being used is to imbed the generated query text in an XML processing instruction. This turns on the SSMS feature that makes the column contents clickable. You can then click the cell in the output window and SSMS opens a new document window containing only that cell’s content with line breaks etc. intact for easy viewing. Note you must be using ‘Results to grid’ for this to work. It is also remotely possible you may need to change the [Options][Query Results][Results to Grid][Max Characters Retrieved][XML Data] limit to see the entire query.

Stored procedure

If you want to create a procedure, most of the logic isn’t hard to capture.  I’ll leave it to you to decide how you want to implement any ‘where’ clause you might want when selecting the names of the databases to execute upon but this should get you started.  Personally I would use the values in the example above as default values for @concatenator and @tail and you should choose what you prefer as well.

create procedure query_active_databases(
   @src varchar(max)
  ,@token varchar(200)='?'
  ,@concatenator varchar(200)=';'
  ,@tail varchar(200)=''
  ,@debug bit= 0)
 as
 begin
   declare @query varchar(max);
   set @query ='';
   select @query = @query + REPLACE(@src,@token,db.name) + @concatenator
     from sys.databases db
     where state = 0 and is_read_only = 0;
   set @query =SUBSTRING(@query,1,(len(@query)-len(@concatenator)))+@tail;
   if @debug = 1
      select cast('<?q '+@query+' ?>'as xml)x;
   else
     exec (@query);
 end;
 go
-- Basic example behaves like sp_MSForEachDB
exec query_active_databases @src='
 select ''?''[DB] ,name collate database_default [Name]
       ,protocol_desc ,type_desc, state_desc, is_admin_endpoint
   from [?].sys.endpoints';
go
-- This uses 'USE' rather than qualifying all names with the database.
-- Notice this forces me to pick an explicit collation sequence.
exec query_active_databases @src='
 use [?];
 select DB_NAME() [DB] ,name collate latin1_general_ci_as [Name]
       ,protocol_desc ,type_desc, state_desc, is_admin_endpoint
   from sys.endpoints';
go
-- Here I'm using all the bells and whistles to return a single sorted result
exec query_active_databases @src='
 select ''master''[DB] ,name collate database_default [Name]
       ,protocol_desc ,type_desc, state_desc, is_admin_endpoint
   from [master].sys.endpoints',
 @token = 'master', @concatenator = ' union all', @tail = ' order by 2,1 option(recompile);';

Looking for references in SSIS

This post was originally created in February of 2012 but for some reason it got deleted by Word Press with no notice or explanation. I just discovered it missing and though I can’t find a way to give the post its original URL (https://rickbielawski.wordpress.com/2012/02/22/looking-for-references-in-ssis), with the help of web.archive.org I’ve been able to restore the content.


Similar to my post of a few years ago where you may want to search an SSRS package (Querying Information from Deployed SSRS Packages) you can also search SSIS. The big difference is in the namespaces used and the fact that sometimes the package isn’t actually in a database to be queried. This query finds certain types of information in a package and returns its location.

-- This query shows exactly where in a package a query containing the search criteria (@F) can be found. 
-- Be careful in your specification of table/column names since wildcards and patterns are not allowed 
-- and some references are generated as [schema].[name] while others are just schema.name.

Declare @F varchar(100) = 'TextToFind';  --< Put the text you want to find here

With XMLNAMESPACES(N'www.microsoft.com/SqlServer/Dts'               AS DTS
                  ,N'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask
                  ,N'http://www.w3.org/2001/XMLSchema'              AS xsd
                  ,N'urn:schemas-microsoft-com:xml-msdata'          AS msdata)
,   FileData AS (select cast( [SSISPkgData].BulkColumn AS XML) AS pkgXML
                   from openrowset(bulk 'H:\SomePackage.dtsx',single_blob) as [SSISPkgData])
, Found as (                                 
select Nodes.value('@SQLTask:SqlStatementSource','varchar(MAX)') Txt
      -- Notice this very cool feature.  From SSMS results window you can click on the statement to see it. 
      ,QueryText  =(select [processing-instruction(q)]=Nodes.value('@SQLTask:SqlStatementSource'
                    ,'varchar(MAX)') for xml path(''),type)
      ,Nodes.value('local-name(../..)','varchar(max)') Parent
      ,Nodes.value('../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') ParentName
      ,Nodes.value('local-name(../../..)','varchar(max)') GrandParent
      ,Nodes.value('../../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GrandParentName
      ,Nodes.value('local-name(../../../..)','varchar(max)') GGParent
      ,Nodes.value('../../../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GGParentName
  from FileData F
 CROSS APPLY F.pkgXML.nodes('//SQLTask:SqlTaskData[contains(upper-case(@SQLTask:SqlStatementSource)
                            ,upper-case(sql:variable("@F")))]') Pkg(Nodes)
union all
select Nodes.value('.','varchar(MAX)') Txt
      ,QueryText  =(select [processing-instruction(q)]=Nodes.value('.','varchar(MAX)') for xml path(''),type)
      ,Nodes.value('local-name(..)','varchar(max)') Parent
      ,Nodes.value('../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') ParentName
      ,Nodes.value('local-name(../..)','varchar(max)') GrandParent
      ,Nodes.value('../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GrandParentName
      ,Nodes.value('local-name(../../..)','varchar(max)') GGParent
      ,Nodes.value('../../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GGParentName
  from FileData F
 CROSS APPLY F.pkgXML.nodes('//DTS:Property[@DTS:Name="Expression" and . != "" and contains(upper-case(.)
                            ,upper-case(sql:variable("@F")))]') Pkg(Nodes)
union all
select Nodes.value('.','varchar(MAX)') Txt
      ,QueryText  =(select [processing-instruction(q)]=Nodes.value('.','varchar(MAX)') for xml path(''),type)
      ,Nodes.value('local-name(../..)','varchar(max)') Parent
      ,Nodes.value('../../@name','varchar(MAX)') ParentName
      ,Nodes.value('local-name(../../../../../..)','varchar(max)') GrandParent
      ,Nodes.value('../../../../../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GrandParentName
      ,Nodes.value('local-name(../../../../../../..)','varchar(max)') GGParent
      ,Nodes.value('../../../../../../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GGParentName
  from FileData F
 CROSS APPLY F.pkgXML.nodes('//property[(@name="SqlCommand" or @name="SqlCommandVariable" or 
                             @name="OpenRowset" or @name="OpenRowsetVariable") and . != "" and 
                             contains(upper-case(.),upper-case(sql:variable("@F")))]') Pkg(Nodes)
)
Select Parent,ParentName,GrandParent,GrandParentName,GGParent,GGParentName,QueryText
  from Found
 --where Txt like '%update%'  -- Uncomment this Where clause to further restrict results

Converting from UTC to Central Daylight Time (or some other DST time zone)

You might think conversion is simple.  Even that there is already a function to do it.  The fact is, it’s not easy at all and it’s not really practical for OS providers to create such a function either.  A generalized solution is so complex that companies exist just to create and maintain class libraries to deal with the complexities.  The rules change not only depending on where you are in the world but what year it is (and more).  This example is specialized for the Central Time Zone and uses rules specific to the United States but it is adaptable to other similarly narrow situations.  Under these constraints the algorithm performs better than any other I’ve seen.

The function can be used like this:

SELECT u.Utc, c.CentralTime
FROM MyTable u
CROSS APPLY UtcToCentralTime(u.Utc) c

Or like this:

SELECT u.Utc, (SELECT CentralTime FROM UtcToCentralTime(u.Utc)) CentralTime
FROM MyTable u

Either usage method might perform better than the other under your specific query conditions. Try them both if you need the ‘best’ performance. I’ve not yet seen a case when the difference was significant though.

 -- =============================================
 -- Author: Rick Bielawski
 -- Description: Converts a GMT(UTC) timestamp to Central Time
 -- The rules for dates between Apr 2000 and Oct 2027 are currently
 -- coded.  Dates outside that range may or may-not return the correct
 -- results.  No warning is given for out of range values.  And, if the 
 -- laws change, even dates before 2027 may not be correct until this
 -- function is updated.
 -- =============================================
 CREATE FUNCTION UtcToCentralTime (@UTC DATETIME2(7))
 RETURNS TABLE
 AS
 RETURN
 WITH UtcDstTime(StartTime, EndTime)
 AS (
 -- There is a smaller amount of time during the year when offset is -6 vs -5 (34 weeks vs 18) so
 -- I made the default -5 so that times the table doesn't cover are more likely to still be correct.
 -- The table therefore needs to contain non-DST ranges rather than the DST time range.
 -- Notice too that the time in these ranges must be expressed as UTC(GMT)
 SELECT CAST('20001029 7:00' AS DATETIME), CAST('20010401 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20011028 7:00' AS DATETIME), CAST('20020407 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20021027 7:00' AS DATETIME), CAST('20030406 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20031026 7:00' AS DATETIME), CAST('20040404 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20041031 7:00' AS DATETIME), CAST('20050403 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20051031 7:00' AS DATETIME), CAST('20060402 8:00' AS DATETIME) UNION ALL
 -- DST changed dates in 2007
 SELECT CAST('20061029 7:00' AS DATETIME), CAST('20070311 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20071104 7:00' AS DATETIME), CAST('20080309 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20081102 7:00' AS DATETIME), CAST('20090308 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20091101 7:00' AS DATETIME), CAST('20100314 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20101107 7:00' AS DATETIME), CAST('20110313 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20111106 7:00' AS DATETIME), CAST('20120311 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20121104 7:00' AS DATETIME), CAST('20130310 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20131103 7:00' AS DATETIME), CAST('20140309 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20141102 7:00' AS DATETIME), CAST('20150308 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20151101 7:00' AS DATETIME), CAST('20160313 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20161106 7:00' AS DATETIME), CAST('20170312 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20171105 7:00' AS DATETIME), CAST('20180311 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20181104 7:00' AS DATETIME), CAST('20190310 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20191103 7:00' AS DATETIME), CAST('20200308 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20201101 7:00' AS DATETIME), CAST('20210314 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20211107 7:00' AS DATETIME), CAST('20220313 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20221106 7:00' AS DATETIME), CAST('20230312 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20231105 7:00' AS DATETIME), CAST('20240310 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20241103 7:00' AS DATETIME), CAST('20250309 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20251102 7:00' AS DATETIME), CAST('20260308 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20261101 7:00' AS DATETIME), CAST('20270314 8:00' AS DATETIME)
 )
 SELECT   CASE WHEN d.StartTime IS NULL THEN DATEADD(HOUR, -5, UTC)
 ELSE DATEADD(HOUR, -6, UTC)
 END AS CentralTime
 FROM     (SELECT @UTC AS UTC
 ) u
 LEFT JOIN UtcDstTime d
 ON u.UTC >= d.StartTime
 AND u.UTC < d.EndTime;

Assembling sysjobhistory progress messages

Sometimes when an SQL Agent Job logs its status to sysjobhistory the message is broken across multiple ‘progress’ records making it difficult to read.   I’ve noticed this especially with  messages from SSIS jobs.  They can appear truncated because they actually continue in very arbitrary places on another ‘Progress’ record.

I also find it useful to have a way to get job history with a query rather than via the GUI.  This function solves both problems.

Note it would be a nit to implement this as a stored procedure and looking back I probably should have.

Because there is no column or set of columns that definitively groups messages from a single agent job execution (run_date and/or run_time does NOT work) this function uses 2 attributes of agent jobs to contrive a definitive grouping.

  • There can’t be concurrent executions of the same job so messages from two executions can’t overlap.
  • All jobs end with a message that has a step_id of 0 (zero)
    Note: I labeled that step ‘(Job Outcome)’ below since it has no MSDB.dbo.sysjobsteps entry and therefore no step_name.

The basic query flow is:

  1. x CTE collects records pertinent to @jobname & @date and makes certain columns more readable.
  2. rec CTE is recursive and starts with all the ‘Job Outcome’ messages (or the last message from each job name to catch a job that’s still in progress) then uses recursion to walk backward until it either runs out of data or runs into the next  (Job Outcome) message.
    The recursion also collects ‘Progress’ messages into the step’s outcome row.
  3. The main query sequences the results and filters out the now unnecessary progress messages.
-- =============================================
-- Author:      Rick Bielawski
-- Create date: 4/18/2013
-- Description: Lists the status and messages associated with a job,
--              re-assembling all messages from a step into a single message.
--      If the job name is null no results return.
--      If a date is null, all executions of the job are included.
-- =============================================
ALTER FUNCTION GetJobStatusMessages (
   @JobName SYSNAME
  ,@date DATE
  )
RETURNS TABLE
  AS RETURN
  WITH  x AS (SELECT  jh.instance_id
                     ,j.name
                     ,COALESCE(js.step_name, '(Job Outcome)') AS step_name
                     ,jh.step_id
                     ,DATEADD(dd, ((jh.run_date) % 100) - 1,
                              DATEADD(mm, ((jh.run_date) / 100 % 100) - 1,
                                      DATEADD(yy, (NULLIF(jh.run_date, 0) / 10000) - 1900, 0)))
                        + DATEADD(ss,jh.run_time % 100,
                                  DATEADD(mi,(jh.run_time/ 100) % 100,
                                  DATEADD(hh,jh.run_time/ 10000, 0))) AS RunTime
                     ,CASE jh.run_status
                        WHEN 0 THEN 'Failure'
                        WHEN 1 THEN 'Success'
                        WHEN 2 THEN 'Retry'
                        WHEN 3 THEN 'Canceled'
                        WHEN 4 THEN 'Progress'
                        ELSE 'Unknown'
                      END AS [Status]
                     ,jh.run_duration Duration
                     ,ROW_NUMBER() OVER (ORDER BY j.job_id, jh.instance_id DESC) row
                     ,CAST(jh.message AS NVARCHAR(MAX)) AS msg
              FROM    MSDB.dbo.sysjobs j
              JOIN    MSDB.dbo.sysjobhistory jh
              LEFT OUTER JOIN MSDB.dbo.sysjobsteps js
                      ON js.job_id = jh.job_id
                         AND jh.step_id = js.step_id
                      ON jh.job_id = j.job_id
              WHERE   j.name = @JobName
                      AND COALESCE(CAST(CONVERT(CHAR(8),@date,112) AS INT),jh.run_date) = jh.run_date
             ),
        rec
          AS (SELECT  name
                     ,step_name
                     ,step_id
                     ,0 step_seq
                     ,RunTime
                     ,[Status]
                     ,Duration
                     ,row
                     ,msg
                     ,ROW_NUMBER() OVER (ORDER BY row) instance
              FROM    x
              WHERE   step_id = 0 OR instance_id IN (SELECT MAX(instance_id) FROM x GROUP BY name)
              UNION ALL
              SELECT  rec.name
                     ,x.step_name
                     ,x.step_id
                     ,CASE WHEN rec.step_id = x.step_id THEN rec.step_seq
                           ELSE rec.step_seq + 1
                      END
                     ,rec.RunTime
                     ,rec.[Status]
                     ,rec.Duration
                     ,x.row
                     ,CASE WHEN rec.step_id = x.step_id THEN x.msg+rec.msg
                           ELSE x.msg
                      END
                     ,rec.instance
              FROM    rec
              JOIN    x
                      ON rec.row + 1 = x.row
                         AND x.step_id <> 0
             )
  SELECT  ROW_NUMBER() OVER (ORDER BY row) Seq
         ,name [Job Name]
         ,step_name [Step Name]
         ,step_id [Step]
         ,[Status]
         ,RunTime
         ,Duration
         ,CAST('<?M'+msg+'?>' AS XML) [Message]  -- This trick makes viewing the message easier.
  FROM    rec
  WHERE   row IN (SELECT  MAX(row)
                  FROM    rec
                  GROUP BY rec.instance
                         ,rec.step_seq);
go
SELECT  *
FROM    GetJobStatusMessages('mis_main', NULL);

T-SQL date formulas

There is a more thorough version for PowerShell here:
https://rickbielawski.wordpress.com/2009/03/13/adding-lots-of-useful-datetime-functions/
This shows how easy it is to do in T-SQL.

-- =============================================
-- Author:      Rick Bielawski
-- Create date: 2008/05/13
-- Description: Truncates a date/time to a particular date-part.
--              NOTE - THIS IS TRUNCATION, NOT DATEPART !
--              For example:
--              select dbo.Trunc_DateTime('12/31/1999', 1,0) --is 01/01/1999 (year)
--              select dbo.Trunc_DateTime('12/31/1999', 2,0) --is 12/01/1999 (month)
--              select dbo.Trunc_DateTime('12/31/1999',12,0) --is 10/01/1999 (quarter)
-- @DateTime    is the value to be truncated.
-- @Part        is the part of the date to truncate to.
--              1 - year
--              2 - month
--              3 - day
--              4 - hour
--              5 - minute
--              6 - second
--           Parts of a year
--             10 - Same as 1 - Start of year
--             11 - Half (Jan,Jul)
--             12 - Quarter (Jan,Apr,Jul,Oct)
--           Parts of a month
--             20 - Same as 2 - First day of month
--             21 - Half month (split on the 15th)
--           Parts of a day
--             30 - (Same as 3)
--             31 - Half day Midnight or Noon (12 hr period)
--             32 - Third of a day Midnight, 8:am, 4:pm
--           Parts of an hour
--             40 - (Same as 4)
--             41 - Half hour (:00 or :30 minute)
--             42 - Third hour (:00, :20, :40)
--             43 - Quarter hour (:00, :15, :30, :45)
--             44 - 10 minutes (:00, :10, :20, ...)
--           Parts of a week (most recent X day of week)
--             71 - Sunday
--             72 - Monday
--             73 - Tuesday
--             74 - Wednesday
--             75 - Thursday
--             76 - Friday
--             77 - Saturday
--           Versionable truncations
--            100 - Business day (NOT IMPLEMENTED)
-- @Offset      After truncation, adds the @offset number of parts indicated by @part.
--              For example:
--              select dbo.Trunc_DateTime('12/31/1999', 1, 2) --is 01/01/2001 (year)
--              select dbo.Trunc_DateTime('12/31/1999', 2, 2) --is 02/01/2000 (month)
--              select dbo.Trunc_DateTime('12/31/1999',12,-2) --is 04/01/1999 (quarter)
-- =============================================
ALTER FUNCTION [dbo].[Trunc_DateTime]
    (@DateTime datetime, @Part int, @Offset int)
RETURNS datetime
AS
BEGIN
  -- The @base date was selected as base because it is a Sunday
  -- and therefore makes computations based on day-of-week simpler.
  -- Calculations using the dateadd & datediff 'week' param only work
  -- correctly when the @base date is on the same day of the week as
  -- DATEFIRST which defaults to Sunday.  There are similar dependencies
  -- when using year or month.  Thus a year that starts on Sunday is apropos.
  Declare @base datetime = '20120101';

    RETURN CASE @Part
-- Basic parts of a date (year,month,day,hour,minute,second)
        WHEN 1  THEN DATEADD(year,    DATEDIFF(year,   @base,  @DateTime)+@Offset,        @base)
        WHEN 2  THEN DATEADD(month,   DATEDIFF(month,  @base,  @DateTime)+@Offset,        @base)
        WHEN 3  THEN DATEADD(day,     DATEDIFF(day,    @base,  @DateTime)+@Offset,        @base)
        WHEN 4  THEN DATEADD(hour,    DATEDIFF(hour,   @base,  @DateTime)+@Offset,        @base)
        WHEN 5  THEN DATEADD(minute,  DATEDIFF(minute, @base,  @DateTime)+@Offset,        @base)
        WHEN 6  THEN DATEADD(second,  DATEDIFF(second, @base,  @DateTime)+@Offset,        @base)
-- Parts of a year (yr-start,half, quarter)
        WHEN 10 THEN DATEADD(year,    DATEDIFF(year,   @base,  @DateTime)+@Offset,        @base)
        WHEN 11 THEN DATEADD(month, ((DATEDIFF(month,  @base,  @datetime)/ 6)+@Offset)* 6,@base)
        WHEN 12 THEN DATEADD(quarter, DATEDIFF(quarter,@base,  @DateTime)+@Offset,        @base)
-- Parts of a month (month start, half month(1st or 15th))
        WHEN 20 THEN DATEADD(month,   DATEDIFF(month,  @base,  @DateTime)+@Offset,        @base)
        WHEN 21 THEN DATEADD(month,   DATEDIFF(month,  @base,  @DateTime)
					+ ((DAY(@DateTime)+1)/17 + @Offset + sign(@Offset-abs(@Offset)))/2,   @base)
				 + abs((DAY(@DateTime)+1)/17 + @Offset)%2*15
-- Parts of a day (start of day, half (noon or midnight), third(0,8am,4pm))
        WHEN 30 THEN DATEADD(day,     DATEDIFF(day,    @base,  @DateTime)+@Offset,        @base)
        WHEN 31 THEN DATEADD(hour,  ((DATEDIFF(hour,   @base,  @DateTime)/12)+@Offset)*12,@base)
        WHEN 32 THEN DATEADD(hour,  ((DATEDIFF(hour,   @base,  @DateTime)/ 8)+@Offset)* 8,@base)
-- Parts of an hour (current hour, half hr, 1/4 hr, 10 min)
        WHEN 40 THEN DATEADD(hour,    DATEDIFF(hour,   @base,  @DateTime)+@Offset,        @base)
        WHEN 41 THEN DATEADD(minute,((DATEDIFF(minute, @base,  @DateTime)/30)+@Offset)*30,@base)
        WHEN 42 THEN DATEADD(minute,((DATEDIFF(minute, @base,  @DateTime)/20)+@Offset)*20,@base)
        WHEN 43 THEN DATEADD(minute,((DATEDIFF(minute, @base,  @DateTime)/15)+@Offset)*15,@base)
        WHEN 44 THEN DATEADD(minute,((DATEDIFF(minute, @base,  @DateTime)/10)+@Offset)*10,@base)
-- Parts of a week (Week,Sun,Mon,Tues,Wed,Thu,Fri,Sat)
        WHEN 71 THEN DATEADD(day,   ((DATEDIFF(day,    @base,  @datetime)/ 7)+@Offset)* 7,@base  )
        WHEN 72 THEN DATEADD(day,   ((DATEDIFF(day,    @base+1,@datetime)/ 7)+@Offset)* 7,@base+1)
        WHEN 73 THEN DATEADD(day,   ((DATEDIFF(day,    @base+2,@datetime)/ 7)+@Offset)* 7,@base+2)
        WHEN 74 THEN DATEADD(day,   ((DATEDIFF(day,    @base+3,@datetime)/ 7)+@Offset)* 7,@base+3)
        WHEN 75 THEN DATEADD(day,   ((DATEDIFF(day,    @base+4,@datetime)/ 7)+@Offset)* 7,@base+4)
        WHEN 76 THEN DATEADD(day,   ((DATEDIFF(day,    @base+5,@datetime)/ 7)+@Offset)* 7,@base+5)
        WHEN 77 THEN DATEADD(day,   ((DATEDIFF(day,    @base+6,@datetime)/ 7)+@Offset)* 7,@base+6)
-- Return same value as that passed if invalid conversion value is passed
                ELSE @DateTime
       END;
END
GO
----------------------------------------------------------------- These test the function.
declare @DateTime datetime
--set @DateTime = '2009/11/16 22:47:31.562'
--set @DateTime = '2009/05/16 10:27:31.562'
set @DateTime = '2009/02/15 10:27:31.562'
select  0,dbo.Trunc_DateTime(@DateTime, 0,1),dbo.Trunc_DateTime(@DateTime, 0,0),dbo.Trunc_DateTime(@DateTime, 0,-1),'Test-Date  ' union all
select  1,dbo.Trunc_DateTime(@DateTime, 1,1),dbo.Trunc_DateTime(@DateTime, 1,0),dbo.Trunc_DateTime(@DateTime, 1,-1),'Year       ' union all
select  2,dbo.Trunc_DateTime(@DateTime, 2,1),dbo.Trunc_DateTime(@DateTime, 2,0),dbo.Trunc_DateTime(@DateTime, 2,-1),'Month      ' union all
select  3,dbo.Trunc_DateTime(@DateTime, 3,1),dbo.Trunc_DateTime(@DateTime, 3,0),dbo.Trunc_DateTime(@DateTime, 3,-1),'Day        ' union all
select  4,dbo.Trunc_DateTime(@DateTime, 4,1),dbo.Trunc_DateTime(@DateTime, 4,0),dbo.Trunc_DateTime(@DateTime, 4,-1),'Hour       ' union all
select  5,dbo.Trunc_DateTime(@DateTime, 5,1),dbo.Trunc_DateTime(@DateTime, 5,0),dbo.Trunc_DateTime(@DateTime, 5,-1),'Minute     ' union all
select  6,dbo.Trunc_DateTime(@DateTime, 6,1),dbo.Trunc_DateTime(@DateTime, 6,0),dbo.Trunc_DateTime(@DateTime, 6,-1),'Second     ' union all
select 10,dbo.Trunc_DateTime(@DateTime,10,1),dbo.Trunc_DateTime(@DateTime,10,0),dbo.Trunc_DateTime(@DateTime,10,-1),'Year       ' union all
select 11,dbo.Trunc_DateTime(@DateTime,11,1),dbo.Trunc_DateTime(@DateTime,11,0),dbo.Trunc_DateTime(@DateTime,11,-1),'1/2 yr     ' union all
select 12,dbo.Trunc_DateTime(@DateTime,12,1),dbo.Trunc_DateTime(@DateTime,12,0),dbo.Trunc_DateTime(@DateTime,12,-1),'1/4 yr     ' union all
select 20,dbo.Trunc_DateTime(@DateTime,20,1),dbo.Trunc_DateTime(@DateTime,20,0),dbo.Trunc_DateTime(@DateTime,20,-1),'Month      ' union all
select 21,dbo.Trunc_DateTime(@DateTime,21,1),dbo.Trunc_DateTime(@DateTime,21,0),dbo.Trunc_DateTime(@DateTime,21,-1),'Half Month ' union all
select 30,dbo.Trunc_DateTime(@DateTime,30,1),dbo.Trunc_DateTime(@DateTime,30,0),dbo.Trunc_DateTime(@DateTime,30,-1),'Day        ' union all
select 31,dbo.Trunc_DateTime(@DateTime,31,1),dbo.Trunc_DateTime(@DateTime,31,0),dbo.Trunc_DateTime(@DateTime,31,-1),'1/2 day  12' union all
select 32,dbo.Trunc_DateTime(@DateTime,32,1),dbo.Trunc_DateTime(@DateTime,32,0),dbo.Trunc_DateTime(@DateTime,32,-1),'1/3 day   8' union all
select 40,dbo.Trunc_DateTime(@DateTime,40,1),dbo.Trunc_DateTime(@DateTime,40,0),dbo.Trunc_DateTime(@DateTime,40,-1),'Hour       ' union all
select 41,dbo.Trunc_DateTime(@DateTime,41,1),dbo.Trunc_DateTime(@DateTime,41,0),dbo.Trunc_DateTime(@DateTime,41,-1),'1/2 hr   30' union all
select 42,dbo.Trunc_DateTime(@DateTime,42,1),dbo.Trunc_DateTime(@DateTime,42,0),dbo.Trunc_DateTime(@DateTime,42,-1),'1/3 hr   20' union all
select 43,dbo.Trunc_DateTime(@DateTime,43,1),dbo.Trunc_DateTime(@DateTime,43,0),dbo.Trunc_DateTime(@DateTime,43,-1),'1/4 hr   15' union all
select 44,dbo.Trunc_DateTime(@DateTime,44,1),dbo.Trunc_DateTime(@DateTime,44,0),dbo.Trunc_DateTime(@DateTime,44,-1),'1/6 hr   10' union all
select 71,dbo.Trunc_DateTime(@DateTime,71,1),dbo.Trunc_DateTime(@DateTime,71,0),dbo.Trunc_DateTime(@DateTime,71,-1),'Sunday     ' union all
select 72,dbo.Trunc_DateTime(@DateTime,72,1),dbo.Trunc_DateTime(@DateTime,72,0),dbo.Trunc_DateTime(@DateTime,72,-1),'Monday     ' union all
select 73,dbo.Trunc_DateTime(@DateTime,73,1),dbo.Trunc_DateTime(@DateTime,73,0),dbo.Trunc_DateTime(@DateTime,73,-1),'Tuesday    ' union all
select 74,dbo.Trunc_DateTime(@DateTime,74,1),dbo.Trunc_DateTime(@DateTime,74,0),dbo.Trunc_DateTime(@DateTime,74,-1),'Wednesday  ' union all
select 75,dbo.Trunc_DateTime(@DateTime,75,1),dbo.Trunc_DateTime(@DateTime,75,0),dbo.Trunc_DateTime(@DateTime,75,-1),'Thursday   ' union all
select 76,dbo.Trunc_DateTime(@DateTime,76,1),dbo.Trunc_DateTime(@DateTime,76,0),dbo.Trunc_DateTime(@DateTime,76,-1),'Friday     ' union all
select 77,dbo.Trunc_DateTime(@DateTime,77,1),dbo.Trunc_DateTime(@DateTime,77,0),dbo.Trunc_DateTime(@DateTime,77,-1),'Saturday   ' union all
select  7,dbo.Trunc_DateTime(@DateTime, 7,1),dbo.Trunc_DateTime(@DateTime, 7,0),dbo.Trunc_DateTime(@DateTime, 7,-1),'Test-Date  '
-----------------------------------------------------------------
GO
 

Quick function for producing numbers

You may want to avoid the disk space and un-cached I/O involved in using a numbers table. These techniques produce the number of numbers you want quick and easy.

Note that there is a limit to the number of rows this can return but depending on the table you use as a source the rows may be cached already.
In this case, with a clean install of 2008 (no tables defined yet), I can generate over 25M numbers.
By adding another param and a simple addition to the function’s query you can return any range.  For example “select n from Numbers(-32768, 32767)”.

-- Returns numbers from 0 up to (but not including) @COUNT 
-- A decent replacement for master.dbo.spt_values when you need more than 0-2047 
CREATE FUNCTION Numbers (@COUNT int) 
RETURNS TABLE AS 
RETURN  
   SELECT TOP (SELECT @COUNT) 
          ROW_NUMBER() OVER (ORDER BY (SELECT 1)) -1 AS N 
FROM master.sys.all_columns A, master.sys.all_columns B;

If you can’t read master.sys, typically for security reasons, you can use almost any table with lots of rows.  But you can also create numbers another way.  The following produces up to 100,000 numbers very quickly and is easily expandable.  In some circumstances this technique can be faster than a physical table.  The drawback is that the source is virtual and therefore never in cache but with no I/O ever needed it’s performance is reliable.

-- Returns numbers from 0 up to (but not including) @COUNT 
-- This tops out at 100,000. Adding N6 would yield 1,000,000 values etc. 
-- Adding more joins does slow the function just a bit so creating several 
-- and using the one with the capacity you need yields top performance. 
CREATE FUNCTION Numbers (@COUNT int)
RETURNS TABLE AS
RETURN
   WITH N(I)AS (
        SELECT 1 UNION ALL
        SELECT UNION ALL
        SELECT UNION ALL
        SELECT UNION ALL
        SELECT UNION ALL
        SELECT UNION ALL
        SELECT UNION ALL
        SELECT UNION ALL
        SELECT UNION ALL
        SELECT 10)
SELECT TOP (SELECT @COUNT)
       ROW_NUMBER() OVER (ORDER BY (SELECT 1)) -1 AS N 
FROM N N1, N N2, N N3, N N4, N N5; 

Update: Leave it to that genius Itzik Ben-Gan to have another/faster way.

CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)),
L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM L5)

SELECT TOP(@high – @low + 1) @low + rownum – 1 AS n
FROM Nums
ORDER BY rownum;

XSLT to transform Visual Studio Documentation to HTML

This isn’t really the greatest script but it does a fair job and makes a great starting point if you want to roll your own.

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <!--                Created by Rick Bielawski - published 2011-02-18                   -->
  <!--                             last updated 2011-06-05                               -->
  <!--  To display an XML documentation file generated by Visual Studio with a browser   -->
  <!--  add the line below immediately after the ?xml version line in your XML document  -->
  <!--  then open the XML file with your browser. Obviously it must point to THIS file.  -->
  <!--        <?xml-stylesheet href="visual-studio-xml-doc.xsl" type="text/xsl"?>        -->
  <!--  Or, use an XSLT generator with this code on your XML document to create html     -->
 
  <!-- ======================================================================
       Here we declare ancilary routines used to extract data
       ====================================================================== -->
  <!--  Replace a given string with another.  Basically this is only needed because long
        parameter lists don't wrap at a comma in HTML without a space being inserted.  -->
  <xsl:template name="tReplaceSubString">
    <xsl:param name="pText" />
    <xsl:param name="pLookFor" select="','" />
    <xsl:param name="pNewValue" select="' ,'" />
    <xsl:choose>
      <xsl:when test="contains($pText,$pLookFor)">
        <xsl:value-of select="substring-before($pText,$pLookFor)" />
        <xsl:value-of select="$pNewValue" />
        <xsl:call-template name="tReplaceSubString">
          <xsl:with-param name="pText"     select="substring-after($pText,$pLookFor)" />
          <xsl:with-param name="pLookFor"  select="$pLookFor" />
          <xsl:with-param name="pNewValue" select="$pNewValue" />
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="$pText" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
 
  <!--  Counting the number of levels in a member's name allows me to tell
        where the name of the entity being documented starts (after the last one).  -->
  <xsl:template name="tCountOccurances">
    <xsl:param name="pText" />
    <xsl:param name="pLookFor" select="'.'" />
    <xsl:param name="pCount" select="0" />
    <xsl:choose>
      <xsl:when test="contains($pText,$pLookFor)">
        <xsl:call-template name="tCountOccurances">
          <xsl:with-param name="pText"    select="substring-after($pText,$pLookFor)" />
          <xsl:with-param name="pLookFor" select="$pLookFor" />
          <xsl:with-param name="pCount"   select="$pCount + 1" />
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="$pCount" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
 
  <!--  Replaces the pCount instance of pLookFor with pSeparator.  Used to split a string.
        pCount=0 doesn't look for the pLookFor.  It just puts pSeperator at the beginning.
        pCount=1 splits at the 1st occurance, 2 splits at the 2nd occurance....
        Splitting on a count greater than the number of occurances puts pSeperator at the end.  -->
  <xsl:template name="tSplitAtInstance">
    <xsl:param name="pText" />
    <xsl:param name="pCount" select="0" />
    <xsl:param name="pLookFor" select="'.'" />
    <xsl:param name="pSeparator" select="'|'" />
    <xsl:param name="pStartEmpty" />
    <xsl:choose>
      <xsl:when test="$pCount &gt; 0 and contains($pText,$pLookFor)">
        <xsl:variable name="temp">
          <xsl:if test="$pStartEmpty = ''">
            <xsl:value-of select="substring-before($pText,$pLookFor)" />
          </xsl:if>
          <xsl:if test="$pStartEmpty != ''">
            <xsl:value-of select="concat($pStartEmpty,$pLookFor,substring-before($pText,$pLookFor))" />
          </xsl:if>
        </xsl:variable>
        <xsl:call-template name="tSplitAtInstance">
          <xsl:with-param name="pText"       select="substring-after($pText,$pLookFor)" />
          <xsl:with-param name="pCount"      select="$pCount - 1" />
          <xsl:with-param name="pLookFor"    select="$pLookFor" />
          <xsl:with-param name="pSeparator"  select="$pSeparator" />
          <xsl:with-param name="pStartEmpty" select="$temp" />
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="concat($pStartEmpty,$pSeparator,$pText)" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
 
  <!-- ======================================================================
       These format individual parts of the document.
       ====================================================================== -->
 
  <!--  This seems to be the only thing that can be done generically -->
  <xsl:template name="tElementName">
    <xsl:param name="pText" />
    <span class="ElementName"><xsl:value-of select="$pText" />: </span>
  </xsl:template>
 
  <!-- ======================================================================
 
       This is where actual transformation starts
 
       ====================================================================== -->
 
  <!--  Set a var with the name of the assembly for the documented project. -->
  <xsl:variable name="vAssembly">
    <xsl:value-of select="doc/assembly/name" />
  </xsl:variable>
 
  <!--  This creates the HTML document within which the XML content is formatted. -->
  <xsl:template match="doc">
    <HTML>
      <HEAD>
        <style type="text/css">                 
          h1 {font-weight:bold; text-decoration:underline; text-align:center}
          pre {margin-top:0; margin-bottom:0}
          table {margin-left:5mm; margin-top:0; margin-bottom:0}
          tr#FirstRow {font-size:1.2em; text-decoration:underline}
          td {padding-left:2em}
          
          .AttributeName {font-weight:bold; padding-right:1em; color:blue; font-family:"Monospace"}
          .ClassName {color:red}
          .CodeItem {font-family:"Monospace"}
          .ElementName {padding-right:5px; text-transform:capitalize; font-weight:bold; font-style:italic}
          .EntityName {margin-left:2em; font-weight:bold; font-size:1.2em; font-family:"Monospace"}
          .MemberTypeName {color:blue}
 
          .summary {margin-top:0.3em; margin-bottom:0.3em}
          .returns {margin-top:0.3em; margin-bottom:0.3em}
          .exception {margin-top:0.3em; margin-bottom:0.3em}
          .remarks {margin-top:0.3em; margin-bottom:0.3em}
        </style>
        <TITLE><xsl:value-of select="$vAssembly" /> documentation</TITLE>
      </HEAD>
      <BODY>
        <h1><xsl:value-of select="$vAssembly" /> documentation</h1><p />
        <xsl:apply-templates select="members/member" />
      </BODY>
    </HTML>
  </xsl:template>
 
  <!--  This template first extracts information about the Member being formatted   -->
  <!--  Then formats the individual doc sections: summary, param, return...   -->
  <xsl:template match="member">
 
    <!-- I decided to use the <para> tag to flag the entity as public.  This way I can
         document internal stuff for intellisense but not for users.  It works well
         since intellisense ignores <para> formatting anyway.  -->
    <xsl:if test="count(summary/para) > 0">
 
      <!--  The whole name has the type, namespace, class name, and member name.   -->
      <xsl:variable name="vWholeName">
        <xsl:value-of select="@name" />
      </xsl:variable>
 
      <!--  The type of member being documented is encoded in the first character   -->
      <xsl:variable name="vMemberType">
        <xsl:value-of select="substring-before($vWholeName,':')" />
      </xsl:variable>
      <xsl:variable name="vMemberTypeName">
        <xsl:choose>
          <xsl:when test="$vMemberType = 'E'">Event</xsl:when>
          <xsl:when test="$vMemberType = 'F'">Field</xsl:when>
          <xsl:when test="$vMemberType = 'M'">Method</xsl:when>
          <xsl:when test="$vMemberType = 'N'">NameSpace</xsl:when>
          <xsl:when test="$vMemberType = 'P'">Property</xsl:when>
          <xsl:when test="$vMemberType = 'T'">Type</xsl:when>
          <xsl:otherwise>Unknown</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
 
      <!--  We need the number of name parts to get the last part (the name of the entity).
            Unfortunately there might be periods in parameter data types so we must count
            only those that come before any open paren.  -->
      <xsl:variable name="vNumPeriods">
        <xsl:variable name="temp">
          <xsl:choose>
            <xsl:when test="contains($vWholeName,'(')">
              <xsl:value-of select="substring-before($vWholeName,'(')" />
            </xsl:when>
            <xsl:otherwise>
              <xsl:value-of select="$vWholeName" />
            </xsl:otherwise>
          </xsl:choose>
        </xsl:variable>
        <xsl:call-template name="tCountOccurances">
          <xsl:with-param name="pText" select="$temp" />
          <xsl:with-param name="pLookFor" select="'.'" />
        </xsl:call-template>
      </xsl:variable>
 
      <!--  Split the full name between entity name (and any args) and the path (ns,class...)  -->
      <xsl:variable name="vSplitName">
        <xsl:call-template name="tSplitAtInstance">
          <xsl:with-param name="pText"      select="$vWholeName" />
          <xsl:with-param name="pCount"     select="$vNumPeriods" />
          <xsl:with-param name="pLookFor"   select="'.'" />
          <xsl:with-param name="pSeparator" select="'|'" />
        </xsl:call-template>
      </xsl:variable>
 
      <!--  Get the entity name (and any args).
            Add space to commas so browsers can wrap them if needed  -->
      <xsl:variable name="vEntityName">
        <xsl:call-template name="tReplaceSubString">
          <xsl:with-param name="pText"     select="substring-after($vSplitName,'|')" />
          <xsl:with-param name="pLookFor"  select="','" />
          <xsl:with-param name="pNewValue" select="', '" />
        </xsl:call-template>
      </xsl:variable>
 
      <!--  We'll assume the assembly name is also the prime Namespace name and omit it.
            The 'class' name might also include any sub-namespace names.  -->
      <xsl:variable name="vClass">
        <xsl:value-of select="substring-after(substring-before($vSplitName,'|'),'.')" />
      </xsl:variable>
 
      <!--  Variables are populated.  This formats member data on the page.  -->
      <xsl:if test="not($vClass='')">
        <span class="ClassName">
          <xsl:value-of select="$vClass" /> -
        </span>
      </xsl:if>
      <span class="MemberTypeName">
        <xsl:value-of select="$vMemberTypeName" />
      </span>:
      <div class="EntityName">
        <xsl:value-of select="$vEntityName" />
        <xsl:if test="$vMemberType = 'M' and not(contains($vWholeName,'('))">()</xsl:if>
      </div>
      <!-- And now, report all the sub-elements -->
      <xsl:apply-templates select="summary" />
      <xsl:apply-templates select="param" />
      <xsl:apply-templates select="returns" />
      <xsl:apply-templates select="interface" />
      <xsl:apply-templates select="exception" />
      <xsl:apply-templates select="example" />
      <xsl:apply-templates select="remarks" />
      <hr />
    </xsl:if>
  </xsl:template>
 
  <!-- These format the primary documentation elements -->
 
  <xsl:template match="summary">
    <div class="summary">
      <xsl:call-template name="tElementName">
        <xsl:with-param name="pText" select="name()" />
      </xsl:call-template>
      <xsl:apply-templates select="node()" />
    </div>
  </xsl:template>
  <xsl:template match="param">
    <div class="param">
      <xsl:call-template name="tElementName">
        <xsl:with-param name="pText" select="name()" />
      </xsl:call-template>
      <span class="AttributeName"><xsl:value-of select="@name" /></span>
      <xsl:apply-templates select="node()" />
    </div>
  </xsl:template>
  <xsl:template match="returns">
    <div class="returns">
      <xsl:call-template name="tElementName">
        <xsl:with-param name="pText" select="name()" />
      </xsl:call-template>
      <xsl:apply-templates select="node()" />
    </div>
  </xsl:template>
  <xsl:template match="interface">
    <div class="interface">
      <xsl:call-template name="tElementName">
        <xsl:with-param name="pText" select="name()" />
      </xsl:call-template>
      <span class="AttributeName"><xsl:value-of select="@name" /></span>
      <xsl:apply-templates select="node()" />
    </div>
  </xsl:template>
  <xsl:template match="exception">
    <div class="exception">
      <xsl:call-template name="tElementName">
        <xsl:with-param name="pText" select="name()" />
      </xsl:call-template>
      (<xsl:value-of select="substring-after(@cref,'.')" />)
      <xsl:apply-templates select="node()" />
    </div>
  </xsl:template>
  <xsl:template match="example">
    <div class="example">
      <xsl:call-template name="tElementName">
        <xsl:with-param name="pText" select="name()" />
      </xsl:call-template>
      <pre><xsl:value-of select="." /></pre>
    </div>
  </xsl:template>
  <xsl:template match="remarks">
    <div class="remarks">
      <xsl:call-template name="tElementName">
        <xsl:with-param name="pText" select="name()" />
      </xsl:call-template>
      <xsl:apply-templates select="node()" />
    </div>
  </xsl:template>
 
  <!-- Document elements can contain custom formatting elements.  -->
 
  <xsl:template match="para">
    <xsl:if test="not(.='')">
      <xsl:apply-templates select="node()" />
      <br />
    </xsl:if>
  </xsl:template>
 
  <xsl:template match="c">
    <span class="CodeItem">
      <xsl:value-of select="." />
    </span>
  </xsl:template>
 
  <xsl:template match="list">
    <table>
      <tr id="FirstRow">
        <td>Item</td>
        <td>Description</td>
      </tr>
      <xsl:apply-templates select="node()" />
    </table>
  </xsl:template>
  <xsl:template match="item">
    <tr><xsl:apply-templates select="node()" /></tr>
  </xsl:template>
  <xsl:template match="term">
    <td><xsl:value-of select="." /></td>
  </xsl:template>
  <xsl:template match="description">
    <td><xsl:value-of select="." /></td>
  </xsl:template>
 
</xsl:stylesheet>