Timeout Expired

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Timeout Expired

Timeout Expired

Rate Topic

Display Mode

Topic Options

Author

Message

UmaShankar Patel

Posted Wednesday, December 19, 2012 11:08 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:13 PM
Points: 139, Visits: 203

I fire a trigger on tblAdmin
as

CREATE TRIGGER [dbo].[trg_mailupdation]
ON [dbo].[tblAdmin]
After UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'crm',
@recipients = 'umashankar@gmail.com',
@body_format = 'HTML',
@query = 'select adminname,resposibility1,mailid from customer.dbo.tblAdmin' ,
@subject = 'Admin Update Details',
@attach_query_result_as_file = 1;

END

Now when I execute select *from tblAdmin
Timeout Expired error occured.
Nither this trigger deleted nor disable.

What are causes?
Please help.........

Thanks.......
-----------------------------------
My Blog | Articles

Post #1398781

AlexSQLForums

Posted Thursday, December 20, 2012 8:27 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 6:53 AM
Points: 1,227, Visits: 1,712

Do you realize you're sending the whole tbladmin in your email?
Triggers use inserted and deleted tables and your query is not specifying any of them
select adminname,resposibility1,mailid from customer.dbo.tblAdmin
you should change it to
select adminname,resposibility1,mailid from customer.dbo.tblAdmin ta inner join inserted i on ta.your key = i.yourkey
that will give you the new updated record.
Plus it's bad practice to include sp_send_dbmail inside a trigger. if the mail server stalls you're ()#&%(#^%.

Alex S

Post #1399006

« Prev Topic | Next Topic »

Permissions