Saving output as SQL file

  • dan-572483

    SSCoach

    Points: 15868

    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 9 months, 3 weeks ago by  dan-572483. Reason: make more clear
    • This topic was modified 9 months, 3 weeks ago by  dan-572483.
  • RonMexico

    Hall of Fame

    Points: 3414

    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?

  • dan-572483

    SSCoach

    Points: 15868

    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 9 months, 3 weeks ago by  dan-572483.

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

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