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
