SQLTeam.com | Weblogs | Forums

Send mail without query results


#1

I am trying to send a email from my database without the Query results.
If I remove the Query string the dbmail fails completly.
How do I acomplish that? I also want to be able to input data from my database into the body.

DECLARE @modtager varchar(MAX)
DECLARE @hfodselsdag date

SET @modtager = (SELECT Email FROM [Test].[dbo].[Medlemmer] WHERE Rolle = 'Formand')

IF EXISTS (SELECT fodselsdag FROM [Test].[dbo].[Medlemmer] WHERE DAY([Fodselsdag]) = DAY(GETDATE()+1) AND MONTH([Fodselsdag]) = MONTH(GETDATE()))
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMail',
@recipients = @modtager,
@query = 'SET NOCOUNT ON; SELECT nFornavn FROM [Test].[dbo].[Medlemmer] WHERE DAY([Fodselsdag]) = DAY(GETDATE()+1) AND MONTH([Fodselsdag]) = MONTH(GETDATE()); SET NOCOUNT OFF;',
@subject = 'Fødselar',
@Body = 'Test'
ELSE
PRINT GETDATE()
GO


#2

Not sure specifically what "fails completely" means: gets an error message? / doesn't send the mail but no error? / the entire batch fails?

I don't notice any problem with the rest of the code. so check for obvious things:
Make sure the profile name is correct, active and can send an email.
Make sure the Medlemmer table has a row(s) for " Rolle = 'Formand' ".
Make sure it has a row with a matching day and month.

Btw, be sure to specify a Unicode string for the subject, since it uses a Unicode char:
@subject = N'Fødselar',


#3

The current code Work BUT content of the mail I receive is this

Test
nFornavn                                                                                                                                                                                                                                                        

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

John                                                                                                                                                                                                                                                            
Jane                                                            

I would like to be able to put only John and Jane in the mail with maybe a small text.
If I comment out the Query line
-- @query = 'SET NOCOUNT ON; SELECT nFornavn FROM [Test].[dbo].[Medlemmer] WHERE DAY([Fodselsdag]) = DAY(GETDATE()+1) AND MONTH([Fodselsdag]) = MONTH(GETDATE()); SET NOCOUNT OFF;',
I don't receive an email, there is nothing in the DBmail log or in my Exchange log.

What I essentianlly want is to customise the email body with my text mixed with som data I pull out of my database.


#4

You can create an HTML email by hand-coding the HTML tags around the set of data...here is an example using XML to generate an HTML table:


#5

I would prefer to use plain text instead of HTML.


#6

Not sure why you wouldn't use HTML - it gives you the ability to format the email any way you want. With that said - if you don't want the Body information then don't populate the body parameter. Including the Query will include the results in a table in the body for you so you don't have to add anything.


#7

I just prefer plain text and it is possible, I have just a problem putting a date in the mail now.
This is my new code:
DECLARE @modtager varchar(MAX)
DECLARE @bodydata1 varchar(MAX)
DECLARE @bodydata2 varchar(MAX)
DECLARE @bodydata3 varchar(MAX)
DECLARE @bodydata4 date
DECLARE @BodyText date

SET @modtager = (SELECT Email FROM [Test].[dbo].[Medlemmer] WHERE Rolle = 'Formand')
SET @bodydata1 = (SELECT nFornavn FROM [Test].[dbo].[Medlemmer] WHERE DAY([Fodselsdag]) = DAY(GETDATE()+1) AND MONTH([Fodselsdag]) = MONTH(GETDATE()))
SET @bodydata2 = (SELECT nMellemnavn FROM [Test].[dbo].[Medlemmer] WHERE DAY([Fodselsdag]) = DAY(GETDATE()+1) AND MONTH([Fodselsdag]) = MONTH(GETDATE()))
SET @bodydata3 = (SELECT nEfternavn FROM [Test].[dbo].[Medlemmer] WHERE DAY([Fodselsdag]) = DAY(GETDATE()+1) AND MONTH([Fodselsdag]) = MONTH(GETDATE()))
SET @bodydata4 = (SELECT fodselsdag FROM [Test].[dbo].[Medlemmer] WHERE DAY([Fodselsdag]) = DAY(GETDATE()+1) AND MONTH([Fodselsdag]) = MONTH(GETDATE()))
SET @BodyText = + @bodydata1 +' '+ @bodydata2 +' '+ @bodydata3 +N' har fødselsdag d. '+ CONVERT(date, @bodydata4) +' han bliver [N]år.' + CHAR(13)+CHAR(10) + N'Send John en hilsen.'

IF EXISTS (SELECT fodselsdag FROM [Test].[dbo].[Medlemmer] WHERE DAY([Fodselsdag]) = DAY(GETDATE()+1) AND MONTH([Fodselsdag]) = MONTH(GETDATE()))
	EXEC msdb.dbo.sp_send_dbmail
		@profile_name = 'DBMail',
		@body_format = 'TEXT',
		@recipients = @modtager,
		@subject = N'Fødselar',
		@Body = @BodyText
ELSE
	PRINT GETDATE()
GO

This is the error I get:
Msg 402, Level 16, State 1, Line 13
The data types nvarchar(max) and date are incompatible in the add operator.


#8

To include the date you need to convert it to a string - you are converting it to a date. Change that to CONVERT(char(10), @bodydata4, 121) - or to the date format you want.

You can also simplify this by using SELECT to build the body text - and instead of checking for EXISTS you can check for @BodyText:

    Set @modtager = (SELECT Email FROM [Test].[dbo].[Medlemmer] WHERE Rolle = 'Formand';
        
 Select @BodyText = nFornavn + ' ' + nMellemnavn + ' ' + nEfternavn + N' har fødselsdag d. '
      + convert(char(10), fodselsdag, 121) + ' han bliver [N]år.' + char(13) + char(10) + N'Send John en hilsen.'
   From Test.dbo.Medlemmer
  Where day(Fodselsdag) = day(getdate() + 1)
    And month(Fodselsdag) = month(getdate());

     If @BodyText Is Not Null

The way you are checking - you are pulling data for all years for yesterday. If you only want yesterday then you could do this:

WHERE Fodselsdag >= dateadd(day, datediff(day, 0, getdate()) - 1, 0)
AND fodselsdag < cast(getdate() as date)

The above will perform better because you are not using functions on the column and an index can be used.