ManniAT
Posts: 14
Joined: Tue Mar 26, 2013 10:59 am

Select / install SQL / Express

Hi,

in a .NET 4.5 Application I can check / install SQLExpress 2008 R2 with AI.

But my situation is a bit more complex and I don't know how to solve it.

First - nothing is installed - SQLExpress gets installed and I can use my (contained in XXX.exe.config) connection string ...connectionString="Data Source=.\sqlexpress;Initial Catalog...

Second - SQLExpress "older" is installed - so 2008 R2 gets installed with a different instance name so I have to change my XXX.exe.config to something like connectionString="Data Source=.\sqlexpr2;Initial Catalog

Third SQLExpress 2012 is installed -- can I change the Prerequisites form SQLExpress 2008 R2 to "SQLExpress => 2008 R2"?

What if the customer has installed a 2008 R2 Desktop edition (I guess this doesn't solve the SQLExpress ... prerequisite)?

Last not least (maybe needed) I can think of a situation where the customer has access to a "real SQL sever" either somewhere in the corporate network...


So my "idea" -- check for SQL Server 2008 R2 or larger native client.
If not found - install SQLExpress 2008 R2.
Else ask the user If he has access to a central server.
--If this is so - goto Step "Server select".
--else check for SQLExpress 2008 R2 and install if needed.
After this -
Server select - provide the SQL Native Client Server select and get the server / instance name from it.
--This must be run "as logged on user" to enable trusted connections.
Change the information in XXX.exe.config if needed.


A "simplier" way would also be OK - (no central server)
Install SQLExpress 2008 R2 if needed - where needed means - no desktop / express edition of SQL Server 2008 R" or newer.
Obtain the correct instance / server name and change XXX.exe.config if needed.

Can this be done with AI?

Manfred
Daniel
Posts: 8283
Joined: Mon Apr 02, 2012 1:11 pm
Contact: Website

Re: Select / install SQL / Express

Hello Manfred,
First - nothing is installed - SQLExpress gets installed and I can use my (contained in XXX.exe.config) connection string ...connectionString="Data Source=.\sqlexpress;Initial Catalog...
In order to achieve this you can add "SQL Server 2008 R2 Express" as a prerequisite and configure it as it is debated in our "MS SQL Server as prerequisite" thread.
Second - SQLExpress "older" is installed - so 2008 R2 gets installed with a different instance name so I have to change my XXX.exe.config to something like connectionString="Data Source=.\sqlexpr2;Initial Catalog
For this you can copy and then paste the above added "SQL Server 2008 R2 Express" prerequisite in the "Prerequisites" page. Then, in the "Install Conditions" tab replace the existing searches with a search string which will check if an instance of SQL Server is already installed or not. Also, you should change the .config file accordingly.
Third SQLExpress 2012 is installed -- can I change the Prerequisites form SQLExpress 2008 R2 to "SQLExpress => 2008 R2"?

What if the customer has installed a 2008 R2 Desktop edition (I guess this doesn't solve the SQLExpress ... prerequisite)?
I'm afraid I don't fully understand what you mean. Can you please give us more details about this (maybe exemplify)?
Last not least (maybe needed) I can think of a situation where the customer has access to a "real SQL sever" either somewhere in the corporate network...
Please take a look on our "How to browse for SQL Servers on the network?" article which should be useful for you.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube
ManniAT
Posts: 14
Joined: Tue Mar 26, 2013 10:59 am

Re: Select / install SQL / Express

Hi,

thank you for the answer and sorry for my English / confusing formulation (I'm not a native speaker).

Although you tried to explain the things - I couldn't find any answer for my questions.
There is no problem about installing SQLExpress in common - I already found how to do this.
I'll try to explain it with two simple samples (I will not dive deeper to the "exiting network server" section of my previous questions).

My Application (a .NET app) has an executable MyProg.exe and a config file MyProg.exe.config.
MyProg.exe.config is a .NET config file - simply a XML File which contains (next to other things) a connection string.
This file must be installed next to the executable.
So after a "normal" installation I'll have a folder "..\ProgramFiles\MyCompany\MyProg\" with MyProg.exe and MyProg.exe.config in it.

My Application needs SQL Express 2008 (minimum required version).
It can also work with SQL Express R2 or SQL Express 2012 or any newer version.

I assume (on a clean machine) that SQL Express is installed as MACHINE\SQLExpress (or .\SQLExpress).
So MyProg.exe.config has the connection string
-- ...connectionString="Data Source=.\sqlexpress;Initial Catalog=MyDB;...."

Now let's with some of the possible scenarios.
First - the Machine is clean.
I (AI) detect that SQL Express 2008 is not installed so I install it as default instance - no changes needed for MyProg.exe.config.
-- No problem / questions at all.

Scenario two -- SQL 2012 is installed as default instance (.\SQLExpress).
FIRST QUESTION - since there is no "Minimum required" in Prerequisites HOW CAN I FORMULATE
Program need SQL Express 2008 OR NEWER??

Scenario three - SQL Express 2005 is already installed as default instance.
My need here - install SQL Express 2008 AND CHANGE MyProg.exe.config to reflect the new instance name.
I can provide an SQL Express installation file which installs a defined instance.
SECOND QUESTION - How to handle this situation in AI.
The "human readable code" for this is
IF Wrong_SQL_Version_is_installed THEN install SQL Express with PARAMETERS TWO AND Change MyProg.exe.config to reflect this instance name.
-- It would also be possible to provide an extra version of MyProg.exe.config containing the correct instance name.


Scenario four - SQL Express "CORRECT VERSION" is installed but with a different instance name.
I assume there is an instance of SQL Express 2008 R2 installed with the instance name SQLX8R2.
THIRD QUESTION - how to retrieve and set the instance name in MyProg.exe.config

Manfred
ManniAT
Posts: 14
Joined: Tue Mar 26, 2013 10:59 am

Re: Select / install SQL / Express

About my questions above - I did a little research and found
a.) The prerequisite in AI for SQL Express 2008 is
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\100\Bootstrap\Setup\PatchLevel --- 10.0
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server Native Client 10.0\CurrentVersion\Version ---10.0
use if ALL conditions are false

First of all - this seems to be a wrong condition - I tried it on a machine with only native client installed (due to a central server).
SQL Express is NOT installed because of the detection of native client.


If I could do it "manual" check the registry the things would be very easy.
I search the registry for
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Installed Instance (Multi SZ)
In my case I find
SQLX8R2 (Epxress 2008 R2)
MSSQLSERVER (Desktop 2012)

Next for each of the strings check
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceNames\SQL\FOUND_INSTANCE_STRING
And with the result (FOUND_INSTANCE_NAME)
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\FOUND_INSTANCE_NAME\Setup\PatchLevel (Contains the version like 10.xxxx)

Next if the PatchLevel (version) fits my needs I can connect to
.\FOUND_INSTANCE_STRING - except when FOUND_INSTANCE_STRING is MSSQLSERVER - then I need to connect to . (no instance name).




Which leads to the question
Can I retrieve a value from the registry and use it as parameter to change my MyApp.exe.config file?

And next - can I prioritize / nest conditions?

Example
I have SQL Desktop 2012 (default instance / no instance name) and SQL Express 2008 R2 installed.

So I have the keys
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\100\Bootstrap\Setup\PatchLevel
AND
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\110\Bootstrap\Setup\PatchLevel

Conditions would be
IF I Find
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\110\Bootstrap\Setup\PatchLevel >= 11.0
USE
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\110\CollectorParameters as Instance Name
SET - Prerequisite FOUND

ELSE
IF I Find
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\100\Bootstrap\Setup\PatchLevel >= 10.0
USE
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\100\CollectorParameters as Instance Name
SET - Prerequisite FOUND

ELSE
Install SQL Express....

Manfred
Daniel
Posts: 8283
Joined: Mon Apr 02, 2012 1:11 pm
Contact: Website

Re: Select / install SQL / Express

Hello Manfred,
a.) The prerequisite in AI for SQL Express 2008 is
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\100\Bootstrap\Setup\PatchLevel --- 10.0
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server Native Client 10.0\CurrentVersion\Version ---10.0
use if ALL conditions are false

First of all - this seems to be a wrong condition - I tried it on a machine with only native client installed (due to a central server).
SQL Express is NOT installed because of the detection of native client.
Please keep in mind this is our default implementation for "SQL Server 2008 Express" prerequisite package. However, you can change the prerequisite install conditions accordingly to your requirements.
Which leads to the question
Can I retrieve a value from the registry and use it as parameter to change my MyApp.exe.config file?
For this you can add a registry search in "Search" page. The search result will be stored in the search name (e.g. MY_SEARCH) which is an installer property. Then, in "Files and Folders" page you should import your "MyApp.exe.config" file as an XML file and use the search property to set the desired XML attributes. Also, you can take a look on our "Import and edit a XML file" article which could be useful for you.
Example
I have SQL Desktop 2012 (default instance / no instance name) and SQL Express 2008 R2 installed.

So I have the keys
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\100\Bootstrap\Setup\PatchLevel
AND
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\110\Bootstrap\Setup\PatchLevel

Conditions would be
IF I Find
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\110\Bootstrap\Setup\PatchLevel >= 11.0
USE
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\110\CollectorParameters as Instance Name
SET - Prerequisite FOUND

ELSE
IF I Find
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\100\Bootstrap\Setup\PatchLevel >= 10.0
USE
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\100\CollectorParameters as Instance Name
SET - Prerequisite FOUND

ELSE
Install SQL Express....
For this you can try to add registry searches in "Search" page. Then, in "Custom Actions" page you can use "Set installer property" custom actions (added after "Wizard Dialogs Stage -> Searches" action group) which will set some custom properties to a specific value based on the values of the searches associated properties. Finally, you can add the "SQL Server 2008 Express" prerequisite as a feature-based prerequisite and, from "Organization" page you can can select the feature of the prerequisite and use a custom property (set based on the values of the searches associated properties) in the "Install based on Condition -> Condition" field from "Feature Properties" right pane.

As another workaround you can add the prerequisite as feature-based and, then try to use a "Search done by a custom executable" search type for your prerequisite package. In this way you can create your own custom EXE which will search based on your requirements if the prerequisites package should be installed and will set some installer properties with desired values (e.g. with the instance name). Next, you can use the above properties to edit your "MyApp.exe.config" file and install the prerequisite package.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube
ManniAT
Posts: 14
Joined: Tue Mar 26, 2013 10:59 am

Re: Select / install SQL / Express

Hi Daniel,

although things in AI look sometimes complicated - I know installing software isn't an easy thing.
Since I come from "the simple side" (VS installer projects) - I often ask "stupid questions".

The more I learn from AI the more I come to one conclusion - there is only one thing I find "not so good" - it is about VS Project imports.
-- it promises a bit more than it does ATM.
But everything else is very fine.

And - best of all - your support.
Every of my questions got an answer.
The answers came fast.
And (most important) - the answers are detailed and precise.

As every product also AI has room to make things a bit clearer (better / easier) - but from what I learned till now - It solves all my needs.

Thank you again
Manfred
Daniel
Posts: 8283
Joined: Mon Apr 02, 2012 1:11 pm
Contact: Website

Re: Select / install SQL / Express

You're always welcome Manfred. Thank you for your kind words.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

Return to “Building Installers”