How can I use WiX properties in a sql file executed from the installer?

Question

I have a WiX install project that I am rewriting based on our previous installers from Wise. In Wise, you can add files for it to execute (ours used sql script files) and it had a replace tab that specified certain text in the file to be replaced with either with static text or a Wise property.

I am wondering if this is possible in WiX?

Example: use [SQLDBNAME] SELECT * FROM Employee

Where WiX would read the file and replace the [SQLDBNAME] with the value set in WiX's properties table?

Answer 1

The Property replacement works for SqlString element, where you define the SQL string literally as a value of the attribute. However, it doesn't work for SqlScript element, where you put the entire SQL script file to Binary. It all depends on how complex is your SQL queries.

When using SqlString, don't forget that SQL uses [ and ] to escape its own reserved words. So you should escape the MSI properties like this in that case: [\[] and [\]].

Hope this helps.