How to connect to SQL Server LocalDB using Invoke-Sqlcmd?

Question

I have sqlcmd.exe from both SQLServer 2008 and SQLServer 2012:

PS C:\> Get-Command sqlcmd.exe

Definition
----------
C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE
C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE

By modifying $env:PATH i force the use of sqlcmd.exe from SQL Server 2012:

PS C:\> $env:PATH = ($env:PATH -split ";" | Where-Object { $_ -notlike "*\Microsoft SQL Server\100\*" }) -join ";"
PS C:\> Get-Command sqlcmd.exe

Definition
----------
C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE

The default instance of LocalDB is up and running, and owned by the current user:

PS C:\> sqllocaldb i v11.0
Name:               v11.0
Version:            11.0.2318.0
Shared name:
Owner:              DOMAIN\me
Auto-create:        Yes
State:              Running
Last start time:    12/06/13 18:17:57
Instance pipe name: np:\\.\pipe\LOCALDB#08EDBEF0\tsql\query

Now, i can execute command on (localdb)\v11.0 using sqlcmd.exe

PS C:\> sqlcmd.exe -S "(localdb)\v11.0" -Q "select 1"

-----------
          1

But when trying the same with Invoke-Sqlcmd i get a connection error:

PS C:\> Import-Module sqlps
PS C:\> Invoke-Sqlcmd -ServerInstance "(localdb)\v11.0" -Query "select 1"
Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

What can i do to lmake Invoke-Sqlcmd connect to (localdb)\v11.0 ?

Answer 1

UPDATE

Invoke-Sqlcmd from the SqlServer module supports LocalDB:

Invoke-Sqlcmd -ServerInstance "(localdb)\v11.0" -Query "select 1"
Invoke-Sqlcmd -ConnectionString "Server=(localdb)\v11.0; Integrated Security=True" -Query "select 1"

# both will work

Answer 2

From what I know sqlcmd.exe uses a connection string that has all the information, whereas Invoke-Sqlcmd breaks that information down into different parameters. So you likely need to split (localdb) and v11.0 across ServerInstance, Database, and ComputerName. Or you can use the ConnectionString parameter instead.

Now, I am not familiar with your use of localdb so there might be something funky with the way it handles that...or something.

Answer 3

Got this from a couple other sources, seems to work so far.

JBs Powershell

and

How can I run PowerShell with the .NET 4 runtime?

Another way of making PowerShell and LocalDB play nice is to make PowerShell aware of DOTNET 4.0.3. This can be done by creating a file called "powershell.exe.config" in the C:\Windows\System32\WindowsPowerShell\v1.0 . The file should contain the following:

<?xml version="1.0"?>
<configuration> 
     <startup useLegacyV2RuntimeActivationPolicy="true"> 
          <supportedRuntime version="v4.0.30319"/> 
          <supportedRuntime version="v2.0.50727"/> 
     </startup> 
</configuration>

Be aware that this not an officially supported way of using PowerShell, so it might break other stuff ...

Answer 4

I have been doing this at work recently and had some initial troubles connecting to a local Database. To get it to work, I ran the following code;

C:\> Import-Module sqlps -DisableNameChecking
SQLSERVER\:> cd ".\SQL\$(hostname)"
SQLSERVER\:> Invoke-Sqlcmd -Username "user" -Password "pass" -Database "databasename" -Query "foobar"

This worked for me and I was able to query the database. Obviously, change the Username, Password and Database parameter details to whatever the name of your database on the SQL Instance is called.

Answer 5

This is code that works for me under adverse conditions (see my comments just after the code). I suspect that simpler code may work in a more common environment, but I haven't dug into it.

The instance pipe times out after a few minutes. You're better off if you can connect using (localdb)\instanceName, because those connections don't seem to time out.

function Get-InstancePipeName ([string] $localDbName)
{
  while (!($pipeName = ((sqllocaldb info $localDbName) -match 'instance pipe name').Split(':', 2)[1].Trim()))
  {
    sqllocaldb start $localDbName | Out-Null
  }
  return $pipeName
}

$scsb   = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$scsb.psbase.DataSource = Get-InstancePipeName localDbName # <== put your db name here
$sc     = New-Object System.Data.SqlClient.SqlConnection $scsb.ConnectionString

$smoSc  = New-Object Microsoft.SqlServer.Management.Common.ServerConnection $sc
$smoSvr = New-Object Microsoft.SqlServer.Management.Smo.Server $smoSc
Invoke-Sqlcmd -ServerInstance $smoSvr -Query 'select 1'

For reasons currently outside my control, the execution environment where this runs is unusual. It's a remote execution environment with an incomplete session context. Also, I had to redefine USERPROFILE to work around some other issues.

[later edit: I recently found a way to extend the timeout - I had to add a RECONFIGURE after the 2nd sp_configure and (as recommended) stop and start the localdb to get it to take effect)]

Answer 6

I'm guessing that invoke-sqlcmd doesn't know what "(localdb)" is. Try using localhost instead.