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
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',
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.
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:
I would prefer to use plain text instead of HTML.
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.
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.
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.