... until the collector arrives ...

This "blog" is really just a scratchpad of mine. There is not much of general interest here. Most of the content is scribbled down "live" as I discover things I want to remember. I rarely go back to correct mistakes in older entries. You have been warned :)

2012-12-18

SQL Server Schema Differences

The following SQL statement will generate a crude list of column differences between two SQL Server databases:

DECLARE @oldDatabase NVARCHAR(MAX)
DECLARE @newDatabase NVARCHAR(MAX)

SET @oldDatabase = 'unittestnew_3_14_x'
SET @newDatabase = 'unittestnew_3_15_x'

DECLARE @sql NVARCHAR(MAX)
SET @sql = '
WITH
  oldColumns AS (
    SELECT
      tab.name AS tableName
    , ROW_NUMBER() OVER (PARTITION BY tab.name ORDER BY col.column_id) AS columnSeq
    , col.name AS columnName
    , typ.name AS type
    , col.max_length AS length
    , col.scale AS scale
    FROM ' + @oldDatabase + '.sys.all_columns AS col
    INNER JOIN ' + @oldDatabase + '.sys.all_objects AS tab
      ON tab.object_id = col.object_id
      AND tab.type = ''U''
    INNER JOIN ' + @oldDatabase + '.sys.schemas AS sch
      ON sch.schema_id = tab.schema_id
      AND sch.name = ''dbo''
    INNER JOIN ' + @oldDatabase + '.sys.systypes AS typ
      ON typ.xusertype = col.system_type_id
  )
, newColumns AS (
    SELECT
      tab.name AS tableName
    , ROW_NUMBER() OVER (PARTITION BY tab.name ORDER BY col.column_id) AS columnSeq
    , col.name AS columnName
    , typ.name AS type
    , col.max_length AS length
    , col.scale AS scale
    FROM ' + @newDatabase + '.sys.all_columns AS col
    INNER JOIN ' + @newDatabase + '.sys.all_objects AS tab
      ON tab.object_id = col.object_id
      AND tab.type = ''U''
    INNER JOIN ' + @newDatabase + '.sys.schemas AS sch
      ON sch.schema_id = tab.schema_id
      AND sch.name = ''dbo''
    INNER JOIN ' + @newDatabase + '.sys.systypes AS typ
      ON typ.xusertype = col.system_type_id
  )
, matched AS (
    SELECT
      COALESCE(old.tableName, new.tableName) AS tableName
    , old.columnSeq AS oldColumnSeq
    , old.columnName AS oldColumnName
    , old.type AS oldType
    , old.length AS oldLength
    , old.scale AS oldScale
    , new.columnSeq AS newColumnSeq
    , new.columnName AS newColumnName
    , new.type AS newType
    , new.length AS newLength
    , new.scale AS newScale
    FROM oldColumns AS old
    FULL OUTER JOIN newColumns AS new
      ON new.tableName = old.tableName
      AND new.columnName = old.columnName
  )
SELECT
  tableName
, oldColumnName
, oldType
, oldLength
, oldScale
, CASE
    WHEN oldColumnName IS NULL THEN ''added''
    WHEN newColumnName IS NULL THEN ''deleted''
    ELSE ''changed''
  END AS diff
, newColumnName
, newType
, newLength
, newScale
FROM matched
WHERE oldColumnName IS NULL
OR newColumnName IS NULL
OR oldColumnName <> newColumnName
OR oldType <> newType
OR oldLength <> newLength
OR oldScale <> newScale
ORDER BY tableName
, COALESCE(oldColumnSeq, newColumnSeq)
'

EXEC sp_executesql @sql

2012-12-13

Java vs. UTF-8 BOM

From time-to-time, one runs into Java software that has trouble handling a Unicode byte-order-mark (BOM) in a UTF8-encoded character stream. I've seen this, for example, in various XML processing pipelines. Most recently, I came across it in a character stream generated by a .NET component that was being consumed by a Java component. The problem is always the same -- the BOM is finding its way into the data stream instead of being silently consumed by the Java I/O infrastructure.

This is a known problem, listed as bug #4508058 on the Java bug parade. Oracle/Sun acknowledged the bug, and it was even briefly fixed in the "Mustang" (Java6) release. However, a follow-on bug report (#6378911) complained that the fix broke backwards compatibility with previous releases. So, the fix was ultimately withdrawn and the original bug was marked as "won't fix".

Bottom line: when writing Java components that consume UTF8-encoded character streams, be prepared to consume the BOM yourself. Also, be aware that the various Microsoft I/O frameworks aggressively write BOMs into UTF8 streams, even if the stream would be otherwise empty.

It seems to me that there is an opportunity here for Java to add an alternate method to construct UTF8-readers that handle BOMs properly.

2012-12-12

curl vs. Windows vs. esi.manage measure export

A self-contained Windows command file can use curl to invoke the esi.manage measure export integration service through judicious use of newline and quote escaping:

@ECHO OFF

SETLOCAL

SET server=127.0.0.1:8080

SET request=^
  -h ^
  -g PROJECT.NAME ^
  -c none        -m PRODUCTION -b GROSS -v FORECAST -U Oil:boe -D NONE        --curtailment ^
  -c scheduled   -m PRODUCTION -b GROSS -v FORECAST -U Oil:boe -D SCHEDULED   --curtailment ^
  -c unscheduled -m PRODUCTION -b GROSS -v FORECAST -U Oil:boe -D UNSCHEDULED --curtailment ^
  -c all         -m PRODUCTION -b GROSS -v FORECAST -U Oil:boe -D ALL         --curtailment ^
 2010-01-01 ^
 18 M ^
 \"origin subset\"

curl ^
  --ntlm ^
  --user : ^
  -X POST ^
  -H "content-type:text/plain" ^
  --data-binary "%request%" ^
  http://%server%/3esi/manage/integration/export

The use of the --data-binary option is important since -d or --data-ascii will reformat the data (e.g. strip newlines).

Blog Archive