none
PowerShell + Invoke-Sqlcmd and SCOM SQL update script RRS feed

  • Question

  • I am trying to automate a SCOM install.   When you apply a UR to SCOM, no matter what the version, you must run one or more SQL scripts against the DB or DW for SCOM.

    When you run these in the management studio, no results are returned.  You only get a message: Command(s) completed successfully.

    I need to know if invoke-sqlcmd will work with this script, and if it's possible to get the "message" output on the screen and or sent to a text file.

    Here are the two variations I have been testing:

    Invoke-Sqlcmd-ServerInstance SERVERNAMEHERE-DatabaseOperationsManager-Query$SCOMURSQL #($SCOMURSQL is the variable which contains the query.  It's over 4K lines).

    and

    Invoke-Sqlcmd -InputFile "D:\Apps\Microsoft System Center 2016\Operations Manager\Server\SQL Script for Update Rollups\update_rollup_mom_db.sql" -ServerInstance "SERVERNAMEHERE" -Database "OperationsManager"

    How can I validate that the script ran and updated what it was supposed to?  In the management studio I get the message, via this command line I get nothing.


    Regards, Blake Email: mengotto<at>hotmail.com Blog: http://discussitnow.wordpress.com/ If my response was helpful, please mark it as so, if it answered your question, then please also mark it accordingly. Thank you.

    Wednesday, May 22, 2019 12:21 AM

All replies

  • Hi,

    You can add PRINT in your T-SQL script to return a message you want. Here is a example.

    Invoke-Sqlcmd -ServerInstance SERVERNAMEHERE -Database OperationsManager -Query "PRINT 'update successfully';" –Verbose


    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 22, 2019 2:41 AM
  • Sounds good, but what does the PRINT actually do, just print what I put in quotes?  This update sql statement, when run in the management studio returns zero results, there is actually no results tab, just a message tab that says "Command(s) completed successfully".  I want to capture that, if possible, and return it to a file or the screen. 

    From what you show me here, it seems as if I am adding a comment, that it ran successful even if it did not.

    Is that the case?


    Regards, Blake Email: mengotto<at>hotmail.com Blog: http://discussitnow.wordpress.com/ If my response was helpful, please mark it as so, if it answered your question, then please also mark it accordingly. Thank you.

    Wednesday, May 22, 2019 3:05 AM
  • Yes, it only print the message you defined. You can also add some judgment statement.

    IF @return = 1 -- success
        BEGIN
            PRINT 'Success'
        END
    
    ELSE
        BEGIN
    	PRINT 'Failure'
        END

    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 22, 2019 3:21 AM
  • I tried to do your initial suggestion and got an error:

    Invoke-Sqlcmd-ServerInstanceSERVERNAME -DatabaseOperationsManager-Query$SCOMURSQL"PRINT 'update successfully';"-Verbose 

    Invoke-Sqlcmd : A positional parameter cannot be found that accepts argument 'PRINT 'update successfully';'.

    At F:\SCOM\SCOM Install Scripts\7_SCOMURSQLCMD.ps1:4256 char:5

    how do I ad the if else statement?


    Regards, Blake Email: mengotto<at>hotmail.com Blog: http://discussitnow.wordpress.com/ If my response was helpful, please mark it as so, if it answered your question, then please also mark it accordingly. Thank you.

    Wednesday, May 22, 2019 3:30 AM
  • I mean you need to add it in your T-SQL script.

    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 22, 2019 5:03 AM
  • Hi Blake Mengotto,

    Any update? If you have resolved your issue, please close the thread by marking the useful reply as answer.

    In addition, if you have another questions, please feel free to ask.

    Thanks for your contribution.

    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 27, 2019 1:41 AM
  • Thanks for the help, but I don't think you can return status messages when you invoke this, at least I found no way.  I don't want to have to edit a script in order to get a return, if it's a SQL update script, like this one for SCOM is, then I suspect because nothing is returned, it runs, but there seems to be no way to check that it has.  I am guessing logging would have to be cranked up on SQL in order to discover that something like this was run.

    Regards, Blake Email: mengotto<at>hotmail.com Blog: http://discussitnow.wordpress.com/ If my response was helpful, please mark it as so, if it answered your question, then please also mark it accordingly. Thank you.

    Monday, May 27, 2019 9:47 AM