0

I have a script file in my local machine. I have to open SSMS and open the query in SSMS and to execute the query.

For opening the SSMS and query, i have used the following PowerShell script to do it.

ssms.exe 'E:\PowerShell_PROD\Screenshot\ServerDetails.sql' -E -S localhost

How can i run this query using PowerShell and how can i close this query window?

The reason I'm doing this is I have to take the evidence of the SQL Server properties and other information. For that I have a PowerShell script to capture the screenshot.

I'm preparing the PowerShell script that runs every query in SSMS and captures the outcome of that and save into specified path. Now am able to open the query in SSMS and capture the screenshot, but I'm unable to run the query.

Tom V
  • 15,752
  • 7
  • 66
  • 87
Vinesh Senthilvel
  • 313
  • 1
  • 6
  • 15

2 Answers2

8

You probably shouldn't do this, but it's possible to simulate user activity by using SendKeys.

Sends keystrokes to the active application.

This does what you want:

add-type -AssemblyName System.Windows.Forms

ssms.exe 'c:\temp\test.sql' -E -S localhost

# wait to make sure the application is open
start-sleep -Milliseconds 10000

# press F5
[System.Windows.Forms.SendKeys]::SendWait("{F5}")

# wait for the query to finish
start-sleep -Milliseconds 10000

# press ALT-F4
[System.Windows.Forms.SendKeys]::SendWait("%{F4}")
Tom V
  • 15,752
  • 7
  • 66
  • 87
6

I can't see why you'd use SSMS for unattended script execution.
There is no added value at all.

You would use sqlcmd?

sqlcmd -Q 'E:\PowerShell_PROD\Screenshot\ServerDetails.sql' -E -S localhost

or Invoke-SQLCmd?

Invoke-SQLCmd -ServerInstance localhost -Query 'E:\PowerShell_PROD\Screenshot\ServerDetails.sql'

Which are almost the same thing. Quoting from the docs

The Invoke-Sqlcmd cmdlet runs a script containing the languages and commands supported by the SQL Server SQLCMD utility.

gbn
  • 70,237
  • 8
  • 167
  • 244