Windows Installer – SQL Server Installation Issues and a Solution.
Posted on October 25, 2007
Filed Under .Net Code, VB.Net, Windows Deployment, Windows Development
Here is the problem in a nutshell: You have a custom client application that uses SQL Server 2005 Express. The server installation is a little bit to be desired. First, you have to install SQL Server Express, then you have to configure the options (unless you did command line options), then you have to execute the scripts to create your database, tables, and possibly populate it with some data. Or conversely, execute scripts or install and use the management console to attach a database. Messy, at best.
So what if you want to have a single installation program that will install all the prerequisites (.Net), then install SQL Server 2005 with your custom options (instance name for example), and then have it execute scripts and fill it with data? After working at it for hours, I came to the conclusion that you could be out of luck. Until I did some reconfiguring.
First things first, you need to get SQL Server Express to install properly. I struggled with this for a while, until I had the bright idea of using a bootstrapper… But can’t we use the one that comes with the Visual Studio Package and Deployment Wizard? Yes you can!
Go into your Visual Studio 8 folder and find the SDK/v2.0/Bootstrapper/Packages folder. Make a copy of the SqlExpress folder and name it something like, MyAppSqlExpress. Inside that folder, there is a product XML file. Edit that product.xml file and change the product code. Mine was Microsoft.Sql.Server.Express.1.0 and I changed it to MyAppName.Microsoft.Sql.Server.Express.1.0 … Just change the MyAppName to the app name of the product it goes with. You will also see a En folder for the English installation. The package.xml file is the one you want to edit in that folder. The first item to edit, is the arguments line. This passes arguments to the SqlExpr32.exe file (the setup for Sql Express). The arguments are the same as any other SQL Express install… So change it to be something like this:
Arguments='-q /norebootchk /qb reboot=ReallySuppress addlocal=all INSTANCENAME=MyApp SECURITYMODE=SQL SAPWD=MyStrongPassword DISABLENETWORKPROTOCOLS=0 SQLAUTOSTART=1'
This will do a silent installation of SQL Server Express using all protocols, a custom instance name (MyApp), turn on the SQL security mode and specify a strong SA password.
Next, there is the strings block at the bottom. Edit the StringName “DisplayName” to look like this:
MyApp SQL Server 2005 Express Edition
Save it, and go into your setup and deployment project you created, go to the project properties, then to the prerequisites, and in the list you should now see your custom install of SQL Server Express!
This does work, I just tested it out today.
So that solves problem #1. What about the problem of executing scripts? That seems to be the easy part… From my earlier post you can incorporate that code into a custom action.
Create a new project, and select Installer Class from the templates. This creates a new installer class for you to augment.
Private Sub ExecuteSQL(ByVal SQL As String, _ ByVal ConnectionString As String) Dim srv As New Server(New ServerConnection( _ New SqlClient.SqlConnection(ConnectionString))) srv.ConnectionContext.ExecuteNonQuery(SQL) srv.ConnectionContext.Disconnect() End Sub Private Sub BulkLoad(ByVal ConnectionString As String, _ ByVal TableName As String) Dim bulk As New SqlClient.SqlBulkCopy(ConnectionString, _ SqlClient.SqlBulkCopyOptions.KeepIdentity) Dim ds As New DataSet Dim path As String = My.Application.Info.DirectoryPath ds.ReadXmlSchema(path & "\" & TableName & ".xsd") ds.ReadXml(path & "\" & TableName & ".xml") bulk.BulkCopyTimeout = 0 bulk.DestinationTableName = TableName bulk.WriteToServer(ds.Tables(TableName)) bulk.Close() End Sub Public Overrides Sub Install(ByVal stateSaver As _ System.Collections.IDictionary) MyBase.Install(stateSaver) Dim ConnBuilder As New SqlClient.SqlConnectionStringBuilder() ConnBuilder.UserID = "sa" ConnBuilder.Password = "MyStrongPassword" ConnBuilder.DataSource = "localhost\MyApp" ConnBuilder.InitialCatalog = "master" ExecuteSQL(My.Resources.CreateDatabaseSQL, _ ConnBuilder.ConnectionString()) ConnBuilder.InitialCatalog = "MyDatabase" BulkLoad(ConnBuilder.ConnectionString(), "MyTable") End Sub
In this example, I just stored the data files (an XML data file and an XSD schema file) in the setup project, which gets installed along with the custom installer DLL. The create database SQL stuff was all saved as a store resource in the installer class project. I did this so that some of the structure was hidden, which may or may not be an issue for others.
Now, add the custom action to a setup project and you are finished!
Comments
34 Responses to “Windows Installer – SQL Server Installation Issues and a Solution.”
Leave a Reply
Hi,
Strange – I did just what you describe in step 1, and the new option does not show up in the list of Prerequisites. Why would that be?
It’s my first time dealing with incorporating SQL Express in the setup. Also, in my case I don’t want to make it a prerequisite for the client application. Rather, I want to install it just once on the “server”. So, I created an empty setup project with SQL Express is a prerequisite. Hope this works – not tested yet.
Thanks in advance if you have any suggestions,
Anatoly Molotkov, MCSD, MCPD
You know, I may have left out a step. Inside the folder for SqlExpress, there is a product XML file. In the NEW folder you copied this one from, change that product.XML and change the product code. Mine was Microsoft.Sql.Server.Express.1.0 and I changed it to MyAppName.Microsoft.Sql.Server.Express.1.0 … Just change the MyAppName to the app name of the product it goes with. I think the reason it’s not showing up for you is because that name was never changed.
That worked – thanks so much!
This is great, the only problem i have is that if i install my application on a machine that already has SQLEXPress installed it does not install my new instance. any ideas?
Benjamin,
I remember trying this on a machine that had a default instance of SQL Express installed, and I remember it working (because I was testing it on my dev machine which already had SQL Express from Visual Studio 2005). I went to try it again today, but the install will not work because my dev machine is now Vista x64 and I only ship out the 32-bit version of SQL Express at the moment. If I get a chance I will try to install it into a XP Pro virtual machine to test it out.
Aaron
I’ve follwed every step of this, altering the XML files to the right values, but my custom prerequisite is still not showing in the list in Visual Studio.
I’ve tried this 5 times now, and still can’t get the prerequisite to show up. Any ideas?
Andy
Hi again. You know, it’s strange, but I could never get this bootstrapper approach to work on my end. I am trying a silent installation, and it keeps showing all the SQL Server Express setup dialogs. Were you actually able to build a setup and confirm that a silent install works? I have a MS support case open, they seem to think I need to run SQL Server setup from command line to get a silent install to happen. Thanks!
Andy – I’m not really sure why yours isn’t showing, but make sure you changed the product name in the product.xml file.
Anatoly – I don’t do a completely silent install. My users don’t have to click anything, but the setup windows show them what is going on. If you want it to be completely silent, you will have to explore the options in the sqlexpr.exe file to get that to happen and then use those options in the package.xml file. See this site: http://msdn2.microsoft.com/en-us/library/ms144259.aspx
I think it’s the /qn switch. I use the /qb switch which displays the basic dialogs so they know what’s happening. I think with the /qn switch, error messages are suppressed to, which could be a bad thing… But I’m not 100% sure of that.
Very Cool, just a quick question before I try it out:
Will my instance of sql server express be removed silently if the user chooses to remove my program with the add/remove programs option in the control panel?
Shagohod,
No, it will not uninstall it if you remove your program. Like all prerequisites that are installed by Windows Installer, those components are left there.
Havin a bit of trouble with it, I’ve changed the product code in my product.xml but it detects my other instance of SQLEXPRESS and does not install my custom instance. I see both instances available for prereq’s and I only have my custom instance selected…….
Shagohod, I had to redo this for Visual Studio 2008 since the bootstrapper folder is in a different spot now and I discovered there is a tag in the package.xml file you need to remove to stop this from happening.
Find all occurances of this:
and remove it.
Hope this helps (albeit a month later!).
[…] solution presented here works exceedingly well. May 22, 2008 | Filed Under […]
Hello.
Does anyone know whether I can enable the CLR during silent installation? Is there an argument for this operation?
Thanks in advance.
This is the Microsoft documentation on command line installation:
http://technet.microsoft.com/en-us/library/ms144259.aspx
I have putted The Solution Here
Plz , Visit It
http://hamidseta.blogspot.com/2008/05/install-sqlexpr32exe-silently.html
Hope So This Will U
Regards
Seta Hamid S
[…] solely SQL Server 2005 Express with a database including stored procedures and triggers, we have an installation package where you just have to click on setup.exe and let it do its thing. When I called the main contact […]
Really nice.Saved my lot of time.Thanks
hi all
I am trying to install sql server 2005 , facing a problem.
when i start installation , at one step it should show options for database,client components and all but it is just showing online documentation..
can anyone suggest me a solution to this problem
plz mail me at harish_trash@rediffmail.com
thank you
Harish
Hey,
I’m trying to create an installation kit for a windows application with sqlserver express. my db has a windows authentication , not a specific user’s authentication.
I’ve followed your steps as it’s written –
Create a setup project , Adding myApp Sqlserver as a prequisit , and build the setup project.
the realese folder seemed to be correct – it contains my project dlls and database , and contain folder for each prequisit [.net Framework , MyAppSqlServer,WindowsInstaller 3.1]
But, when i’m trying to run the msi on a client , it seems that the msi don’t install the prequisits.
Can you help me? Do you whats the problem? should i add something to the installation project?
thanks allot,
Good week.
There should be a setup.exe that is the bootstrapper that will install the prerequisites. If you just run the MSI, it won’t install the prereqs.
hi, thanks for the article.
I tried using the trick but i failed with the error message
Component LSMS SQL Server 2005 Express Edition has failed to install with the following error message:
“An error occurred attempting to install SQL Server 2005 Express Edition.”
The following components were not installed:
– SQL Server 2005 Express Edition
The following components failed to install:
– LSMS SQL Server 2005 Express Edition
See the setup log file located at ‘C:\DOCUME~1\ADMINI~1.RAD\LOCALS~1\Temp\VSD11A.tmp\install.log’ for more information.
here i renamed sqlserver to LSMS SQl server as My Appln
in yurs
now i can not continue installation as i get error:
Setup has detected that the publisher of file “C:Documen…..\temp\VSD24.tmp\dotnetfx\dotnetfx.exe” can not be verified.
i tried making copy of dotnetfx file as that of Sql Server but I am not sure and i couldnot get it working.
Thanks
hi changed the argument to \qn setup is installed silently ,but i run the setup again it is againg installing the same instance .if instance is installed it should skip the sql express installation.
give me a suggestion to achieve this
hi silent installation is working for me but i want to check condition if sqlexpress instance previously installed it should skip sqlexpress installation how to achieve this
Satish,
You have to make sure your product.xml file contains this section:
and that the SqlExpressChk.exe file is in the package folder.
Then in the package.xml file make sure that in the InstallConditions section you have this:
works
Hi,
I tried your code, I am able to see the specific instance in the prerequisties. But When i am installing the setup, it showng sql server installation dialog boxes. I don’t know what i am missing here to achieve this. Many thanks for your help.
Hi,
I’m using your code, it works smoothly. But when I add the Service Pack 2 o SQL Express (It’s needed for Windows 7 computers), the setup doesn’t recognize the installed instance.
TIA
will this work for SqlServer 2008 express r2?
It should, I have not tested it under that version, however.
Hello,
the post you made here on ‘December 10th, 2009 10:49 am’ is not clear. after the ‘this section:’ i see a big white space. is there suppose to be code below it?
Second
I like you instruction, but i find it difficult to follow, especially the part that deals with the custom actions database. why not explain the process to be clear for a first timer.
I followed the instruction found here http://msdn.microsoft.com/en-us/library/49b92ztk%28v=VS.100%29.aspx
And am going to try it and give feedback here
Yes there is supposed to be code (XML) displaying throughout the article. If that doesn’t show, make sure you have javascript turned on and that you are using a fairly recent version of your web browser.
Thanks! works great