How to use a custom property in a SQL script

SQL scripts cannot contain installer properties because they would be parsed incorrectly when added in an Advanced Installer project. However, properties can be used inside the script by using the SQL Script Replacement Dialog feature.

Let's suppose you need to attach a database and you are using this script:

CREATE DATABASE [database_name] ON
  ( FILENAME = N'C:\Program Files\My Company\My Application\database.mdf' ),
  ( FILENAME = N'C:\Program Files\My Company\My Application\database.ldf' )
FOR ATTACH ;

But you want to use the installation folder (the "APPDIR" property) for the database path instead of a hard-coded path. For this you can try following these steps:

1. when editing the script use a custom identifier which will be replaced by the property, for example:

CREATE DATABASE [database_name] ON
  ( FILENAME = N'$Install_Path$database.mdf' ),
  ( FILENAME = N'$Install_Path$database.ldf' )
FOR ATTACH ;

2. follow the Using SQL Scripts tutorial to configure your SQL connection

3. add your script file as an SQL script in the project

4. in the "Script Replacements" section of the SQL Script Tab pane add a new replacement

5. configure it to find $Install_Path$ (the custom identifier) and replace it with [APPDIR]

6. build and run the package to perform the replacement

After the package is built, the script inside the MSI will use "[APPDIR]" instead of "$Install_Path$". When the script runs, "[APPDIR]" will be resolved automatically to its value.

This method can be used for any public installer property.