InstallShield has many built-in functions to use with SQL Server and a SQL Scripts view where you can create connections and configure many properties for those connections.
The dialogs and functions that are provided allow you to log in, connect to a database, list all the installed instances, list all the databases, and do other things. They are all very helpful, but what if none of them satisfies your requirement?
I’m specifically talking about SQL Server version detection. You may have the need to know the installed versions and editions of SQL Server on a machine. Here are some of your choices:
- Check the Registry – Not a good choice. The registry and registry entries can change between different version of Windows and different versions of SQL Server. So checking the registry for certain entries is not something you can rely on.
- Borrow from Prerequisites – Another method to consider is to look at some of the SQL Server prerequisites and model a check after one of those. Each prerequisite has a list of conditions that must be satisfied in order to run (for the install to take place). However, this also relies partially on the registry, so we can’t use this one.
- Use WMI – WMI stands for Windows Management Instrumentation. It’s defined as the primary management technology for Microsoft® Windows® operating systems. This is the preferred method for determining SQL Server installed versions. In addition, Microsoft has stated that they will support WMI going forward.
To use WMI in InstallShield, you have several choices, depending on what type of project you are using. You could write a Managed Code Custom Action in C# or VB.NET to check and report the installed SQL versions.
You could also create a VBScript or JScript Custom Action that will get the installed versions. This is the method I have used and is the method I will talk about.
I created a VBScript Custom Action that uses WMI and gets the installed SQL Server versions and editions. It is best to first test your VBScript outside of InstallShield. Just run the standalone .vbs file and output some of the information to the screen.
Once that is working properly, place the VBScript code in the Custom Action. To return the results to your project, you will need to use Properties. Go to the Property Manager and define several properties to hold your results. For example, SQL_VERSION and SQL_EDITION. With these two properites you will be able to determine the versions and editions of SQL Server.
After the properties are defined, you can place the results in those properties. You do this with the Session variable. Here’s an example:
On the left side of the = sign is the Property name. On the right side is the script variable name. Here I am using the CStr() VBScript function which converts an expression to a string.
Now in the script, you could have variables set up for SQL Server 2005 and SQL Server 2008. It’s up to you. I am just illustrating the concept here.
I should also mention that WMI does not work for SQL Server 2000. So, if you have to determine SQL 2000 statistics, you will have to use another method. You might be surprised to know that many federal, state, and local governments are still using SQL Server 2000. They don’t look forward to changing over to a newer version. At least that’s the case in Washington state where I live.
In doing research on the net, you will find some posts discouraging you from using VBScript Custom Actions with InstallShield. If it’s a bad idea, then I did not find it to be the case in my particular example. All the testing I have done in this particular instance bears no problems. In other cases, it may be so.
I have limited experience with VBScript Custom Actions. What I do know is that VBScript error handling is horrid. There are no try/catch blocks like in C#. I do not use JScript, so it may be a better choice. Perhaps using managed code custom actions is the best choice. I am just relating what I did in one situation when I needed something done quickly.
If you know another way to do SQL Version checking in InstallShield, I would appreciate it if you would leave a comment so everyone will know.