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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

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

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
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

View Complete Post

More Related Resource Links

Send Email from SQL Server Express Using a CLR Stored Procedure

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.

C# , Sending email through exchange server?

I've searched on this and tried several methods using System.Net.Mail but I can't get it to work. I want to send email using the Exchange server they have at work. Anyone could show me how to do this (server requires authentication)   Thanks in advance

Sending an email with a link that verifies an id in sql server

I have an online job application program that the user fills out and submits the information to our database and on the last page they give there email address so that we can send them an email that has a link that is associated with the ID that's autogenerated by the sql server database when the user submits the information, when the link is clicked it verifies to the database to keep the information submitted by that user per their autogenerated ID. Below is the code that i've written to send the email I have the link setup to go to yahoo right now but I am not sure how to get this to send the autogenerated link that is associated with the ID that is autogenerated already in my sql server database.Private Sub SendMail()        Dim emailSender As SmtpClient        Dim theMessage As MailMessage        Dim EmailAddress As String = txtEmailAddress.Text        emailSender = New System.Net.Mail.SmtpClient("ppicom")        theMessage = New MailMessage        theMessage.From = New MailAddress("PeerlessProductsInc @mail.com")        theMessage.To.Add(EmailAddress)        theMessage.

can i use stored proc to send email.

Hi how can i use stored proc for sending emails?

Workflow sending old server name in email


I recently migrated from SharePoint 2007 to SharePoint 2010 using the database attach method to a new server. Server has different name than old server. The migrated sharepoint site collection has been working great. I decided to try workflows (we hadn't used any in MOSS 2007). I added a new workflow to an existing library using the "Approval - SharePoint 2010" reusable workflow. When I start the workflow on a document that needs approved, the approver gets an e-mail stating that a document has been changed (he must have alerts configured for that library). However, URL that the e-mail displays is wrong! It showing the old server name instead of the new one. I browse to the library on the OLD server (which is still online for testing purposes) and that document definitely does not exist in the OLD server library.

Can someone point me in the right direction to troubleshoot? I'm green at SharePoint administration. I already checked DNS and ensured that there were no leftover entries for alternate access mapping names.

Sending a File Using Email Helper in WebPages/Razor


Note: This is particularly with respect to Email Helper in WebPages/Razor. Please don't post links using System.Net.Mail object.

Following this tutorial: http://www.asp.net/webmatrix/tutorials/9-adding-email-to-your-website

Under "Sending a File Using Email" section what exactly is the code trying to do.

1: The SendFile.cshtml does not have any option to select file.

2: ProcessFile.cshtml is running fine. I am getting the email but no attachement.

I tried changing fileAttachment input to type="file" but not file is being sent to my email. I just get the message send in body.

Question: How exactly this file attachment works using Email Helper in WebPages.

Data Truncation issue with Enterprise Library Logging WriteLog stored Proc


Hi ,

I'm using Enterprise Library Logging  feature for logging. The issue i am facing is when the Logging message is too large(more than 65534 chars) ,complete data  is not logged in the Formatted Mesage column which is  of data Type nText .

I am able insert complete data if i try inserting from Sql insert Query from sql management studio. Do i need to add any attributes to data base listener or do i need to change the sp.

 Is there any way to increase the WriteLog stored proc param size in EnterpriseLibrary.Logging config file ? . Please let me know.


Thanks In Advance.

getting a email delivert status when sending email using msdb.dbo.sp_send_dbmail


Hi All,

Can some help me regarding following.

I have set a job in sql server 2008 to send reminder emails for product expiration.
Now I want a report which will specify the list of the emails sent with the email status details like queued ,sent,delivered, failed.

I can fetch the emails that are sent by querying the like following

select * from sysmail_mailitems SM inner join sysmail_profile SP on SM.Profile_Id = SP.Profile_Id
and SP.Name ='ReminderProfile'

The problem is only getting a perfect status of email whether it is delived or failed. How I can get those status ?
Please help.


how to write a stored proc to prevent multiple users in uploading the reports at a time? plsss help



I wanted to know how to prevent more than one user from uploading the report at a time .I am new to .net ,plss help with the code that would be great.Details are below:

This is a Windos based app written in c# . When User A  clicks Upload option on  one server  from the menu  to upload the files  and at the same time when User B clicks Upload option on different server , User B should be alerted a message saying "User A's uploading is in progress,pls wait" . How to achieve this, plsss help with the code.. I am thinking this logic should be kept in a stored proc, How do I write that proc? plss help


Thanks in advance!

Populating a list box in InfoPath from a sql stored proc


I have an InfoPath web enabled form that gets published to a SharePoint site.  I need to add a list box to the form that gets populated with data returned from an existing call to a SQL Stored Proc.  

As the form works now, the user types in a patient id #, then clicks a button that calls the Stored Proc and returns any patients that match the given patient ID.  This part works.  

My task is to add a list box so that if more than one patient is returned , display the list box with all of these patients populated in it.

I am hoping to do this within the confines of the existing code.  The existing code looks like this:

private bool LoadPatientInformationFromDB(string patientId, string patientFirstName,
      string patientLastName, string patientMiddleInitial, string birthDate, string gender,
        string admitDate, string location)
      bool completed = false;
      //Load from DB
      SqlConnection conn = new SqlConnection(GetOrderSetDBConnectionString()); 
      //Create Form Builder
      formBuilder = new FormBuilder();
      SqlDataAdapter adpt = null;
        if (!String.IsNullOrEmpty(patientId) || !String.IsNullOrEmpty(patientFirstName) ||
          !String.IsNullOrEmpty(patientLastName) || !String.IsNullOrEmpty(patientMiddleInitial)
          || !St

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;

smtp server 5.1 in xp not sending emails


hi iam using asp.net 2.0 with c#.net.

in my application when client completes its shopping email will goes to user this works fine when there is internet connection is working fine,
if  there is some problem  in internet connection if one mail stops then all the mails will be stopped even now internet works fine,

how to resend the emails using smtp server, is there any option to resend the emails automatically when internet works fine.

can you give example which helps me.

sending an email in asp.net


My problem is  when i am writing the whole code in asp.net using vb which is

Protected Sub btnsend_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsend.Click
        Dim mailmessage As System.Net.Mail.MailMessage = New System.Net.Mail.MailMessage()
        mailmessage.From = New System.Net.Mail.MailAddress(txtfromaddress.Text.Trim())
        mailmessage.To.Add(New System.Net.Mail.MailAddress(txtToAddress.Text.Trim()))
        mailmessage.Subject = txtSubject.Text.Trim()
        mailmessage.Body = txtbody.Text.Trim()
        Dim smtpClient As System.Net.Mail.SmtpClient = New System.Net.Mail.SmtpClient()

In the last line i m getting smtpException unhandled by user code (Failure sending mail)

In the web.config file i have written the code which is as follows:-

      <smtp from="someaddress@myCTdomain.com">
        <network host

Error While sending Email using Outlook object in asp.net 4.0


Hi ,

            I am trying to send Email using Outlook object but  it gives following error :

Retrieving the COM class factory for component with CLSID {0006F03A-0000-0000-C000-000000000046} failed due to the following error: 80080005 Server execution failed (Exception from HRESULT: 0x80080005 (CO_E_SERVER_EXEC_FAILURE)).

I have added outlook reference "using Microsoft.Office.Interop.Outlook"  version

and i am using below code :

Microsoft.Office.Interop.Outlook.Application oApp = new Microsoft.Office.Interop.Outlook.Application();
            Microsoft.Office.Interop.Outlook._NameSpace oNameSpace = oApp.GetNamespace("MAPI");
            Microsoft.Office.Interop.Outlook.MAPIFolder oOutboxFolder =
            oNameSpace.Logon(null, null, false, false);
            Microsoft.Office.Interop.Outlook._MailItem oMailItem =

smtp is noe sending email instantly,it makes a Queue in my local system

I am using a  smtp to send email from asp.net (C#)code.it sending some email instantly but many of email is making a queue in my Inetpub folder at local system.what could be the issue ? please help me to get rid from it.ThankxArvind

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 =

COnfigurations stored in SQL Server ...

I have a couple of connections in my package which use configs stored in SQL Server. I am finding that when I open the package in BIDS, the configuration value in the SQL Server table is updated with the information pertaining to the connection in the package. Sounds bizzarre, but I have watched this happen several times. Am I hallucinating or is this intended behavior? I have checked and there is no replication or any process that updates the config tables. Appreciate the feedback.Jagannathan Santhanam
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