Tag Archives: MsSql

Connecting to a SqlExpress instance with Wix

The other day I came across a bit of a strange issue when trying to delploy a backup to a fresh SqlExpress 2008 R2 instance. I had setup the Wix Sql database tag using a loopback address:

<sql:SqlDatabase Id="DbNameRef"
                 Database="DbName"
                 Server="127.0.0.1"
                 Instance="SQLEXPRESS"
                 CreateOnInstall="yes"
                 DropOnUninstall="yes"
                 User="MySQLUser"
                 ContinueOnError="yes">

but for some reason it wasn’t connecting at all just erroring with a most helpful error message:

CreateDatabase:  Error 0x80004005: failed to create SQL database but continuing, error: unknown error, Database: [DATABASE_NAME]

I enabled TCP/IP so that I could use sql profiler and re-ran the installer hey presto guess what it worked! Obviously as SqlExpress only installs with “Shared memory” enabled this was the issue, it couldn’t connect. I tried the sqlcmd method of using “lpc:” as a prefix but still no joy. It turns out that if you cannot use a hostname or ip for the servername to switch over to shared memory’ you have to use “.” as the server name:

<sql:SqlDatabase Id="DbNameRef"
                 Database="DbName"
                 Server="."
                 Instance="SQLEXPRESS"
                 CreateOnInstall="yes"
                 DropOnUninstall="yes"
                 User="MySQLUser"
                 ContinueOnError="yes">

Killing database connections with PowerShell

Recently I have been setting up a PowerShell script to automatically setup our PY (usually aka SIT) environment. One of the tasks in the script is to Kill all the connections to a database so I can restore a backup. I managed to find a function that met my needs using the SMO libraries. Unfortunately as we are still on Sql2000 (I know, I know! Its not for much longer now tho! 😀 ) the SMO Libraries were not available on our database server so I had to come up with an alternative. Basically I decided to go the route that required the least dependancies and this is what I came up with!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
function KillDBConnections([string]$serverName, [string]$DBName)
{
    $ConnectionString = "Data Source=$serverName;Initial Catalog=master;Integrated Security=SSPI"
    $connection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString);
    $command = New-Object System.Data.SqlClient.SqlCommand;

    $command.Connection = $connection;
    $command.CommandType = [System.Data.CommandType]::Text;
    $command.CommandText = "SELECT spid FROM master..sysprocesses WHERE dbid=db_id('$DBName')";

    $connection.open();
    $reader = $command.ExecuteReader();
    $stringBuilder = New-Object System.Text.StringBuilder

    while ($reader.Read())
    {
        $stringBuilder.AppendFormat("kill {0};", $reader.GetValue(0));
    }

    $reader.Close();

    $command.CommandText = $stringBuilder.ToString();

    if($command.CommandText)
    {
        $command.ExecuteNonQuery();
    }
   
    $connection.Dispose();
}