Click here to Skip to main content
12,813,626 members (33,863 online)
Rate this:
Please Sign up or sign in to vote.
Dear All,

I am using SQL Server 2005 & it's unable to send an email when I am inserting a record in the inventory table. It's showing Mail queued after insert a record in the inventory table.

Please let me know the solution as early as possible.

Note: It's showing 'unsent' in the sent_status column when I am execute below query.
" SELECT 'sysmail_allitems' as TableName, * FROM msdb.dbo.sysmail_allitems "

I write the queries as follows:
--Enable SQL SMail
use PracticeDB
sp_configure 'show advanced options',1
reconfigure with override
sp_configure 'Database Mail XPs',1

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBAC',
@email_address = '',
@display_name = 'Mohammed Abdul Mohsin',
@mailserver_name = ''

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBPN'

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DBPN',
@account_name = 'DBAC',
@sequence_number = 1 ;


CREATE TABLE dbo.inventory (
item varchar(50),
price money

--Trigger for Inventory Table

CREATE TRIGGER Trg_Inventory ON dbo.inventory AFTER INSERT AS
DECLARE @price money
DECLARE @item varchar(50)
SET @price  = (SELECT price FROM inserted)
SET @item = (SELECT item FROM inserted)
IF @price >= 1000
DECLARE @msg varchar(500)
SET @msg = 'Expensive item "' + @item + '" entered into inventory at $' + CAST(@price as varchar(10)) + '.'
EXEC msdb.dbo.sp_send_dbmail @recipients=N'', @body= @msg,  @subject = 'SQL Server Trigger Mail', @profile_name = 'DBMailProfile'
Posted 1-Jan-12 1:18am
Updated 1-Jan-12 1:29am
Mehdi Gholam333.6K

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Check that the service broker is actually running. For example try staring the broker using sysmail_start_sp[^]
Espen Harlinn 1-Jan-12 8:51am
Good call :)
ma.mohsin 2-Jan-12 0:03am
It's getting an error 'Could not find Stored Procedure'. How I can fix it. Please reply to me as soon as possible?

USE PracticeDB ;

EXECUTE dbo.sysmail_start_sp ;

Error Msg: Could not find stored procedure 'dbo.sysmail_start_sp'.
Mika Wendelius 2-Jan-12 2:56am
The procedure is in msdb, so try using:

exec msdb..sysmail_start_sp;
ma.mohsin 2-Jan-12 5:16am
Dear Mr.Mika, It's running fine, but I am using PracticeDB Database.

Again, I am getting the same problems. It's failed to send an email from PracticeDB. Do have any solution?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170308.1 | Last Updated 1 Jan 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100