How to send email from a table in SQL Server

-
Create a new table named System Generated Email in SQL Server Database:

 CREATE TABLE [dbo].[B$System Generated Email](  
      [No] [int] NOT NULL,  
      [To Address] [varchar](250) NOT NULL,  
      [From Address] [varchar](100) NOT NULL,   
      [From Address Title] [varchar](250) NOT NULL,
      [CC Address] [varchar](250) NOT NULL,    
      [Email Subject] [varchar](MAX) NOT NULL,  
      [Email Body] [varchar](MAX) NOT NULL,  
      [Sent] [tinyint] NOT NULL,  
      [Email Sending Date] [datetime] NOT NULL,  
      [Email Sending Time] [datetime] NOT NULL,  
 CONSTRAINT suppliers_pk PRIMARY KEY (No)  
 )

This table will contains all system generated emails. Now we need to write a SQL Procedure which will create emails from System Generated Email Table and send through the SQL email profile.

Email profile can be found in Database Mail option:


SQL code for sending Email:

 USE msdb  
 GO  
 DECLARE @email_id varchar(250), @from_mail varchar(250), @cc_mail varchar(MAX),   
           @title varchar(250),@subject varchar(250), @mail_body varchar(MAX),  
           @No BIGINT, @max_id BIGINT, @Error Int, @lineNo int  
 DECLARE db_cursor CURSOR FOR   
 SELECT No        
 FROM [dbo].[System Generated Email]   
 WHERE [Sent] = 0  
 OPEN db_cursor    
 FETCH NEXT FROM db_cursor INTO @No    
 WHILE @@FETCH_STATUS = 0    
 BEGIN   
      SELECT   
         @lineNo = [No],  
            @email_id = [To Address],  
            @from_mail = [From Address Title] + '<' +[From Address] +'>',  
            @cc_mail = [CC Address1],  
            @subject = [Email Subject],  
            @mail_body = [Email Body1]   
      FROM [dbo].[System Generated Email]   
      WHERE [Sent] = 0 AND [No] = @No;  
      UPDATE      [dbo].[System Generated Email]   
      SET [Sent] = 1, [Email Sending Date] = CONVERT (date, SYSDATETIME()),   
                     [Email Sending Time] = '1754-01-01 ' + CAST(CONVERT (time(3), SYSDATETIME()) AS VARCHAR)  
      WHERE      [No] = @No  
      SELECT @Error = @@ERROR  
      print @Error  
      if @Error = 0   
      BEGIN  
           EXEC sp_send_dbmail @profile_name='TEST',  
                               @recipients= @email_id,  
                               @from_address=@from_mail,  
                               @copy_recipients = @cc_mail,  
                               @body_format='HTML',  
                               @subject=@subject,  
                               @body=@mail_body  
      END  
      ELSE   
      BEGIN  
           SET @subject = 'Error occurred for entry No ' + @lineNo  
           EXEC sp_send_dbmail @profile_name='TEST',  
                               @recipients= 'email@email.com',  
                               @from_address=@from_mail,  
                               @copy_recipients = @cc_mail,  
                               @body_format='HTML',  
                               @subject= @subject,  
                               @body='Error Ocured for sending mail'       
      END  
   FETCH NEXT FROM db_cursor INTO @No   
 END    
 CLOSE db_cursor    
 DEALLOCATE db_cursor

here we take only not sent email records then create and send emails accordingly. We also update the Sent column with true so that email sent only once when run the procedure.

This procedure can be add to SQL schedule so system can send email automatically.

Comments

Post a Comment

Popular posts from this blog

Different use of AL Find Functions for Filters in Dynamics 365 Business Central

-
Image
This post provides tips about different find function in Business Central, including both long-standing C/AL strategies and newer concepts like partial records. FindSet vs FindFirst/FindLast FindSet and FindFirst/FindLast are two methods utilized in Dynamics 365 Business Central for extracting records from a table. The key distinction between them is that FindSet fetches a group of records, whereas FindFirst only fetches the initial record in the filter and FindLast fetches the last record. FindSet is employed when there’s a need to traverse through a sequence of records, and it’s advisable to use FindSet(true) if there’s a requirement to alter any record within the group. Conversely, FindFirst/FindLast is employed when only the initial or last record is needed and there’s no requirement for a loop. SetLoadFields  The SetLoadFields function only loads the specified fields, thereby not fetching the remaining fields in the table and ensuring a quicker load. It is suitable for searching f
Read more

How to create documentation comment using Custom Snippets in VS Code for AL - D365 Business Central.

-
Image
Code documentation is a process of writing additional information to help others understand how your code works. It can include anything from comments in the code to separate documents that explain the code’s architecture, dependencies, and purpose. Also, development teams have their own format for the documentation. Writing the document comment every time can be time consuming. For this we can create custom Snippets. To create a custom code documentation comment using snippets in Visual Studio Code, you can follow these steps: In VS Code Go to Settings -> User Snippets Select AL as we want this Snippet for AL language. al.json file will open then add configurations as follows: Add a new object with a name for your snippet and a prefix that will trigger the snippet. For this example, we write tDocumentationComment Add a description field to describe what the snippet does. Add a body field with the code you want to include in the snippet. You can use placeholders like ${1:variable}
Read more

Microsoft Dynamics NAV - Creating and Consuming a Codeunit Web Service

-
Image
We can create any code unit in Dynamics NAV and publish it as web-service. To do that we need to go to the page (form in 2009)  "Web Services".  In the Web Services page, choose New. In the Object Type column, select Codeunit, then in the Object ID column enter the Codeunit Number, and then enter a name in the Service Name column. This name will be the web-service name to consume.  Mark the check box in the Published column and choose OK to close the New - Web Services page. Webservice Form (2009) Verifying the Web services availability. Make sure flowing services are running in server. In Browser write the service URL to check if the service published successfully, Change the localhost with ip for remote server : http://localhost:7047/DynamicsNAV/WS/Services  If everything is ok you should see the published services names in the browser. Now lets try to consume the service. In this example we will see how to consume the service from a Dot
Read more