Send SMTP Email using SQL Server. Many times it is needed to send a email from the database. The important reason is that you do not need to pull the data in front end and then send emails from front end. Also if the database server and application server are separate, it takes of the load from the application server. Collaboration Data Objects (CDO)For sending emails through SMTP Server I will be using Collaboration Data Objects(CDO). CDO are part of Windows and are useful in sending SMTP Emails. For more information on CDO Read here. · Get a Lean, Mean Dev Machine with the Express Editions of Visual Basic and SQL Server 2005.What's in the Release Notes The release notes cover the following topics: What's New; Earlier Releases of vCenter Server 5.1; Internationalization. Knowing some basic best practices can help you optimize SQL Server's storage availability and performance. Editors Note: this post was updated in June 2014 to link to an online index rebuild bug that can cause corruption. Once up on a time, there was a database server with. ![]() In SQL Server 2. 00. I’ll create a stored procedure that will be used to send emails using CDO. I’ll explain how to send emails using GMAIL SMTP Server. Here I have created a stored procedure sp_send_cdosysmail which accepts the following parameters. Parameter. Relevance@from. Email Address of the Sender@to. Email Address of the Recipient@subject. Subject of the Email@body. Body of the Email@bodytype. Type of Body (Text or HTML)@output_mesg. Output parameter that returns the status (Success / Failed)@output_desc. Output parameter that returns the Error description if an error occurs. ![]() GOSETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATEPROCEDURE [dbo].[sp_send_mail] @from varchar(5. ASDECLARE @imsg int. DECLARE @hr int. DECLARE @source varchar(2. DECLARE @description varchar(5. In the above SQL Snippet I have created the stored procedure and declared some variables that will be used later. Create an OLE Instance of CDOEXEC @hr = sp_oacreate 'cdo. Send. Using. Send. Using Specifies Whether to send using port (2) or using pickup directory (1)EXEC @hr = sp_oasetproperty @imsg,'configuration. Specify your SMTP Server that you will use. Here I am using gmail SMTP Server. EXEC @hr = sp_oasetproperty @imsg,'configuration. Specify the sender’s email address here. The account that will be used to send emails. EXEC @hr = sp_oasetproperty @imsg,'configuration. Specify the password of the account here. EXEC @hr = sp_oasetproperty @imsg,'configuration. Specify where the SMTP server requires SSL (True) or not (False)EXEC @hr = sp_oasetproperty @imsg,'configuration. True'smtpserverport. Specify the Port Number foy your SMTP Server (4. EXEC @hr = sp_oasetproperty @imsg,'configuration. Specify the Type of Authentication Required None (0) / Basic (1) EXEC @hr = sp_oasetproperty @imsg,'configuration. Send Email. Execute the OLE object to send email. EXEC @hr = sp_oamethod @imsg,'configuration. EXEC @hr = sp_oasetproperty @imsg,'to', @to. EXEC @hr = sp_oasetproperty @imsg,'from', @from. EXEC @hr = sp_oasetproperty @imsg,'subject', @subject. EXEC @hr = sp_oasetproperty @imsg, @bodytype, @body. EXEC @hr = sp_oamethod @imsg,'send',null. Error Handling. Below snippet is checking if the mail is send successfully. If not it captures the Error message and the Error Description in the output variables. SET @output_mesg ='Success'IF @hr < > 0 SELECT @hr BEGIN EXEC @hr = sp_oageterrorinfo null, @source out, @description out IF @hr = 0 BEGIN set @output_desc = @description END ELSE BEGIN SET @output_desc =' sp_oageterrorinfo failed' END IFnot @output_desc is. NULL SET @output_mesg ='Error'ENDDestroy the OLE Object Instance. EXEC @hr = sp_oadestroy @imsg. Calling and Execute the Stored Procedure. Below I am calling the Stored Procedure and passing the parameters. Note: the Bodytype can be HTML (htmlbody) or Text (textbody)DECLARE @out_desc varchar(1. EXEC sp_send_mail 'sender@gmail. Hello', '< b> This is s Test Mail< /b> ', 'htmlbody', @output_mesg = @out_mesg output, @output_desc = @out_desc output. PRINT @out_mesg. PRINT @out_desc. Enable OLE Automation in SQL Server 2. OLE Automation is disabled by default in SQL Server 2. GORECONFIGURE; GOsp_configure'Ole Automation Procedures', 1; GORECONFIGURE; GOYou can download the complete script here. Script. Convert Rows to Columns In Sql Server. If my Table look something like this. Create Table #Temp(Id Int, Name Var. Char(2. 0), Value Var. Char(2. 0))Insert Into #Temp Values(1, 'Name','George')Insert Into #Temp Values(1, 'Shoe. Size','9. 5')Insert Into #Temp Values(1, 'Name','Gkjdk')Insert Into #Temp Values(1, 'Shoe. Size','8. 9')Insert Into #Temp Values(2, 'Name','Bill')Insert Into #Temp Values(2, 'Shoe. Size','1. 0. 5')Insert Into #Temp Values(3, 'Name','John')Insert Into #Temp Values(3, 'Shoe. Size','9')Insert Into #Temp Values(4, 'Name','Greg')Insert Into #Temp Values(4, 'Shoe. Size','9')and its output is like thisid name value 1 Name George. Shoesize 9. 5. 1 Name Gkjdk. Shoesize 8. 9. 2 Name Bill 2 Shoesizr 1. I want my result in this formatid Name Shoesize. George 9. 5. 1 Gkjdk 8. Bill 1. 0. 5. I tried pivot function but it will not work it only first row for id 1 bcoz of aggregate function used and i also used group by that also nt work if any one has suggesstions plz help. Min(Case When Name = 'Name' Then Value End) As Name. Min(Case When Name = 'Shoe. Size' Then Value End) As Shoe. Size. Id is not the primary key my scenario is something like that but that actual data is not like that.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
October 2017
Categories |