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.

9
  • 5
    Please explain why the scripts absolutely have to be executed through SSMS rather than another method. Feb 8, 2018 at 9:51
  • 5
    Why does this evidence need to be a screenshot format? Feb 8, 2018 at 15:07
  • 2
    I'll assume this is part of an audit process. Double-check that the query results have to be taken via screenshot. Saving the results to a simple text file might be acceptable.
    – RDFozz
    Feb 8, 2018 at 16:50
  • 1
    Why has this been voted to close because of "Unclear what you are asking"?! I may not agree with the OP's method or reasoning but it is not unclear what is being asked. I see no reason to close the question on this basis. Feb 8, 2018 at 19:31
  • 1
    You are executing in SSMS...just to get a screenshot!!??! You are kidding right? What purpose could that possibly serve?
    – user507
    Feb 8, 2018 at 20:22

2 Answers 2

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}")
1
  • 5
    Needs more xp_cmdshell. Feb 8, 2018 at 14:18
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.

4
  • I need to execute the script in GUI mode (SSMS). The ultimate target of my script is to execute all the scripts one by one in the query window. Using sqlcmd or Invoke-SQLCmd will return the results as a text. I would require the scripts to be executed in SSMS. Is there is any possible way to achieve that? Feb 8, 2018 at 9:14
  • 1
    @VineshSenthilvel I don't understand what you mean by GUI mode
    – gbn
    Feb 8, 2018 at 10:36
  • I mean the script to be executed in SSMS query window rather than in a PowerShell window. Using sqlcmd or Invoke-SQLCmd will execute the SQL Script in PowerShell window. So i'm trying to open the script in ssms.exe and execute. Feb 8, 2018 at 10:59
  • I see your comment on the question. Run it manually in SSMS: there is no clean way to automate it
    – gbn
    Feb 8, 2018 at 11:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.