Post reply

Saving output as SQL file

  • December 31, 2019 at 5:59 pm

    Go to Answer

    #3711507

    If anyone is working today I have a problem I'm working on.

    I have a script which when run in SSMS, generates a SQL update script which is saved as an SQL file and run later.  I am trying to automate this processes and generate a SQL file.

    Based on what I've found in google searches I'm trying to do this in with Powershell:

    invoke-SqlCmd -InputFile "C:\DBAScripts\Script.sql" -ServerInstance XXXX -Database XXXX | Out-File -filePath "F:\OutputPath\OutputScript.sql"

    When I do this, I get a 0 kb empty file.  The assumption in the posts I'm reading is that you want to save returned rows into a file, but when run in SSMS this input script puts the output in the Messages Tab.

    The solution doesn't have to use Powershell.  I just need a way to automate the running of a script and save the content of the Messages tab to a SQL file.

    • This topic was modified 4 years, 3 months ago by  dan-572483. Reason: make more clear
    • This topic was modified 4 years, 3 months ago by  dan-572483.

  • December 31, 2019 at 7:51 pm

    #3711540

    I'm assuming the script you are running prints the result to give you the update script which is why it is showing in the messages tab. Have you tried modifying the script to use select so that it outputs to the results tab?

  • December 31, 2019 at 9:47 pm

    Answer

    #3711543

    I found a better answer:

    invoke-SqlCmd -InputFile "C:\DBAScripts\Script.sql" -ServerInstance XXXX -Database XXXX -Verbose 4>  "F:\OutputPath\OutputScript.sql"

    See https://sqlnotesfromtheunderground.wordpress.com/2015/09/09/powershell-outputting-a-sql-server-query-result-from-the-message-tab/

     

    • This reply was modified 4 years, 3 months ago by  dan-572483.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply