Return All affected dbs in error

Question

Return All affected dbs in error

Super Cat

SSCertifiable

Points: 7334
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.

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

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

Rick-153145 SSCrazy Points: 2823 More actions · Answer 1

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: 7334 More actions · Answer 2

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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: 1004405 More actions · Answer 4

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.

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

Super Cat SSCertifiable Points: 7334 More actions · Answer 5

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: 442467 More actions · Answer 6

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: 1004405 More actions · Answer 7

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.

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