March 23, 2010 at 10:14 am
Hi i'm trying to run this script to test my e-mail database profile but when I executed it gives me an error saying : declare variable @ message error 137 something like that ! here is the code :
-- Here is a sample SQL code segment that sends a email from the SQL server
-- Of course, you must have created a database mail profile at this time.
set @message = 'Enjoy a wonderful Spring break!';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'yourProfile',
@recipients = 'GemMine@gmail.com', -- safe to send email to this address
@subject = 'One week escape',
@body = @message,
@body_format = 'HTML' ;
-- After a send_dbmail has been executed successfully,
-- you can verify the status with the following SQL statements:
SELECT * FROM msdb.dbo.sysmail_profile -- lists all database mail profiles
SELECT * FROM msdb.dbo.sysmail_allitems -- lists all mail items activated
SELECT * FROM msdb.dbo.sysmail_sentitems -- lists all delivered emails
SELECT * FROM msdb.dbo.sysmail_unsentitems -- lists all undelivered emails
-- Additional diagnostic views that may provide useful information
select * from sys.triggers -- displays all triggers
select * from sys.sysusers -- shows allusers and their permissions
select * from sys.sysmessages -- all system message logs
select * from sys.messages -- same as sys.sysmessages
select * from sys.events -- lists the events occurred
March 23, 2010 at 10:19 am
if that is the full script you are using. You must have a DECLARE statement for the @message. You could also just put the text straight on the line without using the @message.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 23, 2010 at 2:31 pm
Okay so where ? specificlay on the text would I put the text straight on the line without using the @message.
March 25, 2010 at 1:35 pm
As Shawn pointed out, you need to declare the variable before you can use it
DECLARE @message varchar(255)
set @message = 'Enjoy a wonderful Spring break!';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'yourProfile',
@recipients = 'GemMine@gmail.com', -- safe to send email to this address
@subject = 'One week escape',
@body = @message,
@body_format = 'HTML' ;
Or you can hard set the message
--set @message = 'Enjoy a wonderful Spring break!';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'yourProfile',
@recipients = 'GemMine@gmail.com', -- safe to send email to this address
@subject = 'One week escape',
@body = 'Enjoy a wonderful Spring break!',
@body_format = 'HTML' ;
March 25, 2010 at 1:39 pm
Awesome thank you so muck for pointing out the details !
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply