Windows Installer - SQL Server Installation Issues and a Solution.

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:
view plaincopy to clipboardprint?

1. Arguments=’-q /norebootchk /qb reboot=ReallySuppress addlocal=all
2. INSTANCENAME=MyApp SECURITYMODE=SQL SAPWD=MyStrongPassword
3. DISABLENETWORKPROTOCOLS=0 SQLAUTOSTART=1′

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:
view plaincopy to clipboardprint?

1. MyApp SQL Server 2005 Express Edition

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.
view plaincopy to clipboardprint?

1. Private Sub ExecuteSQL(ByVal SQL As String, _
2. ByVal ConnectionString As String)
3.
4. Dim srv As New Server(New ServerConnection( _
5. New SqlClient.SqlConnection(ConnectionString)))
6.
7. srv.ConnectionContext.ExecuteNonQuery(SQL)
8. srv.ConnectionContext.Disconnect()
9. End Sub
10.
11. Private Sub BulkLoad(ByVal ConnectionString As String, _
12. ByVal TableName As String)
13.
14. Dim bulk As New SqlClient.SqlBulkCopy(ConnectionString, _
15. SqlClient.SqlBulkCopyOptions.KeepIdentity)
16.
17. Dim ds As New DataSet
18. Dim path As String = My.Application.Info.DirectoryPath
19. ds.ReadXmlSchema(path & “\” & TableName & “.xsd”)
20. ds.ReadXml(path & “\” & TableName & “.xml”)
21. bulk.BulkCopyTimeout = 0
22. bulk.DestinationTableName = TableName
23. bulk.WriteToServer(ds.Tables(TableName))
24. bulk.Close()
25. End Sub
26.
27. Public Overrides Sub Install(ByVal stateSaver As _
28. System.Collections.IDictionary)
29.
30. MyBase.Install(stateSaver)
31. Dim ConnBuilder As New SqlClient.SqlConnectionStringBuilder()
32. ConnBuilder.UserID = “sa”
33. ConnBuilder.Password = “MyStrongPassword”
34. ConnBuilder.DataSource = “localhost\MyApp”
35. ConnBuilder.InitialCatalog = “master”
36. ExecuteSQL(My.Resources.CreateDatabaseSQL, _
37. ConnBuilder.ConnectionString())
38.
39. ConnBuilder.InitialCatalog = “MyDatabase”
40. BulkLoad(ConnBuilder.ConnectionString(), “MyTable”)
41. End Sub

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!

About these ads