SQLCLR in Practice Creating a Better Way of Sending Email from SQL Server. SQLCLR is now considered a robust solution to the few niche requirements that cant be met by the built in features of SQL Server. Amongst the legitimate reasons for avoiding SQLCLR, there is the fear of getting bogged down in code with special requirements that is difficult to debug. Darko takes a real example, extending the features of spsenddbmail, to demonstrate that there need be few terrors in SQLCLR. In this article, I will demonstrate how to build a SQLCLR stored procedure that extends the functionality of the procedure spsenddbmail that is provided in SQL Server. I will be including the code, which is on Github here. The main purpose of this article is to illustrate that SQLCLR is a very good way of accessing. NET functionality for special purposes, but Im also hoping that the example might be useful to you. There are a few reasons for wanting to have a more comprehensive way of sending email from SQL Server than spsenddbmail. You may not have the appropriate rights on the instance level. You need to have more control of the process of sending e mails. You need to implement custom logging of emails for security. You have to use your e mail profiles, rather than those provided by DBA team. If you, as a software vendor, are providing a database that is co hosted with databases from other software vendors, there are some server based resources that you have to share. Usually, each of your customers has their DBA team, but you, the vendor, are responsible for your database. It is all simple until you require a server based resource maybe you have to create some SQL Server Agent alerts or jobs. Download free trials and updates for all products including Creative Cloud, Photoshop, InDesign, Illustrator, Acrobat Pro DC and many more. Im wondering if theres a feature in ppt 2010 to paste the formatting of one table onto another e. g. table borders, cell background colour. Ive tried all the. It could be that you need to send an e mail to yourself or your companys dedicated e mail address to transmit a report, graph or warning message. Then you have to negotiate with local DBA team or with the other software vendors. In SQL Server Agents alert system, it is only possible to have one active e mail profile, and you cant change it without restarting SQL Server Agent. There are situation when local DBA team, just replace public e mail profile in SQL Server mailing system and you suddenly start receiving phone calls that something goes wrong. When you need to send an e mail from a process in your database, that process must have appropriate rights to MSDB database in order to execute spsenddbmail. Alternatively, you see a message like this You will sometimes hear that a reason to replace spsenddbmail with a custom SQLCLR stored procedure is that you is that e mailing isnt a feature of SQL Server EXPRESS edition. Actually, this isnt the case because EXPRESS edition merely lacks the GUI wizard to set up e mailing. If, instead, you use the T SQL script see Appendix B and replace the information in the declaration statement with actual values, you can set up e mail on EXPRESS or any other edition of SQL Server. If you ever look at the parameter list of spsenddbmail, you will probably be slightly astonished at the number of parameters. Of course, many of them are optional. In the screenshot below, I surround with red those parameters that I consider crucial for sending an e mail. I decided that, if I had to create an alternative spsenddbmail for a database, it would be more convenient to have an email profile rather than have to repeatedly enter every item of information that youd need for an e mail profile. The main goal for the project was to have everything needed for e mailing contained within the database. This means that we must not be required to access the instance or system databases in any way. Of course, CLR should be enabled see Appendix B for information how to do that but this is the only setting at the instance level that should be changed. This article talks about how to build such procedure using. NET on the server. This requires using SQLCLR. Here is how this article is organized In the first section, I will make a brief overview about e mailing on SQL Server. Then I will introduce CLR stored procedure EMAIL. CLRSend. Mail. In the third section, I will present a set of new tables needed to implement custom e mailing. Then we are talking about sensitive information and how to protect them. Fifth section talks about testing and monitoring SQLCLR solution. Sixth section talks about debugging SQLCLR solution. Finally, I will discuss how supported material is organized. A Brief Overview of SQL Server E Mailing. Database mail is extremely huge and excellent designed system. Sending e mails occurs outside of SQL Server, in a separate process using external executable. When you send a message, Database Mail adds request to Service Broker queue. The stored procedure spsenddbmail returns immediately. According Microsoft documentation Database Mail uses Service Broker activation to start the external program when there are e mail messages to be processed. Database Mail starts one instance of the external program. The external program runs in the security context of the service account for SQL Server. Therefore, by default, Service Broker has enabled in MSDB database and there is no mailing without Service Broker. Probably some of you did not know that in background Service Broker uses external program. The Database Mail external executable is Database. Mail. exe, located in the MSSQLBinn directory of the SQL Server installation. In addition to the procedure spsenddbmail parameters, there are configuration parameters that sysmailconfiguresp stored procedure regulate. Parameters have listed in table below. These ones colored with green have implemented in SQLCLR solution. In addition, these one colored with blue are new in SQLCLR solution and black ones are omitted. Parameter name. Description. Default Value. Account Retry Attempts. The number of times that the external mail process attempts to send the e mail message using each account in the specified profile. Account Retry Delay secondsThe amount of time, in seconds, for the external mail process to wait after it tries to deliver a message using all accounts in the profile before it attempts all accounts again. Database Mail Executable Minimum Lifetime secondsThe minimum amount of time, in seconds, that the external mail process remains active. The process remains active as long as there are e mails in the Database Mail queue. This parameter specifies the time the process remains active if there are no messages to process. Default. Attachment. Encoding. The default encoding for e mail attachments. MIMEMax. File. Size. The maximum size of an attachment, in bytes. Prohibited Extensions. A comma separated list of extensions, which cannot be sent as an attachment to an e mail message. Logging. Level. Specify which messages are recorded in the Database Mail log. One of the following numeric values 1 This is normal mode. Logs only errors. This is extended mode. Logs errors, warnings, and informational messages. This is verbose mode. Logs errors, warnings, informational messages, success messages, and additional internal messages. Use this mode for troubleshooting. Save Emails. Saving e mails in database. E mails have saved in MSDB database. There are two tables dbo. In SQLCLR there are two tables too, located in schema EMAIL. In SQLCLR, solution there is option to save an e mail to the database. False. Send Mail Asynchronously. This is default, but you can choose to send syncronously. True. No Piping. There is no messages in message box, after success sending. True. Save Attachments. Do we save attachments. True. Finally, there are two configuration objects The database configuration objects provide a way for you to configure the settings that Database mail should use when sending an email from your database application or SQL Server Agent. These are the Database Mail accounts and Database Mail profiles. The Database Mail profile can contain one or more Database Mail accounts. When you fail to send an e mail using the first account on the list, the process of sending e mails will sleep for a while the duration of this wait is specified by configuration property Account Retry Delay and then the process will start sending an e mail using another account. Of course it is not easy to reproduce some of the features such as scalability and reliability using SQLCLR. Introduction to the stored procedure EMAIL. CLRSend.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |