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

Question

I have sqlcmd.exe 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 

After changing $env:PATH , force use 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 LocalDB instance is started by default and belongs to 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 the command on (localdb)\v11.0 using sqlcmd.exe

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

But when you try to do 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 make lmake Invoke-Sqlcmd connect to (localdb)\v11.0 ?

Answer 1

Got this from several other sources, it seems to be working so far.

JBs Powershell

and

How to start PowerShell with .NET 4?

Another way to get PowerShell and LocalDB to play well is to make PowerShell aware of DOTNET 4.0.3. This can be done by creating a file called "powershell.exe.config" in 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> 

Remember, this is not an officially supported way to use PowerShell, so it might break other stuff ...

Answer 2

This is code that works for me in adverse conditions (see my comments right after the code). I suspect that simpler code may work in a more general environment, but I did not delve into it.

Instance instance expires in a few minutes. You will be better off if you can connect using (localdb) \ instanceName, because these connections do not look like timeouts.

 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 beyond my control, the runtime environment in which this operation is performed is unusual. This is a remote runtime with an incomplete session context. In addition, I had to override USERPROFILE to solve some other problems.

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

Answer 3

I assume invoke-sqlcmd does not know what "(localdb)" is. Try using localhost instead.

Answer 4

I did this at work recently and had some initial problems with a local database. To make it 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 parameters to the way your database name is called in the SQL instance.