Return All affected dbs in error

• Super Cat

  SSCertifiable

  Points: 7316

  More actions

  June 12, 2018 at 3:15 am

  #406676

  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

  More actions

  June 12, 2018 at 3:31 am

  #1993646

  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

  More actions

  June 12, 2018 at 4:10 am

  #1993651

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

• Lynn Pettis

  SSC Guru

  Points: 442333

  More actions

  June 12, 2018 at 8:13 am

  #1993689

  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: 996645

  More actions

  June 12, 2018 at 8:21 am

  #1993690

  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

  More actions

  June 15, 2018 at 1:47 am

  #1994085

  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: 442333

  More actions

  June 15, 2018 at 7:53 am

  #1994109

  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: 996645

  More actions

  June 15, 2018 at 7:55 am

  #1994112

  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)