Category Archives: sql server

Web Application installations with Wix…. Awesome

This week I set off with the goal of making one of our web applications alot easier to deploy. The tools I chose were –

  1. Web Application Installer (WAI)
  2. Wix (Windows Installer XML)
  3. WixEdit

I’d played with all three before but never really got past prototype stage. I’ve spent the last day learning wix and all the elements relevant to me. The Web Application Installer is collection of scripts and a template for different types of web applications. Firstly you use WAI to generate a list of files to install and the use WixEdit to edit that list and many other properties of the wix installation. Once happy with the configuration of the installation WixEdit will generate an msi for installation on your target machine. They truly are an excellent collection of tools. I now have an msi installer that sets up a web site in IIS, sets the ASP.NET version to 2, sets all the required permissions, creates a DB in SQL Server Express (specifying where to save the mdf and ldf files too) and changes the connection string in web.config accordingly. Todays quota of job satisfaction has now been achieved – saving about an hour per install (I’ve to install this app 28 times for different clients so the work was definitely worth while).

One huge pit fall which made me silly amounts of angry was that the SQL script you use to generate the tables and initial data for your database MUST be saved in Unicode (UTF-8). If it’s not then wix won’t be able to read it. I lost 3 hours yesterday with this!!!! as SQL server management studio express by default saves to UCS-2 Big Endian. Very little documentation on this fact so hopefully this post will help a little.

MySQL stored procedure variables

A few months ago we wrote a mammoth we application which had an SQL Express DB. We used stored procedures, and after writing 170 of them(I didn’t write all of them of course), I was quite used to the way that variables and so on worked in SQL Server.

We’ve started a new project where the DB is MySQL 5. Again we are using stored procedures, but variables work differently.

Setting a variable in SQL Server

DECLARE @oldPageGUID uniqueidentifier;
SET @oldPageGUID = (SELECT mPage.id FROM mPage WHERE mPage.title = @passedtitle);

Setting a variable in MySQL Server

DECLARE dateAdded TIMESTAMP;
SELECT lusers.dateadded INTO dateAdded FROM lusers WHERE lusers.id= id;

You can see that setting the value of the variable is done completely differently (MySQL Docs for SELECT … INTO Statement). Two other things that confused me for a while was that there is no character prefixed to the variables in MySQL as in SQL server (@) and that there is no uniqueidentifier data-type for GUIDs