Question Locked

EXEC msdb.dbo.sp_send_dbmail and @query SQL Server 2005

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am using Database Mail, (SQL Server 2005) and would trying to do use the @query function.

1. When an invoice is saved to SQL database
2. A trigger sends an email
3. I have captured the invoice number and tracking number but would also like to capture the details of the invoice line items: Item Number, qty. This information is in a different table than the trigger.

Will this be possible? and if so any suggestions?

Doug

4 replies

by tony hopkinson

Just make the content of your mail a join with inserted and invoice_lines.

You might want to look at performance though, doing a load of stuff inside a transaction through a trigger can add a serious overhead.

Personally I'd prefer to set up a table invoice mail, insert into that, then have a scheduled task that runs a stored proc, sands the mail request and then marks the mail as sent/ deletes the record.

All depends on on how heavy and how many though. if you decouple the triiget to send a mail from tehe content and timing you can give yourself a lot of options.

For instance avoiding sending two mails in quick succession, when the user noticed they'd typed in a number wrong.

by dougfettig

Thanks for the respoonse!, Can you give me an example of the join statement? and do I put this statement after the @query=?

Also, This SQL server is low use, so I am not to concerned with the added overhead. however I am interested in the Invoice Mail table, and how I can set that up and then run the stored procedure. Do you have an example of that?

thanks again.

by tony hopkinson

used to doing this sort of thing in far more dynamic systems

Lets say's it's an insert and or update query on the invoices table, then in the trigger something like

Declare @mailQuery nVarchar(2048)

Select @mailQuery = Select 'Select i.Invoice_number, i.Invoice_date,i.Invoice_Value,Invoice_lines.* From Invoices i,Invoice_Lines il
Where il.Invoice_number = i.Invoice_number and i.invoice_number = ' + Convert(Varchar(16), inserted.Invoice_Number)
From inserted

then pass @mailQuery as the @Query value.

or something along those lines.

HtHs

This discussion is closed to new replies.