I wish to send results of 'query' from trigger by email to specific recipients

Question

I wish to send results of 'query' from trigger by email to specific recipients

mschi6317

Old Hand

Points: 363
More actions
December 2, 2009 at 8:04 am

#218181

ALTER TRIGGER [trig_NO_LABOR_DATA]
ON [dbo].[WIP_FG]
AFTER INSERT
AS
--insert results of query into the table below
INSERT INTO No_Prod_Data_Inn (job_number, fg_date, qty_scanned)
--the below query identifies those jobs scanned to finished goods without
--any pde/wip transactions
SELECT wip_fg.job_number, wip_fg.trx_datetime, wip_fg.trx_qty
FROM INSERTED wip_fg inner join spec_file on wip_fg.item_no=spec_file.item_no
left join wip_mach_ops
on wip_fg.job_number=wip_mach_ops.job_number
where wip_fg.job_number>700000
and wip_mach_ops.job_number IS NULL
--END
--try to email results of query here or at very least notify production
--there is a job scanned with no pde/wip data
How can I sent results of insert values (Table No_Prod_Data_Inn) to specific recipients.

Viewing 15 posts - 1 through 14 (of 14 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Personally, I'd cache the query results in a table, and have a job periodically check the table and send any results it finds. That way, you're not slowing down the trigger (which slows down the whole transaction).

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

I agree about not putting the email in a trigger.

Here's the email syntax you can run from a job:

EXEC msdb.dbo.sp_send_dbmail

@recipients = 'MyName@MyCompany.com',

@query = 'SELECT wip_fg.job_number, wip_fg.trx_datetime, wip_fg.trx_qty

FROM INSERTED wip_fg inner join spec_file on wip_fg.item_no=spec_file.item_no

left join wip_mach_ops

on wip_fg.job_number=wip_mach_ops.job_number

where wip_fg.job_number>700000

and wip_mach_ops.job_number IS NULL

' ,

@subject = 'job scanned with no pde/wip data'

mschi6317 Old Hand Points: 363 More actions · Answer 3

My only issue with that is I can do that from Crystal. I would like to send a message to intended recipeints at time of insert. However, I suppose I could set up a job to fire several times/day.

mschi6317 Old Hand Points: 363 More actions · Answer 4

Thanks, very much for the help. Although it's probably better to send results from 'cached' table, I may still include this code in the trigger. I'd like it to send a message at time of insert. Thanks again for your help!

mschi6317 Old Hand Points: 363 More actions · Answer 5

Thanks again for your help yesterday. I received an error when trying to execute this query stating @profile name was needed, so I added the profile as configure in database mail. I then tried to execute query again and received a different error. My new query looks like this;

EXEC msdb.dbo.sp_send_dbmail

@profile_name = my profile name',

@recipients = 'myemail@comapny.com',

@query = 'SELECT wip_fg.job_number, wip_fg.trx_datetime, wip_fg.trx_qty

FROM INSERTED wip_fg inner join spec_file on wip_fg.item_no=spec_file.item_no

left join wip_mach_ops

on wip_fg.job_number=wip_mach_ops.job_number

where wip_fg.job_number>700000

and wip_mach_ops.job_number IS NULL

' ,

@subject = 'job scanned with no pde/wip data'

after execution I receive the following error;

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476

Query execution failed: Msg 208, Level 16, State 1, Server ILLT1394, Line 1

Invalid object name 'INSERTED'.

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

trying to find a solution currently....if you have any thoughts please let me know...thanks

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

Inserted is only available in the trigger. I don't even think it can be used in a sub-sproc. The query there is not executed in the same context or even at the same time that the trigger is running. DB Mail uses service broker to queue up the email and then spools up the dbmail executable to do the work. Short answer, you can't use the inserted "table" in this way. Also you mentioned profile, do you have a defined public default profile in DB Mail, if not you need one if you aren't going to tell it what profile to use.

CEWII

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

Rather than do this in a trigger, I'd probably go the route of using a OUTPUT clause to get the inserted information and the pop the necessary information into a broker queue to let it process asynchronously to prevent impacting performance in a trigger.

mschi6317 Old Hand Points: 363 More actions · Answer 8

Yes I am finding it difficult to get the '2nd' process of shooting off email after insert to work. I was successful in creating profile and can send results from inserted table, but I really wanted to alert production as soon as the insert happened. I supposed I could setup a job as was suggested to run several times/day, etc. Thanks again.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 9

This sounds like a process issue to me. When the data gets inserted where is it coming from? An Application, a Adhoc user request, a bulk upload from a flat file? Any of those three sources could generate the notification to production that a insert occured as well.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

If you use DBMail instead of SQLMail, then sending mail from a trigger should not be an issue as DBMail is built on Service Broker and is therefor asynchronous.

Scanninng tables and things like that are another story of course...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

mschi6317 Old Hand Points: 363 More actions · Answer 11

It most certainly is a process issue. However, the insert is coming from an application. The idea here is to catch items that weren't processed in a somewhat 'realtime' manner. Current app doesn't issue any kind of warning. Do you have any examples of creating output file? I may be going that route. Plan here would be to output or even merely sp_mail result of query by use of job engine in SQL (not familar with broker) on schedule, delete results of inserted table, repeat. Basically a email would be generated to specified users throughout the day regardless of whether there's data in inserted table or not. I wanted it to fire only after insert by using trigger.

mschi6317 Old Hand Points: 363 More actions · Answer 12

As far as I know I am using dbmail or that's what I configured inside of the management studio. Can you give me a little more detail?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

mschi6317 (12/3/2009)
As far as I know I am using dbmail or that's what I configured inside of the management studio. Can you give me a little more detail?

Show us your trigger code, with the Email parts, and we can tell you.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

akhamoshioke Ten Centuries Points: 1110 More actions · Answer 14

You need to modify the object name and try again

SQL Server sees the space between the word INSERTED and the word wip_fg as meaning two objects hence the error.

INSERTED_wip_fg