SQL Script Problems With WiX

I’ve been doing a bit of work lately with the Windows Installer XML toolkit. Although the XML schema is a little daunting at first and the fact there doesn’t seem to be a decent UI written for it, it gets the job done and I guess thats what is most important.

BUT, and this is a big BUT, I am extremely disappointed with it’s SQL script execution support. It seems less than adequate. Its what I’ve been mainly working with the last month or so. I was tasked with writing a database build system application to build databases from SQL scripts and to build an MSI that will go through and run all the scripts. At first it seemed easy enough but I ran into two major problems.

(N.B. I was using WiX 2.0 for all of this as it was necessary to use a stable version)

Problem 1 – There is such a thing as too many SQL Scripts with WiX

Now this one I just couldn’t believe when I found it. It appears that if you place too many SQL scripts into a component in WiX you can cause it to kill itself. For whatever reason it just appears to hang at the ‘Creating SQL Database’ stage. You can easily mistake it for ‘thinking’ though I’ve left it over night and it doesn’t progress at all. Even if it is still actually ‘thinking’ this is just ridiculous. Granted I did have 1500+ SQL scripts to run but I don’t see any good reason as to why this would be a problem.

Solution

Thankfully there is a work around to this. What can be done is that you group your SQL scripts into separate components rather than placing them all into one component. This isn’t a major thing to do and I suppose its good practice to group them into multiple components anyway but it was still something that stumped me for a good while.

Problem 2 – Unselected Features containing SQL Scripts to run can cause problems

Now this problem is just retarded as far as I’m concerned. Feel free to correct me if I’ve totally misunderstood anything here but this explanation is all I can pull together from the absolute lack of documentation I could find on the subject.

Pretty much there is an action called InstallSqlData that runs your SQL scripts for you (correct me if I’m wrong, this is just my understanding from what I’ve read). Now I have no idea what the hell a MSI Handle is exactly but what I do know is that if an InstallSqlData action is created there will be an MSI Handle for it. The onus of closing the handle appears to be with the InstallSqlData action.

So here is the problem. If say you have a whole bunch of features and within each one of those features they run a SQL script. Even though you may not actually have selected that feature to be installed, for whatever reason it may be, the WiX MSI will create a handle for the InstallSqlData action. As these actions don’t get run these handles are closed and are just sort of left dangling. From observation and testing it appears that if 5 or more of these handles are left dangling the MSI will fail at the ‘Creating SQL Database’ stage and will just go straight to the final screen saying a problem as occurred and the installation couldn’t continue. 

To summarise, this means that if say you have 10 features, all of them with their own SQL script to run and you only choose to install 5 then you will have problems. If you choose to run 6 or more then you won’t have a problem. This just seems completely retarded to me.

Solution

I haven’t got one… seriously. I know that might just sound like me giving up (and I guess in a way it sort of is) though as far as I’m concerned there isn’t one. Don’t get me wrong, I have found something that sounds like a solution and I’ll provide details on it below but as far as I’m concerned it sounds more like a hack than a real solution and it bothers me that this is the only ‘solution’ I can find.

The solution I have found, but haven’t managed to get working myself, is to use the ‘NOT SKIPINSTALLSQLDATA’ condition. How you use this exactly I have no idea because all I’ve found out so far on the topic is that this is what you use – very little details on how exactly you use it or where in the .wixobj file to place it.

The biggest problem I have with the use of this solution is the fact it requires placing this condition into the .wixobj file after ‘compiling’ the .wxs file. Why is this a problem a person may ask, well its because it seems to me that the fact I have no way to place this condition in the .wxs file before ‘compiling’ it is the equivalent of me compiling my C++ program then having to go into the object file created and adding in a new variable. I WOULDN’T do that. I would write the new line into my source, re-compile it and then link it. There is no object file modification in between the ‘compiling’ and ‘linking’ stages. If the WiX toolkit insists on referring to its process as a ‘compiling’ and ‘linking’ process then it should act like one.

Conclusion

Now I know I’m probably going to get people disagreeing with me and saying “but thats how you work with WiX” and my only reply to those people is that WiX is a tool to get an MSI, end of story – nothing more, nothing less. I don’t want to have to spend weeks on end trying to ‘debug’ my .wxs/.wixobj file every time I want to create an MSI that packages resources that are slightly different to what most may usually package. I’ve already spent enough time developing the application I’m wanting to package so I don’t really think its a fair expectation that I then have even more complex and frustrating problems ahead of me just to create the MSI.

Of course I may be actually shooting the messenger here. The real problem may be with the MSI architecture and that is the reason for all these problems. Either way I’m not happy and I’m frustrated that it doesn’t work like I expect it to and I really don’t think my expectations are all that demanding.

One thought on “SQL Script Problems With WiX

  1. u cant close MSI Handle the Windows installer will do that for u, u can use it to do win32 API calls to Windows installer, i will recomend “the definitive guide to windows installer” about non wix matters.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s