.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Post New Web Links

Send Email from SQL Server Express Using a CLR Stored Procedure

Posted By: Syed Shakeer Hussain     Posted Date: August 11, 2010    Points: 2   Category :Sql Server
One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.

View Complete Post

More Related Resource Links

Two results from two different tables by Stored Procedure and put them in variables and send email.


Hi All, first i had like 7-8 steps just to execute stored proc and send email. now i have put everything in one stored proc as follows :

Alter procedure PlanFinder.InsertInvalidRecords

Truncate table [PlanFinder].[InvalidAwps] 
INSERT INTO [PlanFinder].[InvalidAwps](Ndc, AwpUnitCost) 

SELECT DISTINCT P.Ndc Ndc, A.Price AwpUnitCost 
    PlanFinder.PlanFinder.HpmsFormulary P 
    LEFT JOIN (SELECT Ndc, Price FROM MHSQL01D.Drug.FdbPricing.vNdcPrices  
               WHERE PriceTypeCode = '01' AND CurrentFlag = 1) A 
ON P.Ndc = A.Ndc  
WHERE (A.Ndc IS NULL OR A.Price <= 0 OR A.Price IS NULL) 

DELETE FROM PlanFinder.NdcAwp
       Ndc IN
              SELECT Ndc
              FROM  PlanFinder.InvalidAwps                     &nb

send email using Godaddy server by gmail


 Hello ,


My site is hosted  in Godaddy server. And i am using google apps for emailing a/c for that i have changed the  MX-records and then it is working fine.

Then i finished my web site


it is working fine there .

in the site there is a  contactus link which opens


this page basically should send a mail to a particular email id .

for sending mail I am using gmail a/c . (Here is the full code)


public void SendEmail(string From,string GmailEmail, string GmailPassword, string To, string Subject, string Body,      
        FileUpload fileupload)

        System.Net.Mail.MailMessage mail = new System.Net.Mail.MailMessage();
        mail.From = new MailAddress(From, "One Ghost", System.Text.Encoding.UTF8);
        mail.Subject = Subject;
        mail.SubjectEncoding = System.Text.Encoding.UTF8;

TSQL: Passing array/list/set to stored procedure (MS SQL Server)

Passing array/list/set to stored procedure is fairly common task when you are working with Databases. You can meet this when you want to filter some collection. Other case - it can be an import into database from extern sources. I will consider few solutions: creation of sql-query at server code, put set of parameters to sql stored procedure's parameter with next variants: parameters separated by comma, bulk insert, and at last table-valued parameters (it is most interesting approach, which we can use from MS SQL Server 2008). Ok, let's suppose that we have list of items and we need to filter this items by categories ("TV", "TV game device", "DVD-player") and by firms ("Firm 1", "Firm2", "Firm 3). It will look at database like this So we need a query which will return us list of items from database. Also we need opportunity to filter these items by categories or by firms. We will filter them by identifiers. Ok, we know the mission. How we will solve it? Most easy way, used by junior developers - it is creating SQL-instruction with C# code, it can be like this List<int> categories = new List<int>() { 1, 2, 3 };   StringBuilder sbSql = new StringBuilder(); sbSql.Append( @" select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName from Item i inner join Firm f on i.FirmId =

How can i send an input param of XML with more than 8K to a stored procedure in ADO?

 My sample SP : CREATE PROCEDURE MyINV   @sData AS XML  AS  BEGIN  SET NOCOUNT ON; select 1 as iTestCount  END The param sData is more than 8000 characters.  till 8000 bytes, it works .But throwing exception on 8001 bytes.   Code = 80040e21,Code meaning = IDispatch error #3105,Source = Microsoft OLE DB Provider for ODBC Drivers,Description = [Microsoft][ODBC SQL Server Driver]String data, right truncation   Im using VC++/ADO. _variant_t varChunk; varChunk = (char*)(sValue.GetBuffer()); pParam->AppendChunk(varChunk); pCommand->Parameters->Append(pParam); pCommand->Execute(...)  

Sending Email by exeucting a stored proc on SQL Server that references CLR

Hi Guys, I want to modify the data type of the @body variable in the stored procedure that sends an email. The stored procedure is as follows:   CREATE PROCEDURE [dbo].[spSendMail4]<br/> @recipients [nvarchar](4000),<br/> @cc [nvarchar](4000),<br/> @subject [nvarchar](4000),<br/> @from [nvarchar](4000), @body [nvarchar](4000), @attachment [nvarchar](4000)<br/> WITH EXECUTE AS CALLER<br/> AS <br/> EXTERNAL NAME [SMTPCLR].[StoredProcedure].[spSendMail]<br/> GO   I want to change the @body variable to data type: text. The reason for this, is because I am sending an HTML email whose content (characters) exceed 8000. Basically the new stored procedure should looks like below:   CREATE PROCEDURE [dbo].[spSendMail4] @recipients [nvarchar](4000), @cc [nvarchar](4000), @subject [nvarchar](4000), @from [nvarchar](4000), @body text, @attachment [nvarchar](4000) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SMTPCLR].[StoredProcedure].[spSendMail] GO   If attempt to make this change, I get the below error:   CREATE PROCEDURE for "spSendMail" failed because T-SQL and CLR types for parameter "@body" do not match.   Understandably so, because from the article http://msdn.microsoft.com/en-us/library/ms131092%28SQL.100%29.aspx CLR data type (.NET Framework) is not compa

SQL Server 2008 Stored Procedure output parameter

I would like to get OUTPUT parameter names from stored procedure without executing stored procedure.  for excample schema API will help to get input parameter metadata. Thanks in advance. Murali

SQL Server 2005 Express Edition - GUI to set permissions on stored procedures

Hi there, I have SQL Serve 2005 Express Edition (Build 2600: Service Pack 3) installed; I also have the Management Console installed. My problem is that I cannot set execute permission, or any other type of permissions on my stored procedures through the GUI, as the Property menu item is missing from the right click menu. I read somewhere that this happens when you have SP1, but as I stated above I have got SP3 installed... Any help?   Regards, D.

can i use stored proc to send email.

Hi how can i use stored proc for sending emails?

debugging stored procedure created in sql server 2008

Hi experts,     I want to get rid of old debugging of stored procedure where we use print statement. I know how to debug the stored procedure just wanted to ask, can i get the details of #temp or @tbltemp(TABLE datatype) variable values while debugging

debugging stored procedure created in sql server 2008

Hi experts,     I want to get rid of old debugging of stored procedure where we use print statement. I know how to debug the stored procedure just wanted to ask, can i get the details of #temp or @tbltemp(TABLE datatype) variable values while debugging

SQL Server 2005 - Specifying Optional Parameter in Stored Procedure

I need to alter the following stored procedure so that the UserID is an optional parameter. I have, as yet, been unsuccessful. Basically, if the user doesn't supply a User ID, I want to return all of the users that match the other criteria.

This seems like it should be a simple thing.......


@CompanyID tinyint

,@DepartmentID tinyint

,@ApplicationID int

,@UserID AS









FROM [ReportQueries]


[UserID] = @UserID

Accessing Stored Procedure Of SQL Server From Popup window


Hello everybody.

I have a parent window parent.aspx and a popup window popup.aspx. parent.aspx has a confirm button which on click open popup.aspx. This popup.aspx holds a filled application form like leave application form of employees and a email sending button. That button on click send emails to the specified email addresses with attachment. The

how to create stored procedure in sql server ???


here i have 3 attributes i want to write a sp it should take 1input value by tht i get remaining 2out put values

 table name: Employee

attributes: id,name,sal;

help me in this....

if this exected i should get succecss msg also and boolen is true...


flase and not exectuted msg...


Send NULL value to a stored procedure variable


Hi all. I want to send an image to a stored procedure variable that is varbinary(max) field, It works correctly when an image selected by fileupload and my image property has image file, and it can send to stored procedure. but when fileupload is empty I want to send NULL to stored procedure variable that is varbinary(max). I use DbNull.Value in C# code. But error occurs. What should I do for send Null to stored procedure? Cry

how to log all inputs for each stored procedure? plss help as I am new to sql server


Hi ,

I have upto 16 calculators(I mean 16 stored procedures) in one database.How do I log all inputs for each calculater(each stored proc)?.Most of the variables are not populated in the output table so it became hard to reproduce.The variables are not consistant across all the 16 procs . Do I need to set up new table for these inputs?

your help would be appreciated ..pls help how do I create a table or write a common proc ? Your help with the idea and code will be greatly appreciated!



SQL Server 2008 - Stored Procedure


I work for a software manufacturing company and I am doing a project for a very large potential customer.  This customer has a SQL Server 2008 DB and claims that his emplpyee's can only access the DB (read or write) using a stored procedure.  Now the catch is we cant re-write the software to accomidate this customer, and our requests to see the stored procedure to see how hes using it have failed.  But I need to be able to access his DB at no additional cost to the customer.  Is there any way to call a SP using a Data Source Driver?  Or any other ideas, I need an answer for this cutomer today :(

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend