Trying to figure this out

CREATE Procedure sp_advname_2_alabama
(
@searchfirstname varchar(300) =NULL,
@searchlastname varchar(300),
@sortfield varchar(50),
@sortorder varchar(50)
)
WITH ENCRYPTION
AS

DECLARE @searchstr varchar(3000)

set @searchstr = 'Select * from
openquery(washingtonco,''alenv3.dbo.sp_stdnamesearch '''''
+@searchfirstname + ''''',''''' + @searchlastname + ''''', '''''+
@sortfield +''''', '''''+ @sortorder +''''''')'
set @searchstr = @searchstr + 'UNION ALL '
set @searchstr = @searchstr + 'Select * from
openquery(saraland,''alenv3.dbo.sp_stdnamesearch ''''' +@searchfirstname
+ ''''',''''' + @searchlastname + ''''', '''''+ @sortfield +''''',
'''''+ @sortorder +''''''')'
set @searchstr = @searchstr + 'UNION ALL '
set @searchstr = @searchstr + 'Select * from
openquery(satsuma,''alenv3.dbo.sp_stdnamesearch ''''' +@searchfirstname
+ ''''',''''' + @searchlastname + ''''', '''''+ @sortfield +''''',
'''''+ @sortorder +''''''')'
set @searchstr = @searchstr + 'ORDER BY plastname, pfirstname'

EXEC(@searchstr)

The Above is my current Procedure which work 100% when all of the remote
servers are online,
Fails when any one of the remote servers goes offline.

How can I make the sproc check the connection to each remote server and
only return data from the servers that respond, skipping the offline
servers.

Thanks in advance for any advice

Michael Florkiewicz
Coastal Security Solutions, Inc.
http://www.coastalsecuritysolutions.com

Provider of Remote Backup Services
Get your free trial at
http://www.cssibackup.com



> -----Original Message-----
> From: Javed
> [mailto:mssqldba-ezmlmshield-x43592642.[Email address protected]
> Sent: Monday, April 11, 2005 4:24 AM
> To: LazyDBA Discussion
> Subject: RE: SQL Mail
>
>
>
> Hi,
>
> How safe it is to use 'xpsmtp80.dll'. Any one have tried it???
>
> Regards,
>
> Javed Iqbal
>
>
> -----Original Message-----
> From: Hugh du Toit
> [mailto:mssqldba-ezmlmshield-x53960956.[Email address protected]
> Sent: Monday, April 11, 2005 1:57 PM
> To: LazyDBA Discussion
> Subject: RE: SQL Mail
>
>
> Do not use SQL mail.
>
> Rather use something like http://www.sqldev.net/xp/xpsmtp.htm or
> http://www.databasejournal.com/features/mssql/article.php/3489111.
>
> With these procedures you send out mail by connecting to a
> mail server on
> your network, and not by installing outlook on your SQL
> server (required by
> SQL mail)
>
> -----Original Message-----
> From: Sunil Seth
> [mailto:mssqldba-ezmlmshield-x68258993.[Email address
> protected]
> Sent: Saturday, April 09, 2005 00:39 AM
> To: LazyDBA Discussion
> Subject: Re: SQL Mail
>
> Hi all
>
> I am trying to set Sql mail to receive mails if my backup
> fails or succeeds.
> But when I right click on sql server agent and try to go to
> mail session my
> mail profile is disabled.. Why is that and what do I do to resolve it.
>
> Any help would be appreciated
>
> Thanks
>
> Sunil
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
> For additional commands, e-mail: mssqldba-[Email address protected]
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
> For additional commands, e-mail: mssqldba-[Email address protected]
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
> For additional commands, e-mail: mssqldba-[Email address protected]
>
>

MS Sql Server LazyDBA home page