Trigger, Query in attachment - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trigger, Query in attachment

Author

Topic

pyromelana
Starting Member

23 Posts

Posted - 2009-03-17 : 08:38:28

Hello!

I want to send an email with Trigger and the query result should be in an attachment


ALTER TRIGGER [dbo].[Kundenstamm2]

ON [dbo].[YKUN0015]

FOR INSERT,UPDATE

AS
SET NOCOUNT ON

declare @query nvarchar(20)
declare @message nvarchar(50)

set @query = (select Name1 as Name from dbo.YKUN0015 WHERE NUMMER = 101289)


EXEC msdb..sp_send_dbmail

@recipients = 'status@mydomain.com',
@subject = 'Query in Emailattachment',
--@body = @query;
@query_attachment_filename = query
@attach_query_result_as_file = 1;

Can anyone give me a hint, how to solve this problem? Email works, query result in body works, sending files with sp_send_dbmail works, but all together result in attachment in email does not work... help!

pyromelana
Starting Member

23 Posts

Posted - 2009-03-17 : 10:18:44

anyone?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-17 : 10:59:41

why do you want to send email from trigger? can i ask how frequestly you want email to go? i think it would be better for you to schedule this as a sql job which sends the email in a periodic basis.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-17 : 11:08:51

well first couple obvious things are:
- @query is nvarchar(20) which isn't big enough to hold your query.
- The query you are providing is not an nvarchar value, it is an actual statement. You need to replace the parenthises with single quotes.

Just to re-enforce what Visakh said...
Generally speaking it is a very bad design to send email from a trigger. It will significantly slow down the user transaction and if there are any problems the user action will be rolled back.

Be One with the Optimizer
TG

pyromelana
Starting Member

23 Posts

Posted - 2009-03-17 : 11:57:03

Thx for your answers...

1.) It is only for training, so do not care about performance and frequence
2.) Why I do this? This should work - it has to work. Especially in this more than simple case (select name from tbl where city = 1111....). I know that query is bullshit, but with such a simple one you can proof your result
3.) ups, yes, declare @query nvarchar(max) is better
4.) Parantheses instead of single quotes or the other way round?
I am sure set @var = (select...) --correct me if I am wrong

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-17 : 13:17:58

it should be

set @query = 'select Name1 as Name from dbo.YKUN0015 WHERE NUMMER = 101289'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-17 : 13:20:02

quote:
I am sure set @var = (select...) --correct me if I am wrong

The parameters are all layed out and explained clearly in Books Online.


declare @query nvarchar(max)
set @query = 'select Name1 as Name from dbo.YKUN0015 WHERE NUMMER = 101289'

EXEC msdb..sp_send_dbmail
@recipients = 'status@mydomain.com',
@subject = 'Query in Emailattachment',
@body = N'Please see attachment for details'
@query = @query --this variable needs to be able to be EXEC'd ie: exec(@query)
@query_attachment_filename = 'Results.txt' --The attachment will be named this
@attach_query_result_as_file = 1;

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources