Re: Continue SP after Database Access Failure

From: Ami Levin (XXX_NOSPAM___XXX__amlevin_at_mercury.com)
Date: 02/01/05 

Date: Tue, 1 Feb 2005 13:04:25 +0200

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/microsoft.public.sqlserver.programming/browse_thread/thread/df3390d2b34758e2

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.
>

Relevant Pages

  • Continue SP after Database Access Failure
    ... Over night we take a copy of various live SQL databases onto another SQL ... server for reporting purposes. ... of the live databases is offline the stored procedure has an error and stops. ...
    (microsoft.public.sqlserver.programming)
  • Re: T-SQL
    ... connections to two different databases. ... A stored procedure runs on one connection, ... multiple databases on the same instance of SQL Server. ...
    (comp.databases.ms-sqlserver)
  • Re: SQL Enable FullText automaticly on reboot
    ... Create a stored procedure and mark it as a startup stored procedure ... > Is there a way to execute the sp_fulltext_enable stored proc automaticly ... > after a server rebooted on all the databases in the server? ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored Procedure for Logical File Names
    ... > Is there a stored procedure on SQL2000 that will give me the logical file ... > database and select name from sysfiles on each databases but would like to ... Dejan Sarka, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: AS2005 ... what is wrong with it?
    ... What I have seen is that the dev server is *faster* than the prod server ... And does your dev server also have all 6 databases with all these roles? ... When I restore the "PROV" i got approx 20 sec. ... Starting from an empty data folder should show if this theory is true ...
    (microsoft.public.sqlserver.olap)