February 6, 2010 at 9:26 am
I need help with the following code. I want to send an email based on the query results from the following code. I would love to either send these results via an excel spread sheet attachment or text file. OR I would like to send an HTML e-mail with the results in a nice e-mail format. I am using SQL 2000, using CDOSYS
The following code does not execute. I receive an error at the + EXEC (@strQuery)
I have verified that the error is not occurring because of the length of my variables @strQuery and @strBody
DECLARE @strQuery nvarchar(4000), @strBody nvarchar(4000)
SET @strQuery = ('SELECT
[SCOUTS].[SCT_FNAME] + '' '' + [SCOUTS].[SCT_LNAME] AS [Scout Name],
[REF_DEN].[DEN_NUMBER] AS [Den Number],
[Belt Loop] = CASE
WHEN [xref_BLPIN_SCT].[BL] = 1 THEN [REF_BELTLOOP_PIN].[BLPIN_NAME] + '' Beltloop''
ELSE ''''
END,
[Pin] = CASE
WHEN [xref_BLPIN_SCT].[PIN] = 1 THEN [REF_BELTLOOP_PIN].[BLPIN_NAME] + '' Pin''
ELSE ''''
END,
(convert(varchar,[xref_BLPIN_SCT].[UPDATE_DATE],101)) AS [Date Entered]
FROM
[dbo].[SCOUTS] [SCOUTS]
inner join [dbo].[xref_BLPIN_SCT] [xref_BLPIN_SCT]
on [SCOUTS].[SCT_ID] = [xref_BLPIN_SCT].[SCT_ID]
inner join [dbo].[REF_DEN] [REF_DEN]
on [SCOUTS].[REF_DEN_ID] = [REF_DEN].[DEN_ID]
inner join [dbo].[REF_BELTLOOP_PIN] [REF_BELTLOOP_PIN]
on [xref_BLPIN_SCT].[BLPIN_ID] = [REF_BELTLOOP_PIN].[BLPIN_ID]
WHERE datename(m,[xref_BLPIN_SCT].[UPDATE_DATE] ) = datename(m, getdate())
ORDER BY [REF_DEN].[DEN_NUMBER], [SCOUTS].[SCT_LNAME], [REF_BELTLOOP_PIN].[BLPIN_NAME]')
SELECT @strBody = 'Here is the Summary of Awards for the Month of: February:
' + EXEC (@strQuery)
EXEC sp_send_cdosysmail 'from email','to email','Test of CDOSYS',@strBody
February 6, 2010 at 10:54 am
I would run the query and insert the results into a temp table.
When sending the mail I'd use "SELECT cols FROM temp table".
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply