Mails

I have the following code to send email using DTS and CDO objects.

I am getting run time error. Dont know why ?

Any configurations to be made ?

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
dim sch

sch = "http://schemas.microsoft.com/cdo/configuration/"
Set cdoConfig =CreateObject("CDO.Configuration")

cdoConfig.Fields.Item(sch & "sendusing") =2
cdoConfig.Fields.Item(sch & "smtpserver") = MPBAKOREX01.corp.mphasis.com"

cdoConfig.fields.update

Set cdoMessage =CreateObject("CDO.Message")
Set cdoMessage.Configuration = cdoConfig
cdoMessage.From = "shanmuga.r@.mphasis.com"

cdoMessage.To = "shanmuga.r@.mphasis.com"

cdoMessage.Subject = "Sample CDONTS NewMail"
cdoMessage.HTMLBody="<html><b>rajaganapathy</b></html>"
msgbox "before "
cdoMessage.Send
msgbox "Send"

Set cdoMessage = Nothing
Set cdoConfig = Nothing

Main = DTSTaskExecResult_Success
End Function

hi RajaGanapathy,

What sort of error you see? COM error?

From a .dtsx file or DTS 2000?

|||

Hi,

Creating a new instance of "CDO.Configuration" requires a dll to be registered on the server. Is the dll available on the server. Also please specify the error.

Thanks

Mohit

|||

Thanks enric vives and mohtigupta,

Even i can use xp_sendmail, but i cant send in htmlformat.

I dont think my system is installed with cdo.dll.

Can u just tell me how to send a mail in html format !

|||This forum concerns SSIS, not DTS. However, the DTS newsgroup is still available.

Mail Profile

SQL 2K
I just developed a DTS and want to add a 'Send Mail Task'.
In 'Send Mail Task' the drop-down for profle name is not displaying the
Profile Name configured under SQL Mail.
I know SQL Mail is confugured under a different account, my question is - As
a developer how can I set this task '
Thanks
MS UserSend Mail Tasks are imperfect, so I never use them. Instead, do an Execute
SQL task that executes xp_sendmail
Robert.
"MS User" wrote:
> SQL 2K
> I just developed a DTS and want to add a 'Send Mail Task'.
> In 'Send Mail Task' the drop-down for profle name is not displaying the
> Profile Name configured under SQL Mail.
> I know SQL Mail is confugured under a different account, my question is - As
> a developer how can I set this task '
>
> Thanks
> MS User
>
>|||In message <F4DBC765-4503-458E-9157-4E24549B516A@.microsoft.com>, Robert
Davis <RobertDavis@.discussions.microsoft.com> writes
>Send Mail Tasks are imperfect, so I never use them. Instead, do an Execute
>SQL task that executes xp_sendmail
>Robert.
>"MS User" wrote:
>> SQL 2K
>> I just developed a DTS and want to add a 'Send Mail Task'.
>> In 'Send Mail Task' the drop-down for profle name is not displaying the
>> Profile Name configured under SQL Mail.
>> I know SQL Mail is confugured under a different account, my question is - As
>> a developer how can I set this task '
>>
>> Thanks
>> MS User
...and one of the reasons why Send Mail is "imperfect", although my term
would be a bit stronger, is that they use MAPI. A MAPI profile is held
as part of the NT user profile, so when developing on your desktop
machine you can only see your personal profile. On the server when you
run it as a scheduled package for example it can only see the profile
for the SQL Server Agent service account, normally what you would use
for SQL Agent Mail.
DTS is client side, and MAPI is profile specific so unless you are
logged onto the server as the service account you created the SQL Mail
profile under this will not be what you suggest. As for the solution, I
second the Exec SQL idea, although I would always use xp_smtp_sendmail
(http://www.sqldev.net). xp_sendmail also uses MAPI is and that is just
asking for trouble in my opinion.
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Mail Profile

SQL 2K
I just developed a DTS and want to add a 'Send Mail Task'.
In 'Send Mail Task' the drop-down for profle name is not displaying the
Profile Name configured under SQL Mail.
I know SQL Mail is confugured under a different account, my question is - As
a developer how can I set this task ?
Thanks
MS User
Send Mail Tasks are imperfect, so I never use them. Instead, do an Execute
SQL task that executes xp_sendmail
Robert.
"MS User" wrote:

> SQL 2K
> I just developed a DTS and want to add a 'Send Mail Task'.
> In 'Send Mail Task' the drop-down for profle name is not displaying the
> Profile Name configured under SQL Mail.
> I know SQL Mail is confugured under a different account, my question is - As
> a developer how can I set this task ?
>
> Thanks
> MS User
>
>
|||In message <F4DBC765-4503-458E-9157-4E24549B516A@.microsoft.com>, Robert
Davis <RobertDavis@.discussions.microsoft.com> writes[vbcol=seagreen]
>Send Mail Tasks are imperfect, so I never use them. Instead, do an Execute
>SQL task that executes xp_sendmail
>Robert.
>"MS User" wrote:
...and one of the reasons why Send Mail is "imperfect", although my term
would be a bit stronger, is that they use MAPI. A MAPI profile is held
as part of the NT user profile, so when developing on your desktop
machine you can only see your personal profile. On the server when you
run it as a scheduled package for example it can only see the profile
for the SQL Server Agent service account, normally what you would use
for SQL Agent Mail.
DTS is client side, and MAPI is profile specific so unless you are
logged onto the server as the service account you created the SQL Mail
profile under this will not be what you suggest. As for the solution, I
second the Exec SQL idea, although I would always use xp_smtp_sendmail
(http://www.sqldev.net). xp_sendmail also uses MAPI is and that is just
asking for trouble in my opinion.
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Mail failure in DTS package

Hi, Im trying to do a DTS that exports data to a excel file and then
mail it. It runs fine all way throw until the mail object get an
error. Like this:
Logon failed: Unable to get the default MAPI Message Store due to MAPI
error 273: Det g=E5r inte att =F6ppna informationsarkivet.
Does any one know what to do?
Cheers! /Karinkarin.w74@.hotmail.com wrote:
> Hi, Im trying to do a DTS that exports data to a excel file and then
> mail it. It runs fine all way throw until the mail object get an
> error. Like this:
> Logon failed: Unable to get the default MAPI Message Store due to MAPI
> error 273: Det går inte att öppna informationsarkivet.
> Does any one know what to do?
> Cheers! /Karin
Hi,
It sounds like the user that runs the SQLServer service hasn't got
access to logon to either the mailbox or to the Exchange server you are
using. You might have to check the the user that runs SQL Server also
is the one that has an Outlook profile setup on the server - otherwise
you might run into the problem.
Regards
Steen Schlüter Persson
CRM System Specialist / DBA
Denmark

Mail failure in DTS package

Hi, Im trying to do a DTS that exports data to a excel file and then
mail it. It runs fine all way throw until the mail object get an
error. Like this:
Logon failed: Unable to get the default MAPI Message Store due to MAPI
error 273: Det g=E5r inte att =F6ppna informationsarkivet.
Does any one know what to do?
Cheers! /K"liteblandat@.hotmail.com" wrote:
> Hi, Im trying to do a DTS that exports data to a excel file and then
> mail it. It runs fine all way throw until the mail object get an
> error. Like this:
> Logon failed: Unable to get the default MAPI Message Store due to MAPI
> error 273: Det går inte att öppna informationsarkivet.
> Does any one know what to do?
> Cheers! /K
>
Hi
Are you running this package from the designer or a job? The issue is that
the account under which it is running, does not have access to the MAPI
profile that you have configured for SQLMail. Check the accounts that SQL
Server and SQL Agent have been configured to run as and that they can access
the profile you have set up. Try logging in on the server as these accounts
and sending a test email.
HTH
John

mail client and dts send mail task

Hi,
Is it required to have microsoft outlook client up-and-running to send mail from dts package?
I have a dts package which send email if job fail or success. will it work fine if someone by mistek close outlook client running on server?
please reply.
Ram.No, the outlook client does not have to be running to use send-mail in a dts package.

The outlook client needs to be installed on the server and the profile needs to be created for the user context that will be running the dts package.

Here is where the confusion typically lies. When you run a DTS package interactively, the security context is who-ever you are logged in as. As such, it is relatively easy to debug. What most people don't understand is that if the DTS package is running based on some automated feature (timer, or another application starting it) the DTS package typically runs under the SQL Agent ID (Check the logon-id of the user that starts SQL Agent in control panel Services). You will need to use a specific ID, rather than system. This is because to use SQL Mail, you will have to interactively log-on as that user and configure outlook. Once you have done that, the SQL Agent will be able to send and receive e-mail without you being logged in.

Good luck.|||Originally posted by rmillman
No, the outlook client does not have to be running to use send-mail in a dts package.

The outlook client needs to be installed on the server and the profile needs to be created for the user context that will be running the dts package.

Here is where the confusion typically lies. When you run a DTS package interactively, the security context is who-ever you are logged in as. As such, it is relatively easy to debug. What most people don't understand is that if the DTS package is running based on some automated feature (timer, or another application starting it) the DTS package typically runs under the SQL Agent ID (Check the logon-id of the user that starts SQL Agent in control panel Services). You will need to use a specific ID, rather than system. This is because to use SQL Mail, you will have to interactively log-on as that user and configure outlook. Once you have done that, the SQL Agent will be able to send and receive e-mail without you being logged in.

Much thanks for your reply. explains me how dts send email . nothing to do with up-and-running mail client . it sends email internally useing agent.
cool.

Good luck.|||There's a great email extended procedure you can get that will allow you to send email without installing the outlook client on all your SQL servers. You can go this link for details: http://sqldev.net/xp/xpsmtp.htm

We have implemented this at LexisNexis by writing a wrapper procedure that calls the extended procedure. This provides a higher level security. You can call this from within a DTS package or via a job step within a scheduled job. We've been using this for quiet awhile with great success in all our maintenance plans for backups.