- Database Mail with HTML formatting
- Database Mail with HTML formatting
- Databases: Need to Send a formatted HTML Email via
- Send a body HTML > 8000 chars long trough database Mail
- Firigid Shortcomings
- Option One: How Long Is A String?
- Option Two: XML All The Things
- Option Three: Yildirim Kocdag
- How can I email query results formatted in html from MSSQL 2000 server?
Database Mail with HTML formatting
I wanna know how do I put check num information in the body of the email as right now I am just generating a table which gives them remittance information. Question: I am working on sending information to our customers about the checks they may expect in mail soon.
Database Mail with HTML formatting
I am working on sending information to our customers about the checks they may expect in mail soon. I wanna know how do I put check num information in the body of the email as right now I am just generating a table which gives them remittance information.
Basically I want the body of the email to say
» Check num : 1234567 has been issued to you. So far I have the following query
declare @docnum nvarchar(50) declare @numatcard nvarchar(50) declare @checknum nvarchar(50) declare @checkAmt nvarchar(100) declare @EMaIL nvarchar(200) declare @Date nvarchar(200) declare @table nvarchar(max) declare @message nvarchar(Max) declare @cardcode nvarchar(Max) select @docnum = t2.docnum, @Cardcode=t3.cardcode, @numatcard = t2.numatcard, @checknum = t5.checknum, @Checkamt= T5.CheckSum, @EMAIL= T3.E_Mail , @Date=Convert(Nvarchar(12),T0.Docdate,101) from SAP_PROD.[dbo].[OVPM]T0 INNER JOIN SAP_PROD.[dbo].[VPM2] T1 ON T0.DocNum = T1.DocNum left join SAP_PROD.[dbo].VPM1 T5 on T0.DocEntry=T5.docnum left JOIN SAP_PROD.[dbo].OPCH T2 ON T1.DocEntry = T2.DocEntry And T1.InvType=T2.ObjType left JOIN SAP_PROD.[dbo].ORPC T4 ON T1.DocEntry = T4.DocEntry And T1.InvType=T4.ObjType left JOIN SAP_PROD.[dbo].ORIN T6 ON T1.DocEntry = T6.DocEntry And T1.InvType=T6.ObjType left join SAP_PROD.[dbo].OCRD T3 on T3.CardCode=T0.CardCode where T0.[DocDate]=CAST(getdate()-1 as date) --and --T5.CheckNum=32770 --T0.[DocDate]=CAST(getdate()as date)and t3.cardcode='CHIEF' set @message ='Dear Vendor, Check num : ' + @checknum + ' in the amount of ' + @checkAmt+ ' has been processed and will be mailed within 48 hours. Please see remittance information below. If you have any questions or concerns regarding this payment please contact a member of our Accounts Payable staff. Regards, Accounts Payable Department ' DECLARE EmailCursor CURSOR FOR Select @docnum, @Cardcode, @numatcard,@checknum,@checkAmt,@Checkamt,@EMAIL,@Date from SAP_PROD.[dbo].[OVPM]T0 INNER JOIN SAP_PROD.[dbo].[VPM2] T1 ON T0.DocNum = T1.DocNum left join SAP_PROD.[dbo].VPM1 T5 on T0.DocEntry=T5.docnum left JOIN Solitude.SAP_PROD.[dbo].OPCH T2 ON T1.DocEntry = T2.DocEntry And T1.InvType=T2.ObjType left JOIN SAP_PROD.[dbo].ORPC T4 ON T1.DocEntry = T4.DocEntry And T1.InvType=T4.ObjType left JOIN SAP_PROD.[dbo].ORIN T6 ON T1.DocEntry = T6.DocEntry And T1.InvType=T6.ObjType left join SAP_PROD.[dbo].OCRD T3 on T3.CardCode=T0.CardCode where T0.[DocDate]=CAST(getdate()-1 as date) OPEN EmailCursor FETCH NEXT FROM EmailCursor INTO @docnum, @Cardcode, @numatcard,@checknum,@checkAmt,@Checkamt,@EMAIL,@Date WHILE (@@FETCH_STATUS = 0) BEGIN EXEC msdb.dbo.sp_send_dbmail @profile_name = 'abc', @recipients=@email, @subject = 'Remittance', @body = @message, @body_format = 'HTML' FETCH NEXT FROM EmailCursor INTO @docnum, @Cardcode, @numatcard,@checknum,@checkAmt,@Checkamt,@EMAIL,@Date END Close EmailCursor Deallocate EmailCursor SELECT @body = 'Check num : ' + convert(varchar(50),t5.checknum) +' has been issued to you' FROM [dbo].[OVPM] T0 INNER JOIN [dbo].[VPM2] T1 ON T0.DocNum = T1.DocNum LEFT JOIN [dbo].VPM1 T5 on T0.DocEntry=T5.docnum INNER JOIN [dbo].OPCH T2 ON T1.DocEntry = T2.DocEntry And T1.InvType=T2.ObjType LEFT JOIN [dbo].OCRD T3 on T3.CardCode=T0.CardCode obviously make the from clause return the correct check num
maybe this simplified example will be a little clearer
declare @docnum nvarchar(50) declare @numcard nvarchar(50) declare @checknum nvarchar(50) declare @table nvarchar(max) declare @message nvarchar(200) select @docnum = t0.docnum, @numcard = t2.numcard @checknum = t5.checknum from . set @message = 'Check num : ' + @checknum + ' has been issued to you' set @table = 'Docnum numcard checknum' + ' ' + @docnum + ' ' + '' + @numcard + ' ' + '' + @checknum + '
' Sql server 2008 email notifications, Viewed 457 times. 1. I have set up a profile and operator within SQL Server 2008. I also have an operator that is a group in MS Outlook. When I use …
Databases: Need to Send a formatted HTML Email via
Databases : Need to Send a formatted HTML Email via Database Mail in Sql Server 2008 R2Helpful? Databases : Need to Send a formatted HTML …
Send a body HTML > 8000 chars long trough database Mail
UPDATE
I dont know why but instead of using the concatenation with the plus sign SET @bodyHTMLBuild = ‘ ‘ I have changed to CONCAT set @bodyHTMLBuild =CONCAT(‘ ‘)
And now all the Hmtl is successfully sent by Exec sp_send_dbmail @body = @bodyHTMLBuild, @body_format = ‘HMTL’ 🙂
the >8000 still is fixed by using the set @moreThan8000 = CONCAT(CAST(‘ 7999 chars. ‘) as VARCHAR(max) , CAST(‘More chars (e.g 5000)’) as VARCHAR(max))
select ****(@moreThan8000) will show : 12999
ORIGINAL POST
I am trying to send an HTML body Email using [dbo].[sp_send_dbmail] when a specific table triggers and insert .
the problem is that is truncating the Mail up to 8000 chars I have read some forums about Casting the string as Varchar(max) but I dont see is working.
Here the code: Please any help is greatly appreciated
`DECLARE @bodyHTMLBuild NVARCHAR(max) DECLARE @ProdId VARCHAR(30) DECLARE @Company VARCHAR(30) DECLARE @CreatedBy VARCHAR(30) DECLARE @itemId VARCHAR(30) set @ProdId = 'prod' set @Company = 'Comp' set @CreatedBy = 'CreatedBy' set @itemId = 'ItemId' SET @bodyHTMLBuild = CAST(' BOM Circularity Detected in Production Nr: '+@ProdId+':
Axapta has detected a BOM Circularity in ' AS VARCHAR(MAX) ) + CAST(@Company AS VARCHAR(MAX)) + CAST('
for the related Item Number: ' AS VARCHAR(MAX)) +CAST(@ItemId AS VARCHAR(MAX))+ CAST('.
Production resposable: ' AS varchar(MAX)) +CAST(@CreatedBy AS VARCHAR(MAX))+ CAST('.
fix as soon as possible!
Please do not reply to this mail.
' AS VARCHAR(MAX)) +CAST(@Company AS VARCHAR(MAX))+ CAST(' Sp. z.o.o
Anything wrong? please forward to NC@aks-poland.pl.
' AS VARCHAR(MAX)) print @bodyHTMLBuild Firigid Shortcomings
Print can only print so much, but there are a couple things you can do to work around that.
Option One: How Long Is A String?
Figure out how long your string is, and print substrings of it.
print ****(@bodyHTMLBuild); print SUBSTRING(@bodyHTMLBuild, 1, 8000); print SUBSTRING(@bodyHTMLBuild, 8000, 16000);
Option Two: XML All The Things
If you want something that you can click on, use this:
SELECT @bodyHTMLBuild AS [processing-instruction(_)] FOR XML PATH('');
Option Three: Yildirim Kocdag
You can use Helper_LongPrint to print longer strings.
Database mail — Can SQL Server 2008 R2 get email name, The answer seems to be «no, you can’t». Thanksfully our email addresses were consistant, so I was able to write a function that transforms the [firstname …
How can I email query results formatted in html from MSSQL 2000 server?
The only system SP I have is master.dbo.xp_smtp_sendmail and I can’t make it work (or I just don’t know how). I cannot upgrade the DB at this time. I’m trying something like this:
@body_text=N'SELECT TOP 100 * FROM payhistory WHERE entered BETWEEN DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) AND DATEADD(wk,DATEDIFF(wk,7,GETDATE()),4) AND desk=020 ORDER BY entered DESC exec master.dbo.xp_smtp_sendmail @server = 'server', @from = 'administrator@mydomain.com', @to = 'me@mydomain.com', @subject = 'HTML Testing. ', @type = 'text/html', @message = @body_text
I can do this easily in 2005 and up using «msdb.dbo.sp_send_dbmail» but I am stuck on 2000 due to the size of the DB for now.
Any help or suggestions would be greatly appreciated.
It looks like you’re missing an apostrophe from the end of the @body_text parameter. This fixes that problem:
@body_text=N'SELECT TOP 100 * FROM payhistory WHERE entered BETWEEN DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) AND DATEADD(wk,DATEDIFF(wk,7,GETDATE()),4) AND desk=020 ORDER BY entered DESC'; exec master.dbo.xp_smtp_sendmail @server = 'server', @from = 'administrator@mydomain.com', @to = 'me@mydomain.com', @subject = 'HTML Testing. ', @type = 'text/html', @message = @body_text;
Also, You might consider using xp_sendmail from SQL Server 2000. The syntax is:
EXEC xp_sendmail @recipients = 'recipients' , @message = 'message' , @query = 'query' , @attachments = 'attachments ;. n ' , @copy_recipients = 'copy_recipients ;. n ' , @blind_copy_recipients = 'blind_copy_recipients ;. n ' , @subject = 'subject' , @type = 'type' , @attach_results = 'attach_value' , @no_output = 'output_value' , @no_header = 'header_value' , @width = width , @separator = 'separator' , @echo_error = 'echo_value' , @set_user = 'user' , @dbuse = 'database'
Of course, xp_sendmail requires an Outlook profile be setup on the server, which might be prohibitive.
SQL Server 2008 R2 Query For Failed Email Notifications, Need to Send a formatted HTML Email via Database Mail in Sql Server 2008 R2. 0. Require to Send a formatted HTML Email via Database …