SQL Mail Setup

Hi,

I'm taking over a SQL Server environment that currently is not configured to
email operators.  I'm looking to configure SQL Mail for the SQL Server so
that I can receive email notifications for jobs and alerts.  Reading through
the KB it seems as though the SQL Server must have an Outlook client
installed and configured for the domain user account for the SQL Server/SQL
Server Agent.  We do have an Exchange server configured in the environment.
However, my network administrator is concerned about installing the Outlook
client on the SQL Server because of resource consumption concerns.  He is
asking why we can't use SMTP instead.  Why is this?

Also, I've read that there is a tool out there that can be used instead of
using an Outlook client.  What is this tool?  Is it safe for a production
environment?  Is it supported?  Or is Outlook still the better choice?

SQL Server 2000 EE (SP3)
Windows 2000 Server (SP4)

Thanks.

Jerry

Im doing the same thing as you currently.

http://www.sqldev.net/xp/xpsmtp.htm



"Jerry Spivey" <jspivey@vestas-awt.com> wrote in message
news:O%23qamBjYFHA.1152@tk2msftngp13.phx.gbl...
> Hi,
>
> I'm taking over a SQL Server environment that currently is not configured
> to email operators.  I'm looking to configure SQL Mail for the SQL Server
> so that I can receive email notifications for jobs and alerts.  Reading
> through the KB it seems as though the SQL Server must have an Outlook
> client installed and configured for the domain user account for the SQL
> Server/SQL Server Agent.  We do have an Exchange server configured in the
> environment. However, my network administrator is concerned about
> installing the Outlook client on the SQL Server because of resource
> consumption concerns.  He is asking why we can't use SMTP instead.  Why is
> this?
>
> Also, I've read that there is a tool out there that can be used instead of
> using an Outlook client.  What is this tool?  Is it safe for a production
> environment?  Is it supported?  Or is Outlook still the better choice?
>
> SQL Server 2000 EE (SP3)
> Windows 2000 Server (SP4)
>
> Thanks.
>
> Jerry
>

Chris - I'm actually looking to configure mail more for SQLAgentMail (didn't
say that earlier) for job failures and alert firings.  This tool looks more
for SQL Mail functionality in procs and triggers...is that correct?


"ChrisR" <noemail@bla.com> wrote in message
news:uSoxZEjYFHA.228@TK2MSFTNGP12.phx.gbl...
> Im doing the same thing as you currently.
>
> http://www.sqldev.net/xp/xpsmtp.htm
>
>
>
> "Jerry Spivey" <jspivey@vestas-awt.com> wrote in message
> news:O%23qamBjYFHA.1152@tk2msftngp13.phx.gbl... 
>
>

Hi Jerry,

For what it's worth we're using Outlook for this purpose, and find that the
configuration works quite well.

These links might also be worth a look:

http://www.aspfaq.com/show.asp?id=2403
http://support.microsoft.com/defaul...;311231&sd=tech

HTH

--
Message posted via http://www.webservertalk.com

Some things to consider...

http://www.karaszi.com/SQLServer/info_no_mapi.asp
http://www.karaszi.com/SQLServer/util_smtp_alerter.asp

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/


"Jerry Spivey" <jspivey@vestas-awt.com> wrote in message
news:O%23qamBjYFHA.1152@tk2msftngp13.phx.gbl...
> Hi,
>
> I'm taking over a SQL Server environment that currently is not configured 
to email operators.  I'm
> looking to configure SQL Mail for the SQL Server so that I can receive ema
il notifications for
> jobs and alerts.  Reading through the KB it seems as though the SQL Server
 must have an Outlook
> client installed and configured for the domain user account for the SQL Se
rver/SQL Server Agent.
> We do have an Exchange server configured in the environment. However, my n
etwork administrator is
> concerned about installing the Outlook client on the SQL Server because of
 resource consumption
> concerns.  He is asking why we can't use SMTP instead.  Why is this?
>
> Also, I've read that there is a tool out there that can be used instead of
 using an Outlook
> client.  What is this tool?  Is it safe for a production environment?  Is 
it supported?  Or is
> Outlook still the better choice?
>
> SQL Server 2000 EE (SP3)
> Windows 2000 Server (SP4)
>
> Thanks.
>
> Jerry
>

Before You Set Up SQL Mail
SQL Mail requires a mail connection, a mailbox, a mail profile, and the
Windows NT account you used to start SQL Server. This Windows NT account
needs to be a domain account if you are using an Exchange server as your mai
l
server. It can be a local or domain NT account if you are using POP3/SMTP
mail server.

The basic procedure is to log on to your Windows NT server using the account
you used to start SQL Server, set up your mail profile, test the mail
connection, and then provide this mail profile to SQL Mail. When you choose 
a
profile name, try not to include special characters (such as hyphens, pound
signs, periods, and so forth) because they may work with Exchange clients
(such as Outlook), but not with the SQL Server 6.5 version of SQL Mail. For
the same reason, the profile name cannot be longer than 32 characters.

For best results, use Microsoft Exchange Server for sending and receiving
SQL Mail. Microsoft Windows NT Mail is a limited mail program that was
included with Microsoft Windows NT prior to the release of the full featured
mail services provided by the Microsoft Exchange Server product line and
cannot be used with SQL Mail 2000.

If you are using a third-party mail server (such as Lotus Notes, Lotus
cc:Mail, Novell GroupWise, and so forth), you must configure the mail server
as a POP3 server. Microsoft does not support connecting to these mail server
s
using the native mail services that might be installed by the third-party
mail client. Some third-party mail clients do not support the MAPI required
by SQL Mail. Contact the third-party vendor or refer to the
http://www.microsoft.com/exchange/default.asp Web site for assistance
concerning the use of their mail clients or the availability of a Exchange
connector to integrate both Exchange and your current mail service provider.
How To Set Up SQL Mail With Exchange
1. On the Microsoft Exchange server, set up a mailbox for the user account
that is to be used by the MSSQLServer service. This account must be a domain
account. For example:
Account:  DOMAIN1\SQLServerAcc
ount
Mailbox: SQL1
2. On the SQL Server computer, log on to Windows NT by using the same user
account that is to be used by the MSSQLServer service.

In the preceding example in step 1 it would be:
 DOMAIN1\SQLServerAcc
ount
3. Install an Exchange client on the SQL Server computer. When running SQL
Server 6.5 or SQL Server 7.0, this can be the client application that ships
with Microsoft Exchange Server, Microsoft Outlook 98 or Microsoft Outlook
2000. The addition of the Outlook security patch for Outlook 2000 to prevent
the spread of e-mail viruses will cause SQL Mail 6.5 or 7.0 to stop
responding (hang). Because Outlook 2002 also includes this security feature,
it will also cause SQL Mail 6.5 or 7.0 to hang. (See the "Outlook 2000
Client" section later in this article for a detailed explanation of the
cause.)

When running SQL Server 2000, the mail client must be the Microsoft Outlook
2000 or Outlook 2002 client. Because SQL Mail 2000 makes an extended MAPI
connection to the mail server, it is not affected by the Outlook security
features. With SQL Server 2000 Service Pack 1, you can also use Outlook 98.

For additional information, click the article number below to view the
article in the Microsoft Knowledge Base:
281293 FIX: SQL Mail 2000 Needs Outlook 2000 Client
4. Start the Exchange client and configure the client to connect to the
Microsoft Exchange Server. You will need to provide the name of the Exchange
server and the mailbox on the Exchange server. After the setup is complete,
verify that you can send and receive mail interactively. You can then close
the client.
5. Start the Mail application in Control Panel. Click Show Profiles to find
the name of the profile that was configured in step 4. If the profile name i
s
longer than 32 characters or contains unusual characters (periods, hyphens,
pound signs, and so forth), change the profile name to be less than 32
characters and remove the unusual characters (spaces are okay).
6. In Control Panel, click Services, and then verify that the MSSQLServer
service is configured to run under the same Windows NT user account that you
logged on with in step 2. Start or restart the MSSQLServer service if
necessary. In SQL Server 7.0 and SQL Server 2000, make any startup account
changes by way of the SQL Enterprise Manager if you are using Full Text
Search on the computer.
7. Configure SQL Mail to use the profile name you found in step 5. SQL Mail
uses the account and password configured for the MSSQLServer service to logi
n
to the Exchange server.

NOTE: If the profile name that you type in is not recognized or no profiles
appear in the drop-down list box for SQL Server 7.0 or SQL Server 2000, you
have started the MSSQLServer service under the Local System account. Change
the service to start under a domain account, stop and restart SQL Server, an
d
then try to configure the mail profile again.
8. Start SQL Mail. The SQL Mail icon becomes green if SQL Mail starts
successfully. (This step is not necessary when using SQL Server 2000 because
SQL Mail will be automatically started when you first try to send mail.) Tes
t
SQL Mail by opening a query window and use xp_sendmail to send e-mail to
yourself. If you are using SQL Server 7.0 or SQL Server 2000, also configure
SQLAgentMail with the same profile. You can test SQLAgentMail by sending mai
l
to an operator.


----
Installing the outlook client on the SQL Server box should not consume your
system resources since you are not going to having the outlook program open
all the time on the server you would just have the profile being used to
email alerts when your jobs fail.

Take a look at this article which will tell you about the different options
you have and how to configure your enviornment with whatever method you
choose.
http://support.microsoft.com/defaul...;263556&sd=tech






"Jerry Spivey" wrote:

> Hi,
>
> I'm taking over a SQL Server environment that currently is not configured 
to
> email operators.  I'm looking to configure SQL Mail for the SQL Server so
> that I can receive email notifications for jobs and alerts.  Reading throu
gh
> the KB it seems as though the SQL Server must have an Outlook client
> installed and configured for the domain user account for the SQL Server/SQ
L
> Server Agent.  We do have an Exchange server configured in the environment
.
> However, my network administrator is concerned about installing the Outloo
k
> client on the SQL Server because of resource consumption concerns.  He is
> asking why we can't use SMTP instead.  Why is this?
>
> Also, I've read that there is a tool out there that can be used instead of
> using an Outlook client.  What is this tool?  Is it safe for a production
> environment?  Is it supported?  Or is Outlook still the better choice?
>
> SQL Server 2000 EE (SP3)
> Windows 2000 Server (SP4)
>
> Thanks.
>
> Jerry
>
>
>

Jerry too many people with far more knowledge than me aren't too hip on the
Outlook/ Exchange method. I've used it in the past and was quite happy with
it. But then I was starting to go down that road at my new job and no longer
wanted to use it. There were several reasons for this:

1. I don't want to install Outlook on all my SQL boxes. (security, extra
app)
2. I dont want to have to reboot them so SQL can see my new Profiles. (This
isnt always the case, but often enough to be annoying.)
3. Our Exchange box isnt the most reliable thing in the world.
4. Most of my SQL boxes are in 1 location and my Exchange box is in the
other.

Im still in the testing phase of implmenting this DLL. Im testing purely as
a formality as lots of people on this NG have recommended it very highly and
Im not worried about it. And yes, the DLL can be used for job alerts as
referenced in Tibors article. It is a bit cumbersome Ill admit, but in face
of all the objections I have for the alternative, Ill take it.



"Jerry Spivey" <jspivey@vestas-awt.com> wrote in message
news:e2sIHJjYFHA.3960@TK2MSFTNGP10.phx.gbl...
> Chris - I'm actually looking to configure mail more for SQLAgentMail
> (didn't say that earlier) for job failures and alert firings.  This tool
> looks more for SQL Mail functionality in procs and triggers...is that
> correct?
>
>
> "ChrisR" <noemail@bla.com> wrote in message
> news:uSoxZEjYFHA.228@TK2MSFTNGP12.phx.gbl... 
>
>

You will still need to go change the last step of each job to go to this
new step on failure. But these will help.

ALTER   procedure  admin_AlertFailedJob


@ServerName varchar(256)

,@JobName varchar(256)

as

set @JobName = @JobName+' failed!!!'

exec master.dbo.xp_smtp_sendmail

@FROM   = @ServerName

,@TO     = N'chris. rose@OurVacationStor
e.com'

,@Subject    = @JobName

,@server      = N'10.1.69.200'

,@Port        = 25







Code to add to lots of jobs:



declare @ServerName varchar(128)

declare @Job_name varchar(256)

declare @CommandString varchar(512)



set @ServerName = 'IBM-T42-08'

set @Job_name = 'SMTP Test'

set @CommandString = 'exec  admin_AlertFailedJob
 ''' + @ServerName+'''' +
','''+ @job_name+''''



exec sp_add_jobstep @job_name =  @Job_name

,  @step_name =  'FailureAlert'

,  @command =  @CommandString

,  @on_success_action = 2

"Jerry Spivey" <jspivey@vestas-awt.com> wrote in message
news:e2sIHJjYFHA.3960@TK2MSFTNGP10.phx.gbl...
> Chris - I'm actually looking to configure mail more for SQLAgentMail
> (didn't say that earlier) for job failures and alert firings.  This tool
> looks more for SQL Mail functionality in procs and triggers...is that
> correct?
>
>
> "ChrisR" <noemail@bla.com> wrote in message
> news:uSoxZEjYFHA.228@TK2MSFTNGP12.phx.gbl... 
>
>