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 '18 at 9:51
  • 5
    Why does this evidence need to be a screenshot format? Feb 8 '18 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 '18 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 '18 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 '18 at 20:22
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 '18 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 '18 at 9:14
  • 1
    @VineshSenthilvel I don't understand what you mean by GUI mode
    – gbn
    Feb 8 '18 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 '18 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 '18 at 11:07

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