Post reply

Regarding Mail Trigger with SQL Server 2000

  • August 30, 2009 at 11:40 am

    #135086

    Hi Everybody, I am Ramesh Kumar new in SQL Server technology. I have to Send auto generated mail form server side for that I am using SQL Server 2000

    So for that how to configure Startup Service Account for Domain User Name and also how to write query for triggering Auto email.

    Thanks & Regards

    Ramesh Kumar

  • August 31, 2009 at 8:25 am

    #1046886

    I know two ways of doing this, 1: Using MAPI Profiler (requires OUTLOOK) and 2: Required SMTP configuration. Here it is:

    Method 1: The most easiest way of doing this will be to use XP_SENDMAIL procedure from sql server. However, you require to configure the MAPI mail profile on the server from where you need to send the email.

    xp_sendmail sends email using the MAPI Profiler. First you need to setup the MAPI, once MAPI is setup, then it is very easy to play with emails. You may follow the suggestion provided by Faiz to setup operator once you confirm that MAPI is working.

    You need to install Microsoft Outlook 2000 or earlier on server, than setup your email account on that. Once you setup the Outlook, after that add MAPI profile to SQL Server, so that sql server could send email using that profile. I guess you need to keep on running the outlook on that machine to take advantage of sending emails.

    I guess you know how to install Outlook and setting up your account there, you can follow the steps below to add MAPI profile to your sql service after installing Outlook.

    http://support.microsoft.com/kb/281293/EN-US/

    http://support.microsoft.com/default.aspx/kb/263556

    Method 2: Or, If you won't prefer the first method then you may use CDO object to send email. For this you need to write vbscript similar to what I have pasted below. You may either script within the job step, or you may write DTS package with vb module.

    Set objMessage = CreateObject("CDO.Message")

    objMessage.Subject = "Report"

    objMessage.From = "DBA@xyz.com"

    objMessage.CC = "me@xyz.com"

    objMessage.TO = "me@xyz.com"

    objMessage.HTMLBody = "

    Hi

    Please find attached report

    Thanks"

    objMessage.AddAttachment "c:\file.xtn"

    objMessage.Configuration.Fields.Item _

    ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

    'Name or IP of Remote SMTP Server

    objMessage.Configuration.Fields.Item _

    ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.xyz.com" 'or IP address

    'Server port (typically 25)

    objMessage.Configuration.Fields.Item _

    ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

    objMessage.Configuration.Fields.item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = "1"

    objMessage.Configuration.Fields.item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "xyz/me"

    objMessage.Configuration.Fields.item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "*******"

    objMessage.Configuration.Fields.Update

    If you have SMTP configuration with you, or if you can take SMTP configuration from your System Admin or IT Admin, then I would prefer method 2, because it does not have any dependency on OUTLOOK.

    Thanks

    Swarndeep

    http://talksql.blogspot.com

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply