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.”

  1. Anatoly Molotkov on December 24th, 2007 6:59 am

    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

  2. TheCodeMonk on December 24th, 2007 1:46 pm

    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.

  3. Anatoly Molotkov on December 25th, 2007 2:26 am

    That worked – thanks so much!

  4. Benjamin on January 6th, 2008 10:13 pm

    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?

  5. TheCodeMonk on January 9th, 2008 1:01 am

    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

  6. Andy on January 11th, 2008 6:39 am

    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

  7. Anatoly Molotkov on January 21st, 2008 6:12 am

    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!

  8. TheCodeMonk on January 21st, 2008 11:00 am

    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.

  9. Shagohod on February 22nd, 2008 10:22 am

    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?

  10. TheCodeMonk on February 22nd, 2008 10:29 am

    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.

  11. Shagohod on February 25th, 2008 1:34 pm

    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…….

  12. TheCodeMonk on March 19th, 2008 3:17 pm

    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!).

  13. How to deploy SQL Server 2005 Express in a custom installation… | Andrew's Weblog on May 22nd, 2008 9:57 am

    […] solution presented here works exceedingly well. May 22, 2008 | Filed Under […]

  14. Beroetz on June 2nd, 2008 8:34 am

    Hello.

    Does anyone know whether I can enable the CLR during silent installation? Is there an argument for this operation?

    Thanks in advance.

  15. TheCodeMonk on June 2nd, 2008 9:02 am

    This is the Microsoft documentation on command line installation:
    http://technet.microsoft.com/en-us/library/ms144259.aspx

  16. Seta Hamid S on June 14th, 2008 6:17 am

    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

  17. Make it easy for the IT department. : TheCodeMonk on June 23rd, 2008 10:50 am

    […] 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 […]

  18. Manas on July 2nd, 2008 4:56 am

    Really nice.Saved my lot of time.Thanks

  19. harish on July 2nd, 2008 12:30 pm

    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

  20. Gal on March 8th, 2009 7:17 am

    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.

  21. TheCodeMonk on March 8th, 2009 5:33 pm

    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.

  22. Radhika on June 19th, 2009 2:43 am

    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

  23. Radhika on June 19th, 2009 2:55 am

    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

  24. satish on December 9th, 2009 11:15 pm

    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

  25. sathishdevan on December 10th, 2009 1:35 am

    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

  26. TheCodeMonk on December 10th, 2009 10:49 am

    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:

  27. Vitaly Suhckach on January 1st, 2010 11:08 pm

    works

  28. Neelakrishnan on June 7th, 2010 6:13 am

    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.

  29. Silvana on November 23rd, 2010 3:04 pm

    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

  30. smith on April 27th, 2011 9:56 am

    will this work for SqlServer 2008 express r2?

  31. TheCodeMonk on April 27th, 2011 9:59 am

    It should, I have not tested it under that version, however.

  32. smith on April 27th, 2011 4:12 pm

    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

  33. TheCodeMonk on April 27th, 2011 4:25 pm

    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.

  34. Juan Carlos on August 5th, 2011 6:59 pm

    Thanks! works great

Leave a Reply