Check if the mailserver is running before using it

Hi,
We are using several packages to accomplish some database tasks overnight.
In these packages we have included some SQL statements with the xp_sendmail
command for reporting purposes.
Is there a way to check if our mailserver is running and ready to process
the xp_sendmail command rather than resolving an error when the command
fails? Because it takes quite a long time before SQL server reports an error
that the mailserver is not working properly.
I prefer to have a stored procedure that reports if the mailserver is ready
or not. Then I can use that result to decide wheter or not to skip the
xp_sendmail command.
We are using SQL Server 7.0 with Exchange 5.5.
The mailserver is a different server then the SQL server.
Any ideas are appreciated!I make use of this http://www.sqldev.net/xp/xpsmtp.htm rather than
xp_sendmail.
With it you have the ability to ping the server and check the Return Code
declare @.rc int
exec @.rc = master.dbo.xp_smtp_sendmail
@.server = N'mail.sqldev.net',
@.port = 25,
@.ping = 1
select RC = @.rc
go
HTH. Ryan
"a" <a@.b.c> wrote in message news:eqa5u8CeGHA.564@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are using several packages to accomplish some database tasks overnight.
> In these packages we have included some SQL statements with the
> xp_sendmail
> command for reporting purposes.
> Is there a way to check if our mailserver is running and ready to process
> the xp_sendmail command rather than resolving an error when the command
> fails? Because it takes quite a long time before SQL server reports an
> error
> that the mailserver is not working properly.
> I prefer to have a stored procedure that reports if the mailserver is
> ready
> or not. Then I can use that result to decide wheter or not to skip the
> xp_sendmail command.
> We are using SQL Server 7.0 with Exchange 5.5.
> The mailserver is a different server then the SQL server.
> Any ideas are appreciated!
>
>|||"a" <a@.b.c> wrote in message news:eqa5u8CeGHA.564@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are using several packages to accomplish some database tasks overnight.
> In these packages we have included some SQL statements with the
xp_sendmail
> command for reporting purposes.
> Is there a way to check if our mailserver is running and ready to process
> the xp_sendmail command rather than resolving an error when the command
> fails? Because it takes quite a long time before SQL server reports an
error
> that the mailserver is not working properly.
> I prefer to have a stored procedure that reports if the mailserver is
ready
> or not. Then I can use that result to decide wheter or not to skip the
> xp_sendmail command.
> We are using SQL Server 7.0 with Exchange 5.5.
> The mailserver is a different server then the SQL server.
My advice:....
Install the SMTP Server that comes with IIS on the SQL Server box and have
the MAPI client setup to send THROUGH that.
It can then forward to your Exchange server.
Using this basic technique, I've basically eliminated my problems with
xp_sendmail hanging.

> Any ideas are appreciated!
>
>

Check if the mailserver is running before using it

Hi,
We are using several packages to accomplish some database tasks overnight.
In these packages we have included some SQL statements with the xp_sendmail
command for reporting purposes.
Is there a way to check if our mailserver is running and ready to process
the xp_sendmail command rather than resolving an error when the command
fails? Because it takes quite a long time before SQL server reports an error
that the mailserver is not working properly.
I prefer to have a stored procedure that reports if the mailserver is ready
or not. Then I can use that result to decide wheter or not to skip the
xp_sendmail command.
We are using SQL Server 7.0 with Exchange 5.5.
The mailserver is a different server then the SQL server.
Any ideas are appreciated!I make use of this http://www.sqldev.net/xp/xpsmtp.htm rather than
xp_sendmail.
With it you have the ability to ping the server and check the Return Code
declare @.rc int
exec @.rc = master.dbo.xp_smtp_sendmail
@.server = N'mail.sqldev.net',
@.port = 25,
@.ping = 1
select RC = @.rc
go
--
HTH. Ryan
"a" <a@.b.c> wrote in message news:eqa5u8CeGHA.564@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are using several packages to accomplish some database tasks overnight.
> In these packages we have included some SQL statements with the
> xp_sendmail
> command for reporting purposes.
> Is there a way to check if our mailserver is running and ready to process
> the xp_sendmail command rather than resolving an error when the command
> fails? Because it takes quite a long time before SQL server reports an
> error
> that the mailserver is not working properly.
> I prefer to have a stored procedure that reports if the mailserver is
> ready
> or not. Then I can use that result to decide wheter or not to skip the
> xp_sendmail command.
> We are using SQL Server 7.0 with Exchange 5.5.
> The mailserver is a different server then the SQL server.
> Any ideas are appreciated!
>
>|||"a" <a@.b.c> wrote in message news:eqa5u8CeGHA.564@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are using several packages to accomplish some database tasks overnight.
> In these packages we have included some SQL statements with the
xp_sendmail
> command for reporting purposes.
> Is there a way to check if our mailserver is running and ready to process
> the xp_sendmail command rather than resolving an error when the command
> fails? Because it takes quite a long time before SQL server reports an
error
> that the mailserver is not working properly.
> I prefer to have a stored procedure that reports if the mailserver is
ready
> or not. Then I can use that result to decide wheter or not to skip the
> xp_sendmail command.
> We are using SQL Server 7.0 with Exchange 5.5.
> The mailserver is a different server then the SQL server.
My advice:....
Install the SMTP Server that comes with IIS on the SQL Server box and have
the MAPI client setup to send THROUGH that.
It can then forward to your Exchange server.
Using this basic technique, I've basically eliminated my problems with
xp_sendmail hanging.
> Any ideas are appreciated!
>
>

Check Constraint

Here is what I am trying to accomplish, and I would like to know if it is
possible through the use of check constraints.
Two tables Table_A and Table_B. When I insert new record in Table_A I would
like to check for the existence of a record, through the use of a check
constraint, in Table_B. If the record exists then insert the record,
otherwise the insert fails.
If this is possible, how would I set up the expression? I have tried the
EXISTS statement with no success.
Thanks,On Wed, 26 Apr 2006 15:01:02 -0700, Robert wrote:

>Here is what I am trying to accomplish, and I would like to know if it is
>possible through the use of check constraints.
>Two tables Table_A and Table_B. When I insert new record in Table_A I would
>like to check for the existence of a record, through the use of a check
>constraint, in Table_B. If the record exists then insert the record,
>otherwise the insert fails.
>If this is possible, how would I set up the expression? I have tried the
>EXISTS statement with no success.
>Thanks,
Hi Robert,
Rather than using a CHECK constraint, use a FOREIGN KEY (aka REFERENCES)
constraint for this.
Hugo Kornelis, SQL Server MVP|||Robert (Robert@.discussions.microsoft.com) writes:
> Here is what I am trying to accomplish, and I would like to know if it is
> possible through the use of check constraints.
> Two tables Table_A and Table_B. When I insert new record in Table_A I
> would like to check for the existence of a record, through the use of a
> check constraint, in Table_B. If the record exists then insert the
> record, otherwise the insert fails.
> If this is possible, how would I set up the expression? I have tried the
> EXISTS statement with no success.
You can do this, if you put the check in a user-defined function.
But don't do this! I tried it once. An update of all rows in a table with
24000 rows went from one second to 30 seconds.
Inter-table checks are best done in a trigger.
Unless, we are talking foreign-key checks, in which case you should
use a FOREIGN KEY constraint:
ALTER TABLE Orders ADD CONSTRAINT
FOREIGN KEY fk_Orders_Customers (CustomerID)
REFERENCES Customers(CustomerID)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx