Categories
Archived Databases Development Software Uncategorized

SQL Server 2008 Express Unattended Install

» If you’re looking for instructions on how to do an unattended install of SQL Server 2008 Express R2, please use the search function on this site – the other article is easy to find, I promise.

On the previous version of Digital Formula I wrote an article called "How to perform an unattended installation of SQL Server 2005 Express". With the release of SQL Server 2008 it’s time to write another one about how to do the same but for SQL Server 2008 Express. So, let’s get started …

Unattended installations of SQL Server can be as involved as you want – pretty much every option can be specified in the configuration file. For this example I’m going to perform a relatively basic installation with minimal customisation so you can see the process at work. The installation will specify most of what I think are the critical options you’ll be interested in. Note that for this example I’m using the x86 (32-bit) version with advanced services.

Assumptions

Required Files

  • Run the executable you downloaded with the /x parameter (e.g. C:InstallSQL2008SQLEXPRADV_x86_ENU.exe
  • Choose a location to extract the installation files to.
  • Create a new text file that will become the unattended installation configuration file. Leave it empty for now.

Configuration Files

So, you can now create your own configuration file using Microsoft’s documentation or you can use the version I’ve included in this article.

Be careful with the Microsoft documentation as there is an error on the page called How to: Install SQL Server 2008 from the Command Prompt. It mentions a parameter called /BROWSERSVRACCOUNT but this should be /BROWSERSVCACCOUNT. If you get this wrong the installation WILL fail.

You can download the configuration file I’ve made below. The changes I’ve made are as follows (you may need to change these to match your settings).

  • Set the QUIETSIMPLE parameter to “True”.
  • Set the MEDIASOURCE parameter to the appropriate installation path.
  • Added a parameter called SAPWD and set it to the strong ‘sa’ password.
  • Set the INDICATEPROGRESS parameter to “True”.
  • Set the SQLSYSADMINACCOUNTS value to “MYPCAdministrator”.

Once you have your answer file all nicely setup and ready to use you’ll need to tell the setup program how to use it.

Run the script

From a command prompt (or Start > Run if you’re into that) run the following command. Remember to change the file and path names to the ones that match your system.

Run the install:

C:InstallSQL2008setup.exe /CONFIGURATIONFILE=C:Installsql-2008-express.ini

If you’ve done everything right this will start the installation process and you’ll end up with a SQL Server 2008 instance called SQLExpress running on your system. Easy!

Categories
Archived Databases Software Uncategorized

sqlcmd.exe on SQL 2008 fails – HResult 0x2, Level 16

I’ve built one of my test servers with SQL Server Express 2008 w/ Advanced Services. On a whole load of our servers I use SQL Express for local installations and SQLCMD.EXE works well as a good way to run scheduled backups. It’s done this way because SQL Express doesn’t support SSIS (SQL Server Integration Services), the component required for scheduled tasks. With SQL 2008, however, SQLCMD.EXE didn’t work for me when I tried to setup scheduled backups. Here’s how I fixed it …

The instance in question has the default name of SQLEXPRESS. Obviously this means that to connect to the server you need to use SERVERSQLEXPRESS – that works fine from Management Studio and from the Java application installed on this particular server.

When using SQLCMD.EXE from the command line the full error message looks like this:

HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing
a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured
to allow remote connections. For more information see SQL Server Books Online.
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

Some Googling suggested that this problem could be caused by one of 2 things. Firstly, that the SQL Browser service isn’t running – on my server it was running. Secondly, named pipes aren’t enabled in the SQL Server Configuration Manager … on mine they are. Hmmm.

I’ve had problems in the past with named instances of SQL Server so I had a look at the properties of the named pipes configuration and, sure enough, it said \.pipeMSSQL$SQLEXPRESSsqlquery. That *looks* ok, right? Yes but it’s the cause of the problem.

I changed the named pipe to the following.

\.pipesqlquery

Voila! SQLCMD.EXE now works from the command line meaning my scheduled backups now work too. Not being a full-time DBA I’d put this down to a bit of a fluke on my part although I then found an article by Jesse Johnston that confirmed the same steps worked in their situation too.

Problem solved!