May 12, 2011 at 10:35 am
Please take a look at this:
ALTER PROCEDURE [dbo].[P_SENDEMAILNOTIFICATION]
@Days INT = 0
AS
BEGIN
DECLARE @EmailId NVARCHAR(1000)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profile_Admin' ,
@recipients = 'myemail@company.com',
@body = 'email body',
@body_format = 'HTML' ,
@subject = 'email subject'
END
I don't get any errors but the email is not being sent. Looks like the EXEC sp_send_dbmail is being skipped. If I use the GO keyword to separate it into it's own block than it will work but this is just a simplified version and I need it to be there in the real procedure.
Any ideas?
May 12, 2011 at 10:48 am
Have you checked to so if it is in the queue but not sent?
http://msdn.microsoft.com/en-us/library/ms190630.aspx
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2011 at 10:49 am
Sorry double post...:w00t:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2011 at 10:57 am
Yes I checked it's not in queue
May 12, 2011 at 11:07 am
does this return results/an explanation for unsent mail/failed mail?
SELECT
err.[description],
fail.*
FROM [msdb].[dbo].[sysmail_event_log] err
inner join [msdb].[dbo].[sysmail_faileditems] fail
ON err.mailitem_id = fail.mailitem_id
Lowell
May 12, 2011 at 11:09 am
That is weird.
I don't know how it could be skipping it but perhaps you could try putting a SELECT or Print Statement just before the EXEC Statement.
After the last argument in the EXEC check the value of @@Error by displaying the results.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2011 at 11:17 am
Lowell (5/12/2011)
does this return results/an explanation for unsent mail/failed mail?
SELECT
err.[description],
fail.*
FROM [msdb].[dbo].[sysmail_event_log] err
inner join [msdb].[dbo].[sysmail_faileditems] fail
ON err.mailitem_id = fail.mailitem_id
This returns some really old errors nothing within this year
Welsh Corgi (5/12/2011)
That is weird.I don't know how it could be skipping it but perhaps you could try putting a SELECT or Print Statement just before the EXEC Statement.
After the last argument in the EXEC check the value of @@Error by displaying the results.
I can't put SELECT or PRINT near EXEC error: Incorrect syntax near EXEC
hmm
May 12, 2011 at 11:20 am
euroray (5/12/2011)
Please take a look at this:I don't get any errors but the email is not being sent. Looks like the EXEC sp_send_dbmail is being skipped. If I use the GO keyword to separate it into it's own block than it will work but this is just a simplified version and I need it to be there in the real procedure.
Any ideas?
it might be this: a GO keyword cannot exist inside a stored procedure...what it would actually do is end the stored proc's body wherever that statement exists, adn the code below the GO, where you THINK it is part of the boldy of the proc, would be executed as if it were a stand alone TSQL statement.
make sure you ALTER the proc, and make sure there are no GO statements.
Lowell
May 12, 2011 at 11:24 am
Where exactly are you putting it? What line?
You Can't get the value of @@Error?
Regards,
Welsh
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2011 at 11:35 am
Welsh Corgi (5/12/2011)
Where exactly are you putting it? What line?You Can't get the value of @@Error?
Regards,
Welsh
like so:
ALTER PROCEDURE [dbo].[P_SENDEMAILNOTIFICATION]
@Days INT = 0
AS
BEGIN
DECLARE @EmailId NVARCHAR(1000)
SELECT
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profile_Admin' ,
@recipients = 'myemail@company.com',
@body = 'email body',
@body_format = 'HTML' ,
@subject = 'email subject'
IF @@ERROR != 0
BEGIN
PRINT 'Error occured'
END
END
May 12, 2011 at 11:42 am
it's syntax: remove the word "SELECT" just before EXEC;
looks like an artifact from trimming the code.
Lowell
May 12, 2011 at 11:45 am
Your SELECT Statement is the cause of the error. Replace with a print statement to make it easier.
Also you want to assign the @@ERROR to a variable @ERROR and display the error code. This may not be applicable in this case but it is good practice.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2011 at 11:50 am
It is the cause of the synatx error but email is still not being sent, in fact something as simple as this does not print anything:
ALTER PROCEDURE [dbo].[P_SENDEMAILNOTIFICATION]
@Days INT = 0
AS
BEGIN
DECLARE @EmailId NVARCHAR(1000)
PRINT 'Write something'
END
unless I put it outside of the ALTER PROC:
ALTER PROCEDURE [dbo].[P_SENDEMAILNOTIFICATION]
@Days INT = 0
AS
BEGIN
DECLARE @EmailId NVARCHAR(1000)
PRINT 'Write something' --this does not work
END
GO
PRINT 'Write something' --this works
May 12, 2011 at 11:57 am
DECLARE @err int
SELECT @err = @@error
IF @err <> 0
PRINT '@err is ' + ltrim(str(@err)) + '.'
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply