Monthly Archives: April 2011

Traipsing through MSI files with SuperOrca

1
Filed under Installations, Utilities

I’ve done a number of installs over the years (somehow, I seem to always end up with the installation project for whatever product I’m working on/with). It started way back when with a home grown solution, then on to a few minor products I can’t even recall now, then to Wise Installation System (back before Wise spoke MSI), then the little MSI setup project in Visual Studio, and eventually to InstallShield (oh how I miss Wise!).

MSI files are nice. They’re fairly well supported, powerful, capable.

But they can be god-awful difficult to get right sometimes.

And often, when things get tough, it’s often handy to be able to snoop directly into the MSI itself. Microsoft provides a tool called ORCA to do just that.

image

It’s decent. But, you can only get it via the SDK, which is a pretty hefty install. And it’s not great.

But then I happened upon a nifty little tool from the guys at Pantary.com, SuperOrca.

image

It’s a tiny download and fast install. It’s lightweight, but it’s got all the Orca goodness and then some, like an MSI compare function, and a nice search all function.

Now, InstallShield, and I’m guessing Wise also at this point, has a similar “raw MSI” mode, but, if you don’t have the scratch for those apps, SuperOrca might just be the ticket to shedding some light on your next vexing MSI problem.

Copying Default Constraints to Temporary Scratch (or “Staging”) Tables in SQL Server

5
Filed under SQL

A common pattern in SQL Server development is the use of “staging tables”. These are tables, usually temporary, that contain rows that are manipulated in several ways before being inserted into the “real” table, usually at the end of the entire process and in a transaction.

A very common way to create a staging table is to use SELECT INTO to create a temp table that is an exact replica of the target table, like so:

select top 0 * 
into #TargetTable 
from SourceTable

From there, you can insert new rows into the staging table, manipulate them, and eventually move them into the target table at one time.

However….

If the SourceTable used above happens to have non-nullable columns in it (and corresponding default constraints), the non-nullable attribute of the column will be replicated into the staging table, but the defaults won’t.

This can end up forcing your code to explicitly specify default values for those non-nullable columns. Not a particularly difficult task, but tedious, and error prone. Additionally, it introduces duplicate logic in your SQL (you’ve already set those default values in the definition of SourceTable, so replicating them in your stored proc code is not the greatest idea).

What you need is a way to copy the default constraints to the new staging table after you’ve created it.

And that’s exactly what CopyDefaults is for:

/****************************************************************

Used to copy defaults from a specified source table to a target 
table, usually a temp table created to replicate a particular
table, as in:

    select top 0 *
    into #NewTempTable
    from SourceTable

If the source table contains non-nullable columns that have defaults
the above will replicate the null-nullable attribute of the columns
but won't replicate the defaults, so you won't be able to insert
records into the new temp table without specifying values for all the
non-nullable columns.

Usage:

    exec CopyDefault 'SourceTable', 'TargetTable'
    
You may specify a full name for either source or target

    exec CopyDefaults 'db.dbo.SourceTable', 'otherdb.dbo.TargetTable'
 
****************************************************************/
ALTER PROCEDURE CopyDefaults
    @SourceTable varchar(256), 
    @TargetTable varchar(256)

AS

BEGIN


SET NOCOUNT ON

-- Parse the Database names and provide reasonable
-- defaults
declare @SourceDB varchar(128)
declare @TargetDB varchar(128)
select @SourceDB = parsename(@SourceTable, 3)
select @TargetDB = parsename(@TargetTable, 3)
if @SourceDB is null and left(@SourceTable, 1) = '#' begin
    select @SourceDB = 'tempdb' 
    select @SourceTable = 'tempdb..' + @SourceTable
end    
if @TargetDB is null and left(@TargetTable, 1) = '#' select @TargetDB = 'tempdb' 
if @SourceDB is null select @SourceDB = db_name()
if @TargetDB is null select @TargetDB = db_name()


declare @sql varchar(max)
select @sql=''

-- use a double indirected dynamic sql snippet to
-- create the defaults for the target table, based on those
-- from the Source table.
-- NOTE: the target should NOT already have any of the defaults 
-- established!
select @sql = 
'
declare @sql varchar(max)
select @sql=''''
select @sql=@sql + 
    ''IF  EXISTS (SELECT * FROM ' + @TargetDB + '.dbo.sysobjects WHERE name = '''''' + dc.name + ''xx'''' AND type = ''''D'''')
        ALTER TABLE ' + @TargetTable + ' DROP CONSTRAINT '' + dc.name + ''xx
    ALTER TABLE ' + @TargetTable + 
    ' ADD CONSTRAINT '' + dc.name + ''xx DEFAULT '' +
    replace(replace(definition, ''(('', ''(''), ''))'','')'') 
    + '' FOR '' + c.name + char(13) + char(10) 
FROM ' + @SourceDB + '.sys.default_constraints dc
INNER JOIN ' + @SourceDB + '.sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
where dc.type = ''D'' and OBJECT_ID(''' + @SourceTable + ''') = dc.parent_object_id
exec (@sql)' 

-- and execute the SQL 
exec (@sql)

END
GO

Usage is simple. Just create your Staging Table as described above:

select top 0 * 
into #TargetTable 
from SourceTable

Then execute CopyDefaults:

exec CopyDefaults 'SourceTable', '#TargetTable'

Now your defaults should be in place, so newly inserted rows in #TargetTable will automatically get the appropriate default values.

Finally, here’s a test script to exercise and verify the functionality.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON


/****************************************************************
Create a test Source Table 
****************************************************************/
if OBJECT_ID('tempdb..#SourceTable') is not null drop table #SourceTable
CREATE TABLE #SourceTable(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
    [Bit1] [bit] NOT NULL,
    [Bit2] [bit] NOT NULL,
    [Address] [varchar](50) NOT NULL, CONSTRAINT [PK_Test1] PRIMARY KEY CLUSTERED 
    (ID ASC)
)

ALTER TABLE #SourceTable ADD  CONSTRAINT DF_Test1_Bit1  DEFAULT 1 FOR Bit1
ALTER TABLE #SourceTable ADD  CONSTRAINT DF_Test1_Bit2  DEFAULT 0 FOR Bit2
ALTER TABLE #SourceTable ADD  CONSTRAINT DF_Test1_Address  DEFAULT '' FOR Address



/****************************************************************
Insert a sample record
****************************************************************/
insert into #SourceTable (Name) values ('TestName')


/****************************************************************
Select to prove the defaults were filled in
****************************************************************/
select * from #SourceTable


/****************************************************************
Replicate the source table
****************************************************************/
if OBJECT_ID('tempdb..#TargetTable') is not null drop table #TargetTable
select top 0 * into #TargetTable from #SourceTable


/****************************************************************
Try to insert and it will fail
****************************************************************/
BEGIN TRY
    insert into #TargetTable (Name) values ('TestName')
    print 'Insert into target succeeded (it shouldn''t have)'
END TRY
BEGIN CATCH
    print 'Insert into target failed (which is correct)'
END CATCH



/****************************************************************
Copy the default constraints over
****************************************************************/
exec CopyDefaults '#SourceTable', '#TargetTable'



/****************************************************************
And retry to insert and it should succeed now
****************************************************************/
BEGIN TRY
    insert into #TargetTable (Name) values ('TestName')
    print 'Insert into target succeeded (it should)'
END TRY
BEGIN CATCH
    print 'Insert into target failed (which should not have happened)'
END CATCH
select * from #TargetTable

Visual Studio 2010 Database Project is Slow

3
Filed under SQL

I’d read lots of good stuff about the “Data Dude” in VS, and 2010 was supposed to be even better, with improved support for roundtripping, version control etc.

Trying it out on a small little sample database showed real promise. It was snappy, searches were fast, version control worked nicely and the round tripping worked fairly seamlessly.

Then I tried it on a production database.

Ugh.

This particular server has 3 db’s, with a combined total of 9000+ objects (tables, functions, stored procs, etc). Not a gargantuan amount but quite sizable none-the-less.

The first hint that things were not looking good was this status bar.

DBProject-1

That number proceeded to tick down, by 10s and 100s, then suddenly jumped up to 90000+ and continued ticking down.

While it’s ticking, VS just drags. Badly. Menus take 10+ seconds to pop up. Code editing is unbearable.

Alas, it looks like all is not milk and honey in VS Datadude land just yet.

Back to Toad and SSMS.