May 12, 2010 at 9:33 am
Hi - I'm using sp_send_dbmail in a stored proc.
When executing my stored proc as sa it works fine, when executed as a test user I don't get an error, the return code is 0 but the output mailitem_id is returned as null. I don't think the mail is getting into the queue but I can't see why.
On 2005 sp3. Any ideas.
NB logging is as my test user I can execute sp_send_dbmail fine, the mail is received and the mailitem_id is returned as an integer.
Allen
May 12, 2010 at 9:44 am
Can you please include the exact line of code where you are calling sp_send_dbmail?
Can you see the "Mail queued." message, which is returned on success?
When the user sending the message does not have permission to do so, sp_send_dbmail returns an error.
May 12, 2010 at 11:32 am
Hi elutin,
Code is below (a bit messy but a work in progress)
I don't see the mail queued message because the sp_send_dbmail is in a stored proc. Interestingly running the code of the stored proc in SSMS fails to queue the mail message but no error is returned and no mail queued message is printed.
The test user does have permissions to send mail and can execute sp_send_dbmail in SSMS sucessfully (member of DatabaseMailUserRole in msdb).
USE [PIMS2]
GO
/****** Object: StoredProcedure [dbo].[rpEmailFilenameList] Script Date: 05/12/2010 18:30:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[rpEmailFilenameList] @WeeklyLeafletID INT, @CCEmailAddress VARCHAR(255)
AS
BEGIN
DECLARE @WeeklyLeaflet AS DATETIME
DECLARE @WeeklyLeafletString AS VARCHAR(11)
--SET @WeeklyLeaflet = '03-JAN-2010'
--SET @WeeklyLeaflet = '11-APR-2010'
DECLARE @DayNumber AS INT
DECLARE @WeekNumber AS INT
DECLARE @YearNumber AS INT
DECLARE @WeekString AS CHAR(2)
DECLARE @YearString AS CHAR(2)
DECLARE @Error AS INT
DECLARE @MailItemID AS INT
SELECT @WeeklyLeaflet = OnSaleDate FROM tblWeeklyLeaflet WHERE WeeklyLeafletID = @WeeklyLeafletID
SET @DayNumber = DATEPART(dy, @WeeklyLeaflet)
--SELECT @DayNumber
SET @WeekNumber = (@DayNumber / 7 + 1)
--SELECT @WeekNumber
SET @WeekString = RIGHT('0' + CAST(@WeekNumber AS VARCHAR(2)), 2)
--SELECT @WeekString
SET @YearNumber = DATEPART(yy, @WeeklyLeaflet) - 2000
SELECT @YearString = RIGHT('0' + CAST(@YearNumber AS VARCHAR(2)), 2)
SET @WeeklyLeafletString = UPPER(REPLACE(CONVERT(VARCHAR(11), @WeeklyLeaflet, 113), ' ', '-'))
--SELECT * FROM tblWeeklyLeaflet
--SELECT * FROM vwABF
DECLARE @FilePrefix AS CHAR(5)
SET @FilePrefix = 'W' + @WeekString + @YearString
DECLARE @Query AS VARCHAR(4000)
SET @Query =
'SET NOCOUNT ON ; SELECT
CAST(ProductCode AS VARCHAR(10)) AS [Product Code],
dbo.udfMagicNumber(ABFID) AS [Magic Number],
CAST(ProductDescription AS VARCHAR(50)) AS [Product Description],'''
+ @FilePrefix
+ ''' + dbo.udfGenerateFileName(ProductDescription) + ''-'' + dbo.udfMagicNumber(ABFID) + ''.eps'' AS [Aldi Filename]
FROM
tblABF
WHERE
OnSaleDate = ''' + @WeeklyLeafletString +
''' ORDER BY ProductDescription '
-- + ''-' + dbo.udfMagicNumber(ABFID) + '.eps' AS [File Name]
--PRINT @Query
--EXEC (@Query)
DECLARE @DistList AS VARCHAR(MAX)
SET @DistList = 'allen.davidson@europe.mccann.com'
DECLARE @Subject AS VARCHAR(255)
SET @Subject = 'Aldi Filenames for W' + @WeekString + @YearString
DECLARE @CRLF AS CHAR(2)
SET @CRLF = CHAR(10) + CHAR(13)
DECLARE @Body AS VARCHAR(1000)
SET @Body = 'Please use the attached filenames for Aldi Images W' + @WeekString + @YearString + '.' + @CRLF
+ 'If the filename needs to be changed do not change anything after the - (dash).'
+ @CRLF
+ 'If this leaflet is a not a normal weekly leaflet please check with studio for the correct names.'
+ @CRLF
DECLARE @query_attachment_filename AS VARCHAR(50)
SET @query_attachment_filename = 'Aldi Filenames ' + 'W' + @WeekString + @YearString + '.txt'
EXEC
msdb.dbo.sp_send_dbmail
@profile_name = 'DB Mail',
@recipients = @DistList,
@copy_recipients = @CCEmailAddress,
@subject = @Subject,
@body = @Body,
@body_format = 'TEXT',
@query = @Query,
@execute_query_database = 'PIMS2',
@attach_query_result_as_file = '1',
@query_attachment_filename = @query_attachment_filename,
-- [ , [ @query_result_header = ] query_result_header ]
@exclude_query_output = '1',
@mailitem_id = @MailItemID OUTPUT
SET @Error = @@ERROR
SELECT @Error, @MailItemID
RETURN (@Error)
END
May 12, 2010 at 12:32 pm
add the following to see what this sp returns:
DECLARE @RtnCode INT
EXEC @RtnCode = msdb.dbo.sp_send_dbmail...
Check what is returned in the @RtnCode.
0 - success, 1- failure.
BUT! There are some more undocumented return values which indicate failure ...
May 12, 2010 at 1:10 pm
Thanks - I'm getting a return code of 1. If I omit the @query parameter I get a return code of 0 and the email is sent OK.
What does 1 indicate (other than failure)
I think I have not granted EXEC privelidges on the function in the @query parameter to the test user. Will test that now.
May 12, 2010 at 1:23 pm
Yes - that was it!
Another lesson learnt.
May 12, 2010 at 1:24 pm
Check this one:
http://sql-articles.com/blogs/return-code-values-documentation-for-sp_send_dbmail-procedure/
Most likely you have an error in your query, which can be due to some security issues...
Check what the query string contains and execute it within SP.
Actually, I can see the problem now:
You passing the query to be executed by sp_send_dbmail which is in msdb database. Your query wil be executed in the msdb database where there is no your table nor user defined function exist! Use fully qualified names for each db object mentioned in your query eg. [dbname].[namespace].[object name] (... FROM MyDB.dbo.MyTable ...)
Cheers,
Me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply