Category Archives: SQL

Nesting the SQLExpress 2005 Install

Filed under Installations, SQL

I recently had the pleasure of trying to get SQLExpress 2005 to install, on request, during my application’s install, according to various parameters specified by the user during the UI portion of my install. Phew. Yeah, it’s hard enough to say, much less code.

Anyway, the basic problem is that you can’t nest MSI installs. And since an “MSI Install” is considered to be the portion of the install that runs during the EXECUTE sequence of the MSI logic, this means you can’t just execute SQLEXPR32.EXE at some point during the EXECUTE sequence of your install.

This is normally what you’d want to do because then, that action would happen regardless of whether your installation was running silently (ie no UI) or not.

Ok, that’s off the table. Hey, Microsoft! Believe it or not, nesting installs is something real world apps have to do in order to get all those nifty libraries of yours out there onto client machines! Sigh.

Fortunately, in my case (and in many others I’m guessing), not being able to run the SQL Express installer during a silent install is not a deal breaker. More than likely, if my installation is being rolled out silently, whoever’s rolling it out is using some sort of automated rollout tool to do it, and it’s likely they’ll just automate the rollout of SQLExpress as well.

But, what about when a user is simply trying to run my installation to get the product on their machine. In this situation, you want a no fuss, no muss process that has a user up and running with a little decision-making as possible. The last thing I want a user being prompted for is the instance name to use for the SQL Server that’s about to get installed. Sheesh.

Well, I can’t nest MSIs, technically, but I can execute SQLEXPR32.EXE from the UI sequence of the install.

Aha! Success! Except that, as well documented by any number of posts online, it doesn’t work consistently. Often, you’ll get an MSI error 110 indicating that SQL can’t access a file (usually sqlncli.msi) that it needs to complete the install.

            ******* CommandLine: **********
MSI (s) (18:00) [12:59:55:555]: Machine policy value 'DisableUserInstalls' is 0
MSI (s) (18:00) [12:59:55:575]: Note: 1: 1309 2: 5 3: C:\23661fc1e2705da3b45f5b05\setup\sqlncli.msi 
MSI (s) (18:00) [12:59:55:595]: MainEngineThread is returning 110
The system cannot open the device or file specified.

Trouble is, the file is there, exactly where the log says it isn’t.

The problem is clearly an issue with the SQLExpress installer. I can say that pretty confidently because you can nest all of SQL’s prerequisite installs just fine, including:

  • MSXML6.MSI (The XML 6.0 libraries)
  • SQLCNLI.MSI (SQL Native Client)
  • SQLServer2005_XMO.msi (SQL Server management objects installer)
  • And even VSTOR.EXE (the Visual Studio Tools for Office)

Just not the SQLEXPR32.EXE itself.

One possible solution that I found mentioned online was to extract all the SQLEXPR32.EXE files, then actually include them directly in your installation and at the end of your installation (in the UI sequence) launch the SETUP.EXE that starts the SQLExpress installation.

So I extracted them all:


Ouch. And I mean owwwwwwwwwchaaaaaa. 400+ files scattered over dozens of directories. The InstallShield Component Wizard choked trying to add them all as components and there was simply no way I was going to manually set up all that felgercarb in my install. Yeah, I went there<g>

Then it struck me. The extraction process (that /x command line arg) isn’t actually an msi install, so I should be able to nest it just fine. And if that’s the case, then I could extract all the files from SQLEXPR.EXE dynamically after installing the exe, and then fire up SQL’s setup.exe directly, just as I’d tried launching the SQLEXPR32.exe before (but that failed with the 110 error).

So, first, make sure you install the SQLEXPR32.EXE along with the rest of your application’s files.

Then, set up a Custom Action in your MSI project, in the EXECUTE sequence, that extracts all the files from SQLEXPR32.EXE to some folder (usually inside your INSTALLDIR somewhere):


(note the use of /q; that will keep the extraction process quiet, including suppressing a rather bothersome “Extraction process done” message box when it finishes, Also note that this adds substantially to your app footprint so you may need to accommodate that in available space calculations).

Next, create another Custom Action to execute the SQL Express SETUP.EXE that was extracted in the previous step.


Set this CA to execute towards the end of the UI sequence (because this is an MSI setup and this action can’t be started from the EXECUTE sequence of your installation, no getting around that one).

I set a command line of /qb to use the basic install ui for SQL Express, but you might also want to include other command line parameters, like:


or any of the other options you can specify. More info about the command line options for SQLEXPR32 are pretty easy to find online.

And finally, to keep your installation from looking like it just hung during the extraction part above, be sure to author a row in the ACTIONTEXT table for that custom action, and give it some text, like “Extracting SQL Server files…”. The text will show up over the progress bar that normally displays during an InstallShield-authored installation.

Let me know if this works for you, or if I’ve missed some esoteric combination in InstallShield that prevents it from working.

As always, YMMV.

Comparing MS SQL Database Structures and Data

Filed under Code Garage, SQL

If you’ve messed with SQL much, invariably, you get into a situation where you have to ask “What has changed in the database from the last version to this version?”

There’s some good tools out there for this, to be sure.

AdeptSQL is my favorite, but RedGate SQL Compare is very good. And DBGhost is highly regarded for synchronizing db schemas (and data to a degree, I believe).

But sometimes you want (or need) to “roll your own”.

So, how would you do that?

First, the criteria:

  • I need to be able to compare both schema and some “seed data” in certain tables (for instance, pre-populated lookup tables, etc)
  • I’d like to see any significant changes, but everyone’s definition of significant can be different.
  • I’d like the changes to be “easy” to read. They need to pop out if possible

Next, the display.

Well, there’s plenty of file comparison utilities out there, and my favorite is Araxis, so why not just use it to do the “delta-generation” heavy lifting.

I’ll need two text files to compare, the “before” version and the after.

I need them grouped by schema object type (stored-procs in one section, table definitions in another, data values in another, etc).

And I need them ordered consistently so that any changes are easy to spot.

I ended up a with a stored proc that, at least right now, does most of what I need.

The basic idea is that you call DBOutput, and it then calls itself recursively to resolve and output all the various elements of the database that you might be interested in comparing. Why a recursive procedure instead of several separate procs? No earth-shattering reasons. It’s just easier to deal with one proc for this sort of thing that several separate ones.

The routine itself pretty simply, and it doesn’t yet have to resort to directly reading the system tables for any information. It makes heavy use of the INFORMATION_SCHEMA views to retrieve most information. It should run under MSSQL 2000, MSDE, 2005 Express and 2005, but I haven’t tested it under the 2000 variants. 

Probably most notable is the use of sp_helptext to retrieve the actual full definition text of stored procs and views.

You’ll have to excuse the formatting, pasting into HTML seems to have messed up my nice tabs.

ALTER PROCEDURE [dbo].[DBOutput] (
    @TableName sysname = null,
	@SPName sysname = null,
	@ViewName sysname = null


DECLARE @Rows as int


if	len(IsNull(@TableName, '')) = 0 and 
	Len(IsNull(@SPName,'')) = 0 and 
	Len(IsNull(@ViewName,'')) = 0 BEGIN

    -- do all tables
    declare @Table sysname
	declare @Routine sysname
	declare @View sysname
    declare @i int
        select lower(Table_Name) from Information_Schema.Tables
        where table_type = 'base table' order by Table_Name
    OPEN cCursor
    FETCH NEXT FROM cCursor INTO @Table
        -- check for tables we don't care about
        if @Table in ('sysdiagrams'
            -- do nothing
            set @i = 0
        else if left(@Table,2) in ('ms', 'xx')
            set @i = 0
        else if left(@Table,3) in ('sys')
            set @i = 0
        else if left(@Table,6) in ('dtprop')
            set @i = 0
        else BEGIN
            exec DBOutput @Table, ''
        FETCH NEXT FROM cCursor INTO @Table

    CLOSE cCursor
    DEALLOCATE cCursor

	-- now Scan all the Stored Procs and functions
        select lower(ROUTINE_NAME) from Information_Schema.Routines order by ROUTINE_NAME
    OPEN rCursor
    FETCH NEXT FROM rCursor INTO @Routine
        -- check for Stored Procs and functions we don't care about
        if @Routine in (''
            -- do nothing
            set @i = 0
        else if left(@Routine, 3) in ('sp_', 'fn_')
            set @i = 0
        else BEGIN
            exec DBOutput '', @Routine
        FETCH NEXT FROM rCursor INTO @Routine

    CLOSE rCursor
    DEALLOCATE rCursor

	-- now Scan all the Stored Procs and functions
        select lower(TABLE_NAME) from Information_Schema.Views order By TABLE_NAME
    OPEN vCursor
    FETCH NEXT FROM vCursor INTO @View
        -- check for Stored Procs and functions we don't care about
        if @View in (''
            -- do nothing
            set @i = 0
        else if left(@View, 3) in ('sp_', 'fn_')
            set @i = 0
        else BEGIN
            exec DBOutput '', '', @View
        FETCH NEXT FROM vCursor INTO @View

    CLOSE vCursor
    DEALLOCATE vCursor


-- handle a single table recursively
IF Len(@TableName) > 0 BEGIN
	DECLARE @PrimaryKeyColumn nvarchar(64)
	DECLARE @sql nvarchar(1000)
	DECLARE @Parms nvarchar(1000)
	DECLARE @Temp sysname

	-- Primary key info (we're not using yet)
	   WHERE [TABLE_NAME] = @TableName

	-- dump a list of the columns, with pertinent info
	PRINT replicate('*', 60)
	PRINT replicate('*', 60)
	PRINT 'TABLE: ' + @TableName 
	EXEC ('SELECT ORDINAL_POSITION as ColNum, left(COLUMN_NAME, 20) as ColName, left(DATA_TYPE, 20) as DataType, Left(COLUMN_DEFAULT, 20) as DefaultValue, CHARACTER_MAXIMUM_LENGTH as Len, IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS WHERE Table_name = ''' + @TableName + ''' order by ColNum')

	-- dump a list of the indexes, with pertinent info
	-- also includes RI information associated with the keys (if any)
					left(Keys.CONSTRAINT_NAME,30) as IDXName, 
					left(Keys.COLUMN_NAME, 20) as ColumnName, 
					Left(RI.UNIQUE_CONSTRAINT_NAME, 20) as PrimaryKey, 
			FROM information_schema.Key_Column_Usage as Keys 
					left outer join information_schema.referential_constraints as ri on keys.Constraint_Name = ri.Constraint_Name 
					left outer join information_schema.check_constraints as chk on keys.Constraint_name = chk.Constraint_Name
			WHERE Table_name = ''' + @TableName + ''' 
			order by KeyNum, IDXName')

	-- check if there's any data in the table. If so, dump it all
	set @SQL = 'SELECT @Cnt = Count(*) FROM ' + @TableName
	set @parms = '@Cnt int OUTPUT'
	EXEC sp_executesql @SQL, @Parms, @Cnt = @Rows OUTPUT
	IF @Rows > 0 BEGIN
		PRINT replicate('~', 60)
		PRINT 'TABLE DATA FOR:' + @TableName
		EXEC ('SELECT * FROM ' + @TableName)

-- Handle all stored procs here
IF LEN(IsNull(@SPName, '')) > 0 BEGIN
	PRINT replicate('*', 60)
	PRINT replicate('*', 60)
	-- pull the entire text of the proc into a variable to let us print it nicely
	EXEC sp_helptext @SPName

-- Handle all Views
IF LEN(IsNull(@ViewName, '')) > 0 BEGIN
	PRINT replicate('*', 60)
	PRINT replicate('*', 60)
	PRINT 'VIEW: ' + @ViewName
	-- pull the entire text of the proc into a variable to let us print it nicely
	EXEC sp_helptext @ViewName


Basically, you just open up Enterprise Manager, and exec DBOutput. It’s best to put the results window in TEXT mode as opposed to grid mode. Then you can simply save the output to a text file and use your favorite file compare to get a delta. All the PRINTs are there just to make things a little more legible in a typical “text editor” view.

Let me know what you think, or if there’s additional bits to report that might be useful.

Also, I plan to update this script over time, and I’ll post the updates here.

SQL and Portability

Filed under Software Architecture, SQL

There’s an interesting discussion going on at SQL Server Central about applications and database portability.

The question thrown out was “How valuable is portability to your application”.

And by portability, they’re referring to portability across different database backends. Can your app run on Oracle, SQL Server, MySQL, etc, or do you just lock down to a specific vendor, and sell to your apps strengths and not DB portability?

It’s an interesting question and from the responses so far, it would seem that DB portability is a 4 letter word. But I think many of the responses are a little short sighted or limited to the DBA/developer perspective.

There is one comment saying something along the lines that an app this company purchased was DB agnostic and was found to contain no where clauses. Now that may have been an exageration to make a point, but I’d argue that an app like that was poorly architected from the outset. The fact that it performs poorly would seem to have less to do with being DB agnostic and more to do with just poor coding/architecture. My guess is, if you looked past the DB code in that app, you’d find a lot more to dislike as well.

The bottom line in any business is 1) The customer is always right and 2) you have to sell the product you have in order to make the product you want to sell.

Now, as to part 1, I’m not saying you can’t educate the customer, but in the end, if they really want Oracle as their backend, there may be some business reasons for that that you can’t sell around. And if the IT shop of that customer is centered on Oracle, good luck going in with a SQL Server based app.

I suppose it’d be nice if every shop was an IBM sized house that could hire DBAs specifically to design and support the backends for every reasonable DB, but most shops don’t have those kinds of resources.

In small shops, it’s all about leveraging code as much as possible.

SQL Express 2005 Command Line

Filed under Installations, SQL

We’re switching a major project over to SQL Express 2005 from MSDE 2000, and in the process of doing so, I came to several discoveries.

  • MS Isn’t releasing any MSI Merge Modules for 2005. Why? No idea. Appearently, that great MSI technology that they insist everyone else use isn’t good enough for their own products.
  • With InstallShield anyway, there is no prerequisite support for Express 2005 in IS 11.5 and earlier. You must move to InstallShield 12. Joy.

I did, however find this article about sqlexpr32 command line switches which should allow you to perform the Express install from within a normal MSI install by just running the exe with the proper switches. Not ideal, but it’s something.