SQL Scripts, Conditions and Schema Versions
Posted 10 December 2009 - 16:24
I have an Basic MSI package that contains several SQL scripts to be executed based on specific properties being defined. The condition on the SQL script in question is:
CBE_SETUPTYPE~="M" And CBE_DBCREATED~="NO"
So if the above condition equates to TRUE, the script should be executed.
But, this script also has a Schema Version defined as 0001.00000.0705 in the General section.
When this package runs it determines correctly that the CBE_SETUPTYPE is 'M' and that the database is created, so CBE_DBCREATED="YES". Therefore I would expect the SQL script with the condition above not to execute. But it does...
Checking SQL I noticed that the InstallShield table had a version of 0001.00000.0701. So if my SQL script did not have the condition defined and went strictly by the Schema Version, I would expect the script to run.
So I guess my question is, what takes precedence? If a condition exists on a SQL script and there is also a Schema Version added to the General section, what get's honored? In my case it looks like even though the condition evaluated to FALSE, the Schema Version in the InstallShield table was lower, so MSI went ahead and executed the script.
Would this be the expected behavior?
Posted 12 December 2009 - 16:14
The condition however is created as a component (or feature?) condition, which means that is evaluated during CostFinalize. So your property needs to be set at this time, which is before the dialogs are displayed.
Posted 01 February 2010 - 16:27
These properties are set prior to the CostFinalize action. This does not happen on all systems.
Here's another question...
If the initial 2.0 installer was deployed onto a system and the condition on the SQL script in question was not correct (i.e. it would allow the script to be executed regardless) and then I change the condition for a patch, would the patch replace the 'bad' condition and use the new condition? Or is the 'bad' condition always used during a patch and the only way to fix this would be to uninstall the initial 2.0 package and install a 'fixed' version?
Posted 21 July 2010 - 12:05
First off: The 'dbo.Installshield' table?
Where should this table be created. I've seen it created under our product database and I've seen it created under the SQL master database. The one that gets created under our product database ends up with multiple versions listed in it. For example:
When a patch runs and the table exists in our product database, all the old SQL scripts run because the schema table can't be found. If the table exists in the master database, then the SQL scripts run fine.
How does MSI determine where to create this table? Our SQL server name is the computer name (CBEWRBO), our DB name is (cbewrdb) and I do not have any Catalog Name defined in the SQL Connection Panel. All our upgrade SQL scripts contain 'use cbewrdb' at the top. It appears to create the table in our database, but then looks at the master database to find the schema table.
Also, should there be multiple entries in the Installshield table or should it contain just 1 record of the last version applied?
The Installshield documentation/help is very minimal around the use of Schema Versions, so I hope the community can shed some light on this.
Posted 22 July 2010 - 09:14
|if these are just string values, you'll need to write a custom action to perform the numerical comparison|
|How does MSI determine where to create this table?|
Posted 25 May 2012 - 15:28
Here we go again on this topic.
I have had fairly good success with the SQL scripts running the way they should, but now with our recent update, some sites are running the SQL scripts to upgrade the database and some sites are not. Looking at the log files for these 2 sites, the 2 update scripts on the sites that DID NOT WORK the component associated with the SQL scripts is marked as:
Installed: Local; Request: Local; Action: Absent
Where the ones that work are marked:
Installed: Local; Request: Local; Action: Local
The conditions on these scripts have remained the same since the initial release of the product. The only thing I change isthe schema version value.
What would cause MSI to mark this script as ABSENT on one site and LOCAL on another when both sites have been upgraded the same way up until this latest upgrade?
Posted 27 May 2012 - 17:39
Posted 28 May 2012 - 10:33
Going through and rechecking the condition, I noticed that one of the properties I was checking in the condition was not properly set.
When we release our product we generally send it to a couple of pilot sites. This was a pilot site and was a couple of builds behind the final release for the previous version.
This new release did not consider it a valid version to upgrade from and did not upgrade the database.
False alarm, but thanks for the reply Stefan. It made me recheck the properties.