typo error...
From: Ami Levin (XXX_NOSPAM___XXX__amlevin_at_mercury.com)
Date: 02/01/05
- Next message: Tom Moreau: "Re: newbie - Stored procedure"
- Previous message: Bob Barrows [MVP]: "Re: query help, almsot got it"
- In reply to: Ami Levin: "Re: Continue SP after Database Access Failure"
- Next in thread: PaulaPompey: "RE: typo error..."
- Reply: PaulaPompey: "RE: typo error..."
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 1 Feb 2005 14:03:12 +0200
First Proc should be p1 and not p3
> create proc p1 as
> select * from nonexist
> select 'passed after error', @@error
> go
>
> exec p1
> -- batch was terminated without returning the message
"Ami Levin" <XXX_NOSPAM___XXX__amlevin@mercury.com> wrote in message
news:uqAPs2ECFHA.3824@TK2MSFTNGP10.phx.gbl...
> 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.
> >
>
>
- Next message: Tom Moreau: "Re: newbie - Stored procedure"
- Previous message: Bob Barrows [MVP]: "Re: query help, almsot got it"
- In reply to: Ami Levin: "Re: Continue SP after Database Access Failure"
- Next in thread: PaulaPompey: "RE: typo error..."
- Reply: PaulaPompey: "RE: typo error..."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
