0

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
DBADon
  • 473
  • 4
  • 11
  • 1
    See if any of these solutions help: https://dba.stackexchange.com/questions/234564/how-to-prevent-sql-files-opening-new-instances-of-ssms-17-9-1 and also you might state what version of the SSMS application you are running. Also look over https://stackoverflow.com/questions/7123365/how-to-open-multiple-sql-files-in-only-one-ssms-instance for other solution that may help. – Vomit IT - Chunky Mess Style Dec 09 '20 at 16:15
  • Thx for those, it appears the issue is worse than I assumed. I have updated my question to add the SSMS versions and denote that I am looking for an answer within Powershell. – DBADon Dec 09 '20 at 16:32
  • 2
    check the documentation https://docs.microsoft.com/en-us/sql/ssms/ssms-utility?redirectedfrom=MSDN&view=sql-server-ver15 and try if it works for all your versions – SimonS Dec 09 '20 at 16:43
  • @SimonS I would love to add SSMS to all of my servers (and that project is moving forward) but I am stuck where I am at for the moment. – DBADon Dec 09 '20 at 16:48

1 Answers1

0

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.

DBADon
  • 473
  • 4
  • 11
  • 1
    I think ssms is in the path variable, you don't need to do this. ssms just works on it's own. if it isn't in the path variable, then add it. – SimonS Dec 09 '20 at 21:52
  • My Env:Path contains all of the "C:\Program Files (x86)\Microsoft SQL Server\XXX" directories (100-150 at least). I tried that before going this direction – DBADon Dec 09 '20 at 21:58
  • @DBADon Here's some logic that you might be able to manipulate for SSMS to satisfy the needs without so many if conditions that I wrote for Google Drive for someone but with a little trial and error, I'm sure you could get it to work for `ssms.exe`: https://superuser.com/questions/1520433/google-drive-shortcut-broken-after-automatic-updates/1523471#1523471. Sharing as FYI at a minimum otherwise.just in case. – Vomit IT - Chunky Mess Style Dec 27 '20 at 17:03