SQL Mail vs Agent Mail Question

[kodaksmile]

I know this is probably simple but, here is the situation I am in. We are creating a dts package that will have a mail task inside. The operators that will be running this will RDP logged in as themselves up to the sql server and either run the DTS package or a job manually. Our Sql agent service and SQL server service run under 2 different domain accounts. If I configure 2 separate outlook profiles which will I assign in the DTS package? or do I need to create one profile and configure both services to use the same by configuring the password/remember password when I login to outlook the first time as the service?

Any recommendation?

Thanks

 

[mrdenny]

If memory serves correctly...

Assuming that you are using the DTS mail task you will need to setup the profile in the Operators account.

If you are using xp_sendmail via a T/SQL command you will need to setup the profile in the account running the SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[kodaksmile]

The challenge I ran into is that for some reason when I login with the operators account (profile configured) the mail does not send until Outlook is opened on the server....which makes it seem like sql mail is not running and unlike xp_sendmail does not start sql mail. I just don't know why or how to get past this.....

If I run the dts from my desk it works fine and emails immediately (of course outlook is running).

If I run it as a job it works fine without outlook open.

I am definitely missing something.

 

[mrdenny]

If you run the package as a job, it's executing on the server and outlook is running, as SQL Mail has started outlook. I can see it as needing to have outlook running before it will sent.

I can think of three options.

1. Have the operator open up Outlook before running the package.
2. Have the package run outlook.
3. Rewrite the package to use xp_sendmail so that it runs from the server.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com