DBmail profile name is not valid

[Imakeoil]

I have been trying all week to get my new sql 2005 server to send out auto emails. The test emails come through fine but the EXEC msdb.dbo.sp_send_dbmail do not. Earlier they said that the email was queuing but no email was sent to my inbox. Now it seems I have regressed.
I am seeing this error 'Procedure sysmail_verify_profile_sp, Line 42
profile name is not valid'
I have set up two profiles and 3 accounts.
I have run
SELECT [profile_id]
,[name]
,[description]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_profile]
and copied/pasted the profile name into my stored procedure. I have rebuilt my profiles, and both are members of the 'Data base mail user role'. I am stumped and frustrated. Hopefully some one has some suggestions. Thanks in advance, I really appreciate all the help I get from this forum.

Cheers

 

[SQLBill]

You don't show the results from your query or an example of the way you are sending mail....but we have seen an issue with the profile being (for example):

My SS2005 Mail Profile.

And then users trying to send using My SS2005 Mail. It won't work, you need that 'Profile' at the end as it is part of the profile name.

-SQLBill



The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Imakeoil]

Thanks SQLBill.

Since I copied and pasted the profile name out of the result pane of the sql query that listed my profiles I don't think the name is wrong, am I missing something?

Here is the first sql query:


SELECT [profile_id]
,[name]
,[description]
,[last_mod_datetime]
,[last_mod_user]

FROM [msdb].[dbo].[sysmail_profile]

Results:

1 ase.reports@xxx.com Account for emailing daily reps 2010-06-30 16:57:41.183 CORP\caol
4 SAS Reports Testing 2010-06-30 16:57:41.200 CORP\caol


And here is the query used to generate the email:

USE [smtp]
GO
/****** Object: StoredProcedure [dbo].[send_5500_797_loads] Script Date: 06/29/2010 16:06:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--ALTER PROCEDURE [dbo].[send_5500_797_loads] (@startd int, @endd int) AS

/*
purpose: material movement 2007
author: xxx
notes: 01-31-08 - initial revision.
notes: 02-12-09 - revision
revised to include both 5500.
*/
--/*
-- test code only
DECLARE @sDate datetime, @eDate datetime, @startd int, @endd int
SET @sDate = '2010-05-24'
SET @eDate = '2010-05-24'
SET @startd = (SELECT shiftindex FROM Powerview.dbo.hist_exproot WHERE shiftdate = @sDate AND shift# = '1')
SET @endd = (SELECT shiftindex FROM Powerview.dbo.hist_exproot WHERE shiftdate = @eDate AND shift# = '1')
DROP TABLE #t_dumps
DROP TABLE #t_exproot
DROP TABLE #t_output
--*/
-- build temp tables
SELECT * INTO #t_dumps FROM Powerview.dbo.hist_dumps WHERE shiftindex BETWEEN @startd AND @endd
SELECT * INTO #t_exproot FROM Powerview.dbo.hist_exproot WHERE shiftindex BETWEEN @startd AND @endd

-- calculations
SELECT #t_exproot.shiftindex,
#t_exproot.[name] AS Shift,
COUNT(*) AS loads--, #t_dumps.excav
-- SUM(dumptons) AS tonnes
INTO #t_output
FROM #t_dumps
INNER JOIN #t_exproot ON #t_exproot.shiftindex = #t_dumps.shiftindex
WHERE excav LIKE 'S557%' or excav like 'S8004'
-- AND truck LIKE 'T1%'
AND Powerview.dbo.DefineTruckCompany(truck) = 'xxx'
GROUP BY #t_exproot.shiftindex, #t_exproot.[name]--, excav
ORDER BY #t_exproot.shiftindex, #t_exproot.[name]--, excav
--/*
SELECT Shift, loads--, excav
FROM #t_output


DECLARE @profile_name varchar(1000),
@recipients varchar(1000),
@subject varchar(1000),
@body varchar(5000),
@copy_recipients varchar(1000),
@profile_id varchar (5)

SET @body = ''

DECLARE @oldRow varchar(20)
DECLARE @maxRow varchar(20)

SET @oldRow = (SELECT MIN([shiftindex]) FROM #t_output)
SET @maxRow = (SELECT MAX([shiftindex]) FROM #t_output)

WHILE @oldRow <= @maxRow
-- WHILE 1 = 1
BEGIN
SET @body = @body +
+ CAST((SELECT LEFT(Shift,21) FROM #t_output WHERE shiftindex = @oldRow) AS varchar)
+ CHAR(9)
+ CAST((SELECT cast(loads as decimal(10,0)) FROM #t_output WHERE shiftindex = @oldRow) AS varchar)
+ CHAR(13) + CHAR(10)
-- if @oldRow = @maxRow break
SET @oldRow = (SELECT TOP 1 shiftindex FROM #t_output WHERE shiftindex > @oldRow)
END

PRINT @body
SET @profile_name = 'ase.reports@xxx.com'
-- SET @profile_id = '1'
--SET @recipients = 'xxx@xxx.ca'
SET @recipients = 'xxx.xxx@xxx.com'
SET @subject = 'NA - 5500/797 loads'
SET @copy_recipients = 'xxx.xxx@xxx.com'

PRINT @body

EXEC msdb.dbo.sp_send_dbmail @subject, @body, @profile_name, @recipients, @copy_recipients
--*/
-- cleanup
--
DROP TABLE #t_dumps
DROP TABLE #t_exproot
DROP TABLE #t_output
--
-- GO

Here is the message pane:

Msg 3701, Level 11, State 5, Line 17
Cannot drop the table '#t_dumps', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 18
Cannot drop the table '#t_exproot', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 19
Cannot drop the table '#t_output', because it does not exist or you do not have permission.

(920 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
24-MAY-10 Day Shift 125
24-MAY-10 Day Shift 125
Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42
profile name is not valid

Hopefully this will help you help me.

Thanks

 

[Imakeoil]

After viewing another post here

http://www.tek-tips.com/viewthread.cfm?qid=1594389&page=1

I changed the exec line to this:
EXEC msdb.dbo.sp_send_dbmail @subject = 'NACG - 5500/797 loads', @body = @body, @profile_name = 'ase.reports@xxx.com', @recipients = 'xxx@xxx.com', @copy_recipients ='xxx@xxx.com'

Now it says the mail is queued. And it came through my in box. Great Success!

Thanks SQLBill.

But, since I had set the @params in my sql script, why do I have to restate them in the exec line?

Cheers

 

[SQLBill]

I think it is because it is looking for something to equal those params. Since the 'set up' for sp_send_dbmail includes @<param> it doesn't automatically know to fill in those params with what you have declared and set. You would have to do it something like:

sp_send_dbmail @body = @body, @profile = @profile, @recipients = @recipients, @copy_recipients = @copy_recipients

I believe that would work.

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875