SQL Frequently Asked Questions

When using PostgreSQL or MySql, if the script contains several statement separators it may execute without error outside the installer when running from a dedicated SQL editor.

This usually happens because some SQL dedicated editors can automatically parse different statement separators within the same script. As Advanced Installer does not support this, a solution is to define only one statement separator type for each SQL Script you add in the SQL Script Tab. The Statement Separator can be defined from the script's settings on the right pane.

For example, here's what you can do if you have a script that includes several small ones delimited with different statement separators:

• split the script into separate SQL files so each has only one separator type
• add each script in the SQL Databases Page under Database server item in the order you need them executed (top -> bottom)

This behavior is caused when connecting via SQL Server ODBC resource by an incorrect SqlConnectionString format.

The following new syntax for the Server keyword was recently introduced by Microsoft:

Server=Server[,port]

For example:

Driver=[\{]SQL Server[\}];Server=[SERVER_PROP];Port=[PORT_PROP];Uid=[USERNAME_PROP];Pwd=[PASSWORD_PROP];Database=[DB_PROP];

will become:

Driver=[\{]SQL Server[\}];Server=[SERVER_PROP],[PORT_PROP];Uid=[USERNAME_PROP];Pwd=[PASSWORD_PROP];Database=[DB_PROP];

To escape characters in an SQL connection string you must enclose them between { and } characters (e.g. To escape a password value like ;1234, the connection string should contain Pwd={;1234};. Since Advanced Installer uses formatted fields the syntax will become something like Pwd=[\{][PASSWORD_PROP][\}]; where PASSWORD_PROP public property will be dynamically resolved at runtime to ;1234.)

Starting with SQL Server 2008R2 SP2 (up to 2014) the SQL Server setup package cannot be installed as a Feature-based or Post-install software prerequisite due to a SQL Server setup problem. When added as a feature-based or post-install prerequisite the SQL Server installation hangs because the SQL Server setup it attempts to shut down the windows installer service (msiserver). You can read more about this limitation for MS SQL Server 2008R2 and MSSQL Server 2012.

Starting with the Microsoft SQL Server 2012 Service Pack 2 (SP2), the SQL Setup can be installed as a feature-based or post-install prerequisite.

The SkipInstallerRunCheck command line parameter needs to be passed to the SQL prerequisite through the Install Command Line section of the prerequisite setup tab in order to avoid the Windows Installer handle check of the SQL prerequisite.

For details, please check the SQL Server in a bootstrapping application article.

When using SQL Browse there are several setbacks you need to consider and prevent.

1. The SQL Browse feature enumerates SQL Server instances using a network broadcast (UDP port 1434). The related problems can include:

• UDP port 1434 might be blocked by the firewall on the server. This port is blocked by default thus prevents SQL servers discovery. The solution is to add a rule in your firewall which allows SQL browse service to communicate through port 1434.
• Instances that are not up and running do not respond to the discovery broadcast.
• Instances might respond after the timeout, which will exclude the response from the list.
• Instances might not be listening on the correct protocol, so will never receive the discovery broadcast.
• The HideInstance flag might be set for some instances.
• Network infrastructure, especially routers, might limit the effect/visibility of the broadcast since most routers are configured not to relay broadcast requests to the other side of the router.

2. SQL Server is installed as a prerequisite and the local browse doesn't discover instances. In this case, a computer restart can fix the issue because registry entries might not yet be flushed to disk.

3. SQL servers run on a domain controller and the firewall settings may need special configuration from the administrator.

4. The client computer is inside a domain and the computers outside the domain are not listed. This happens because SQL browse will only discover computers outside the domain which have SQL browse service running on them.