From Powershell, Open Multiple SQL files in a single SSMS Instance

Question

I have an automated Powershell process that ends with an Invoke-Item command to open multiple SQL files (currently 4 SQL files) for the testers, so they can run their scripts. Keep in mind, I am not executing their scripts, I am just opening them after my processes end. The testers can open the scripts themselves of course, but I am hoping to be able to lay everything out for them.

The issue is, the SQL scripts always open in multiple SSMS instances, usually 3 files on the first and 1 on the second, but occasionally 2 and 2. (When I expanded the number of SQL files to 10, I get four instances with 3, 3, 2 & 2 files in each.)

I have servers at different SQL versions, so the path to the executable may be different between systems. So calling the EXE directly with the filenames appended is not an option, unless I add logic to find the correct path to the EXE's beforehand.

Note: These are different SQL versions (2008R2 to 2017) and the SSMS version would be the version that came with the installation rather than the new standalone SSMS

How do I get all of the SQL files to open under a single SSMS instance from Powershell?

$sql = (Get-ChildItem -Path C:\Temp\Export | Where-Object {$_.Name -like "*.sql"}).FullName
II $sql

score 0 · Accepted Answer · answered Dec 09 '20 at 21:46

I was hoping to avoid it, but I was able to resolve this by determining the path to the latest SSMS.exe and then using the Call operator rather than Invoke-Item.

$ssms = $null
If (Test-Path "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18"){
    $ssms = (Get-ChildItem -Path "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18" -Recurse | Where-Object -Property Name -EQ "ssms.exe").FullName}
If ($ssms -eq $null -and (Test-Path "C:\Program Files (x86)\Microsoft SQL Server\150")){
    $ssms = (Get-ChildItem -Path "C:\Program Files (x86)\Microsoft SQL Server\150" -Recurse | Where-Object -Property Name -EQ "ssms.exe").FullName}
If ($ssms -eq $null -and (Test-Path "C:\Program Files (x86)\Microsoft SQL Server\140")){
    $ssms = (Get-ChildItem -Path "C:\Program Files (x86)\Microsoft SQL Server\140" -Recurse | Where-Object -Property Name -EQ "ssms.exe").FullName}
If ($ssms -eq $null -and (Test-Path "C:\Program Files (x86)\Microsoft SQL Server\130")){
    $ssms = (Get-ChildItem -Path "C:\Program Files (x86)\Microsoft SQL Server\130" -Recurse | Where-Object -Property Name -EQ "ssms.exe").FullName}
If ($ssms -eq $null -and (Test-Path "C:\Program Files (x86)\Microsoft SQL Server\120")){
    $ssms = (Get-ChildItem -Path "C:\Program Files (x86)\Microsoft SQL Server\120" -Recurse | Where-Object -Property Name -EQ "ssms.exe").FullName}
If ($ssms -eq $null -and (Test-Path "C:\Program Files (x86)\Microsoft SQL Server\110")){
    $ssms = (Get-ChildItem -Path "C:\Program Files (x86)\Microsoft SQL Server\110" -Recurse | Where-Object -Property Name -EQ "ssms.exe").FullName}
If ($ssms -eq $null -and (Test-Path "C:\Program Files (x86)\Microsoft SQL Server\100")){
    $ssms = (Get-ChildItem -Path "C:\Program Files (x86)\Microsoft SQL Server\100" -Recurse | Where-Object -Property Name -EQ "ssms.exe").FullName}
If ($ssms -eq $null) {Write-Host "SSMS Has Not Been Been Found!" -BackgroundColor Red}
$sql = (Get-ChildItem -Path C:\Temp\Export | Where-Object {$_.Name -like "*.sql"}).FullName
& $ssms $sql

Ordering the directories this way ensures I am opening the latest SSMS client, but more importantly, it opens all the SQL files in the same SSMS instance, every time.

From Powershell, Open Multiple SQL files in a single SSMS Instance

1 Answers1