Continue SP after Database Access Failure

Over night we take a copy of various live SQL databases onto another SQL
server for reporting purposes.
I have a stored procedure that compares the latest live data against the 1
day old copies to ensure that they are up to date.
I connect to the live databases using linked servers.
Here's where the problem is - when one of the external links is down or one
of the live databases is offline the stored procedure has an error and stops
.
How can I test within the stored procedure that the database on the linked
server is available? Then, based on the result, carry out an action?
Even a simple select statement against an unavailable database halts the
whole SP even though I've tried breaking the code down into seperate
transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
fails with "SQL Server does not exist or access denied."
Any advice greatly appreciated.Hi Paula,
Error handling in SQL Server 2000 is "somewhat" problematic as you have
seen.
For these cases, I use the following trick of nesting the execution scopes:
USE tempdb
select * from nonexist
select 'passed after error', @.@.error
go
-- batch was terminated without returning the message
exec ('select * from nonexist')
select 'passed after error', @.@.error
go
-- inner scope was aborted, outer scope continued
create proc p3 as
select * from nonexist
select 'passed after error', @.@.error
go
exec p1
-- batch was terminated without returning the message
create proc p2 as
select * from nonexist
go
create proc p3 as
exec p2
select 'passed after error', @.@.error
go
exec p3
-- inner procedure was aborted, outer procedure continued
This should work for most cases although some errors will stop and rollback
the whole batch including outer scopes.
I have tested it with inaccessible linked servers and it worked fine for me.
See the following thread for more details:
http://groups-beta.google.com/group...f3390d2b34758e2
HTH
Ami
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> Over night we take a copy of various live SQL databases onto another SQL
> server for reporting purposes.
> I have a stored procedure that compares the latest live data against the 1
> day old copies to ensure that they are up to date.
> I connect to the live databases using linked servers.
> Here's where the problem is - when one of the external links is down or
one
> of the live databases is offline the stored procedure has an error and
stops.
> How can I test within the stored procedure that the database on the linked
> server is available? Then, based on the result, carry out an action?
> Even a simple select statement against an unavailable database halts the
> whole SP even though I've tried breaking the code down into seperate
> transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
> fails with "SQL Server does not exist or access denied."
> Any advice greatly appreciated.
>|||Perhaps the object_id(<object> ) function can help. For example, if
object_id('mydb..mytable') will return an object id if the database and
table exists, otherwise it will return NULL.
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> Over night we take a copy of various live SQL databases onto another SQL
> server for reporting purposes.
> I have a stored procedure that compares the latest live data against the 1
> day old copies to ensure that they are up to date.
> I connect to the live databases using linked servers.
> Here's where the problem is - when one of the external links is down or
one
> of the live databases is offline the stored procedure has an error and
stops.
> How can I test within the stored procedure that the database on the linked
> server is available? Then, based on the result, carry out an action?
> Even a simple select statement against an unavailable database halts the
> whole SP even though I've tried breaking the code down into seperate
> transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
> fails with "SQL Server does not exist or access denied."
> Any advice greatly appreciated.
>|||Works for tables on the local SQL server, but not on Linked Servers, which i
s
where I'm having the problem.
Thanks for the tip anyway.
Paula
"JohnnyAppleseed" wrote:

> Perhaps the object_id(<object> ) function can help. For example, if
> object_id('mydb..mytable') will return an object id if the database and
> table exists, otherwise it will return NULL.
>
> "PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
> news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> one
> stops.
>
>|||Paula
You can check PING Server to make sure that remote server is UP or DOWN
set nocount on
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
SELECT * FROM #t_ip
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
'Do something'
END
DROP TABLE #t_ip
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:2D613817-450D-45B4-8EE2-D0B0B849D4E5@.microsoft.com...
> Works for tables on the local SQL server, but not on Linked Servers, which
is
> where I'm having the problem.
> Thanks for the tip anyway.
> Paula
> "JohnnyAppleseed" wrote:
>
SQL
the 1
or
linked
the
still|||Abolutely great! I was over complicating things for my self instead of
breaking the problem down. I will now be pinging the server using your
helpful code, then testing for the database using another great persons
suggestions from this wonderful resource!
Thanks again
Paula
"Uri Dimant" wrote:

> Paula
> You can check PING Server to make sure that remote server is UP or DOWN
> set nocount on
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> SELECT * FROM #t_ip
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> 'Do something'
> END
> DROP TABLE #t_ip
>
> "PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
> news:2D613817-450D-45B4-8EE2-D0B0B849D4E5@.microsoft.com...
> is
> SQL
> the 1
> or
> linked
> the
> still
>
>