Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
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
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go
 
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBAC',
@email_address = 'Emailid@hotmail.com',
@display_name = 'Mohammed Abdul Mohsin',
@mailserver_name = 'mail.hotmail.com'
 
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 ;
 
--Table

CREATE TABLE dbo.inventory (
item varchar(50),
price money
)
GO
 
--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
BEGIN
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'EmailId@hotmail.com', @body= @msg,  @subject = 'SQL Server Trigger Mail', @profile_name = 'DBMailProfile'
END
GO
Posted 1-Jan-12 1:18am
Edited 1-Jan-12 1:29am
Mehdi Gholam253.4K
v4

1 solution

Rate this: bad
good
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[^]
  Permalink  
Comments
Espen Harlinn at 1-Jan-12 8:51am
   
Good call :)
ma.mohsin at 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 ;
GO
 
EXECUTE dbo.sysmail_start_sp ;
GO
 
Error Msg: Could not find stored procedure 'dbo.sysmail_start_sp'.
Mika Wendelius at 2-Jan-12 2:56am
   
The procedure is in msdb, so try using:
 
exec msdb..sysmail_start_sp;
ma.mohsin at 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
0 George Jonsson 215
1 Kornfeld Eliyahu Peter 169
2 OriginalGriff 120
3 PIEBALDconsult 110
4 BillWoodruff 85
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,294


Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 1 Jan 2012
Copyright © CodeProject, 1999-2014
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