Using Windows Installer Properties to Dynamically Replace Strings in SQL Scripts
InstallShield 2024
Project:
| • | Basic MSI |
| • | DIM |
| • | InstallScript MSI |
For Basic MSI, DIM, and InstallScript MSI projects, you can specify a replacement for text in a SQL script at run time through the use of Windows Installer properties. This enables you to let end users specify information that is then used in the SQL script that is launched on the target system. Windows Installer uses MsiFormatRecord to resolve the properties in the SQL script at run time.
Example
The following procedure demonstrates how to create a database at run time using a custom SQL script that contains information that end users enter on the built-in SQL login dialog: the SQLLogin dialog in a Basic MSI installation or the SQLServerSelectLogin2 dialog in an InstallScript MSI installation. Windows Installer properties are used for the database name and its target location.
To create a database using a SQL script that contains information that end users specify at run time:
| 1. | In the View List under Server Configuration, click SQL Scripts. |
| 2. | Right-click the SQL Scripts explorer and click New SQL Connection. InstallShield adds a new SQL connection. |
| 3. | Click the SQL connection, and then click the General tab. |
| 4. | Clear the Create Catalog If Absent check box. |
| 5. | In the SQL Scripts explorer, right-click the new connection and click New Script. InstallShield adds a new SQL script to the SQL connection. |
| 6. | Click the SQL script, and then click the Script tab. |
| 7. | In the script pane, enter the following: |
if not exists(select name from master.dbo.sysdatabases where name = '%DBNAME%')
begin
CREATE DATABASE %DBNAME%
ON
( NAME = %DBNAME%_dat,
FILENAME = '%DBPATH%%DBNAME%dat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = %DBNAME%_log,
FILENAME = '%DBPATH%%DBNAME%log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;
end
GO
| 8. | Configure two text replacements for the SQL script: |
| a. | Click the Text Replacement tab, and then click the Add button. The Find and Replace dialog box opens. |
| b. | In the Find What box, type the following: |
%DBNAME%
| c. | In the Replace What box, type the following: |
[IS_SQLSERVER_DATABASE]
| d. | Click OK. The Find and Replace dialog box closes. |
| e. | Click the Add button. The Find and Replace dialog box opens. |
| f. | In the Find What box, type the following: |
%DBPATH%
| g. | In the Replace What box, type the following: |
[INSTALLDIR]
| h. | Click OK. The Find and Replace dialog box closes. |
| 9. | Click the Runtime tab. |
| 10. | Clear the Run Script During Install check box and select the Run Script During Login check box. |
| 11. | Build your release. |
See Also
MsiFormatRecord (Windows Installer Help Library)
Working with Windows Installer and Advanced UI or Suite/Advanced UI Properties
Working with Dialogs in Basic MSI Projects
Working with Dialogs in InstallScript and InstallScript MSI Projects