Best Practice for Database Upgrade Patch

[RonanE]

Hi,
I've used installshield 2011 to create a db installation for our system.
What I'd like to do now is create a patch to upgrade the schema of an old release to a current version. I can see from searching the KB and forums that quick patch doesn't support this.

Can anyone give me an example of best practice for upgrading a schema using an SQL script?

What I'd like to do is have the patch check for Version X of the product and only run script Y if the DB product is at version X

Thanks,
Ronan

[RonanE]

Was looking into this and saw this it might be possible if I add components with SQL scripts into the installs.

Because we use SQL server database projects I can easily build upgrade scripts.
I was considering adding the upgrade scripts to our base install and marking the base install script with if not exists
Has anyone tried this approach?
Thanks

[RonanE]

Seeing as there were no replies I thought I might post this up so the next person can learn:

It seems you can version each script in your install with a schema version
Installshield creates a table in the DB with the column ISSchema
This records schema versions

However I have put two scripts in my installer as a test and each time the installer runs, the sql scripts are scheduled to run. My schema table looks like:
6.2.2001
6.2.2001
6.2.2002
6.2.2001
6.2.2002


one script has version 6.2.2001
the upgrade test script has version 6.2.2002
The first time I ran the install it inserted
6.2.2001
thereafter there were two scripts in the install and it inserted the following values twice as both scripts ran each time:
6.2.2001
6.2.2002


here's the windows installer log:

MSI (s) (60:64) [12:55:52:472]: Executing op: CustomActionSchedule(Action=ISSQLServerUninstall,ActionType=1025,Source=BinaryData,Target=ISSQLServerUninstall,CustomActionData=E:\TEMP\~2B10.tmp)
MSI (s) (60:A0) [12:55:52:476]: Invoking remote custom action. DLL: C:\Windows\Installer\MSI2DD1.tmp, Entrypoint: ISSQLServerUninstall
1: Processing the SQL connection 'NewSQLConnection1'...
1: Determining if there is at least one SQL script scheduled to execute under the connection...
1: The SQL script 'SqlScript.sql' is not scheduled to execute. Script Execution=RunOnInstall, Action=Uninstall, Component State=Install, Schema=
1: The SQL script 'CreateWasteMobile5DB.sql' is not scheduled to execute. Script Execution=RunOnInstall, Action=Uninstall, Component State=Install, Schema=6.2.2002
1: The SQL script 'AddNoteTypes.sql' is not scheduled to execute. Script Execution=RunOnInstall, Action=Uninstall, Component State=Install, Schema=
1: The SQL script 'UpgradeTest.sql' is not scheduled to execute. Script Execution=RunOnInstall, Action=Uninstall, Component State=Install, Schema=6.2.2002
1: No SQL scripts scheduled to execute under the connection...Skip.
1: Skip the SQL connection 'NewSQLConnection1'. There is no script to execute under this connection.

From above you'd expect that no sql scripts will be ran
However the scripts do run later during the install:

MSI (s) (60:64) [12:55:52:583]: Executing op: CustomActionSchedule(Action=ISSQLServerInstall,ActionType=1025,Source=BinaryData,Target=ISSQLServerInstall,CustomActionData=E:\TEMP\~2B10.tmp)
MSI (s) (60:58) [12:55:52:588]: Invoking remote custom action. DLL: C:\Windows\Installer\MSI2E3F.tmp, Entrypoint: ISSQLServerInstall
1: Processing the SQL connection 'NewSQLConnection1'...
1: Determining if there is at least one SQL script scheduled to execute under the connection...
1: The SQL script 'SqlScript.sql' is scheduled to execute. Note that the final decision will be made later if you have a schema version specified for this script. Script Execution=RunOnInstall, Action=Install, Component State=Install, Schema=
1: The SQL script 'CreateWasteMobile5DB.sql' is scheduled to execute. Note that the final decision will be made later if you have a schema version specified for this script. Script Execution=RunOnInstall, Action=Install, Component State=Install, Schema=6.2.2002
1: The SQL script 'AddNoteTypes.sql' is scheduled to execute. Note that the final decision will be made later if you have a schema version specified for this script. Script Execution=RunOnInstall, Action=Install, Component State=Install, Schema=
1: The SQL script 'UpgradeTest.sql' is scheduled to execute. Note that the final decision will be made later if you have a schema version specified for this script. Script Execution=RunOnInstall, Action=Install, Component State=Install, Schema=6.2.2002
1: Attempting to establish the SQL connection... Server: ., Database: master
1: Changed database context to 'master'.

Turned out that because my connection context was master installshield understandably couldnt' see the Installshield table and schema version
So I created a second connection to use my db context and this runs fine. Schema working perfectly now
Hopefully someone else will find this useful