Return All affected dbs in error

  • Super Cat

    SSCertifiable

    Points: 7316

    SELECT
      [DB]
     
    FROM [LogNewTables]

    where Newtable > 0
    and logDate > Dateadd(dd,-1,Getdate())

      If @@rowcount > 0

        Begin

        <do the magic here>

        end

    I can’t for the life of me figure out how to return an error when the query returns multiple rows.
    Single row is not an issue.
    I want to display the DBNAME when multiple dbs have had new tables created which will notify the DBAs when this has occurred.

  • Rick-153145

    SSCrazy

    Points: 2713

    Set up a custom message in sys.messages, then set up an alert for that that emails the DBA team maybe..

    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addmessage-transact-sql?view=sql-server-2017

  • Super Cat

    SSCertifiable

    Points: 7316

    Thanks, I have resolved. Had a coffee and something to eat and my brain clicked into gear.

  • Lynn Pettis

    SSC Guru

    Points: 442332

    A DDL trigger that fires on CREATE TABLE would do this for you as well.  Take a look this as it may be simpler.

  • Jeff Moden

    SSC Guru

    Points: 996622

    Super Cat - Tuesday, June 12, 2018 4:10 AM

    Thanks, I have resolved. Had a coffee and something to eat and my brain clicked into gear.

    So what did you end up doing?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Super Cat

    SSCertifiable

    Points: 7316

    I was really simple. Embarrassed I posted now. Dumped the name in a temp table and just created a cursor to loop through and pump the db names in the error.

    I will eat next time before starting work.

  • Lynn Pettis

    SSC Guru

    Points: 442332

    Super Cat - Friday, June 15, 2018 1:47 AM

    I was really simple. Embarrassed I posted now. Dumped the name in a temp table and just created a cursor to loop through and pump the db names in the error.

    I will eat next time before starting work.

    How about posting your code.

  • Jeff Moden

    SSC Guru

    Points: 996622

    Super Cat - Friday, June 15, 2018 1:47 AM

    I was really simple. Embarrassed I posted now. Dumped the name in a temp table and just created a cursor to loop through and pump the db names in the error.

    I will eat next time before starting work.

    Heh... we've all been there, for sure.  Thanks for the feedback.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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