How to connect to SQL Server LocalDB using Invoke-Sqlcmd?
Got this from a couple other sources, seems to work so far.
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 ...
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 $smoScInvoke-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)]
Here's what I use, and I use PowerShell to leverage SQL every day. Create a function that you call as needed. Build your queries elsewhere and pass them along to this function. Be sure to use the -read or -write switches so it uses the correct calls. Read returns a value, and write is execute only. I have the error messages commented out by default, but enable them if something is not working.
As far as the DB connectivity is concerned, install SSMS and verify it's set up correctly. If you can query it in SSMS, then this function will work. If a different account has elevated privileges to this instance, then run PS as that user.
I highly recommend using ISE instead of the standard shell.
function Execute-SQL($query, [switch]$read, [switch]$write){ $dataSource = "Your instance name" $database = "Your database name" $connectionString = "Server = $dataSource; Database = $database; Integrated Security = $TRUE;" $connection = New-Object System.Data.SqlClient.SqlConnection $connection.ConnectionString = $connectionString $connection.Open() $cmd = New-Object System.Data.SqlClient.SqlCommand $cmd.Connection = $connection $cmd.CommandText = $query $cmd.CommandTimeout = 0 if($write){ try{ $cmd.ExecuteNonQuery() | Out-Null }catch{ #Write-Host $Error[0] -ForegroundColor DarkRed #Write-Host $cmd.CommandText -ForegroundColor DarkGray } }elseif($read){ try{ $output = "" $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $adapter.SelectCommand = $cmd $DataSet = New-Object System.Data.DataSet $adapter.Fill($DataSet) | Out-Null $output = $DataSet.Tables[0] | ?{$_} $connection.Close() return $output }catch{ #Write-Host $Error[0] -ForegroundColor DarkRed #Write-Host $query -ForegroundColor DarkCyan } }else{ #Write-Host "READ/WRITE NOT SPECIFIED" -ForegroundColor DarkCyan } $connection.Close()}