Post reply

management studio query window outputs and messages

  • September 16, 2010 at 2:23 am

    #157206

    Hi Guys,

    I posted in the T-SQL part of the forum on this subject and don't know which forum it rightly sits in... apologies if this is the wrong place...

    Anyway.. I am making a little application that will tell users whether or not a stored/scheduled job completed successfully. If not the system also has to give the user helpful information as to why.

    normally if a stored proc fails overnight one would execute it during the day in an interactive mode and see what error messages are returned. eg 'warning truncation would occur blah blah blah'

    this information is helpful to a developer to see the cause of the issue and fix it. unfortunately our sql server is a single cpu VM which also runs IIS for PHP and ASP applications. so simply does not have enough resources to run our overnight jobs interactively during the day. I know that this is terrible and any DBA would be well within their rights to come and smack me for having such a terrible setup. I will say however that it's not my fault! our IT dept said 'We are going virtual! Everywhere!' but thats beside the point.

    this means that when a sql server stored proc fails we are all clueless as to why - digging around the sql agent 'view history' stuff can be helpful but isn't always.

    what I want to do is execute a stored procedure and every time a message occurs such as

    (0 rows affected)

    or

    Msg 2812, Level 16, State 62, Line 1

    Could not find stored procedure 'vPrfName'.

    I want that string to be dumped into a varchar column in a table.

    I also want to redirect everything that currently goes to the 'Results' pane to a database record.

    this will allow a member of our service desk to see at a glace the status of a job each morning and raise a call with the developers to fix it.

    is there an easy way I can redirect the messages output?

    Thanks!

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • September 16, 2010 at 11:21 am

    #1222418

    HI Ben,

    Please check the script from the following link...

    This gives all the details about the failed jobs including the Failure message.

    http://www.mssqltips.com/tip.asp?tip=1054

    You can either store the o/p in a table or create a report for the service desk people to access so that they can always know the status of the Failed jobs.

    Thank You,

    Best regards,

    SQLBuddy

  • September 17, 2010 at 1:46 am

    #1222782

    Thanks Buddy,

    That's really useful!

    Still wondering about the 'x rows affected' stuff but this is very helpful 🙂

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • September 17, 2010 at 2:10 am

    #1222792

    When you schedule a job you can setup the output to a file or to a table.

    You'll get all the output, including errors and (xx rows(s) affected).

  • September 17, 2010 at 2:20 am

    #1222800

    oooh I had not realised this! Thanks!

    dont suppose anyone has a tutorial at all? if not I'll google for a bit 🙂

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • September 17, 2010 at 2:38 am

    #1222810

    Just keep clicking everywhere in the management studio 🙂

    The job properties windows are easy to use, just make sure you check every options and see if you need it 🙂

  • September 17, 2010 at 2:41 am

    #1222811

    Thanks Oliii!

    I had a look at the steps details and found the tickbox to export to table but couldn't see where/how to specifiy what table to output to! I'll have another muck about in a bit 🙂

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

Viewing 7 posts - 1 through 6 (of 6 total)

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