Re: Continue SP after Database Access Failure
From: PaulaPompey (PaulaPompey_at_discussions.microsoft.com)
Date: 02/02/05
- Next message: alansamet_at_gmail.com: "Re: Backup database without stored procedures"
- Previous message: PaulaPompey: "Re: typo error..."
- In reply to: Uri Dimant: "Re: Continue SP after Database Access Failure"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 2 Feb 2005 04:07:04 -0800
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...
> > 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:
> >
> > > 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.
> > > >
> > >
> > >
> > >
>
>
>
- Next message: alansamet_at_gmail.com: "Re: Backup database without stored procedures"
- Previous message: PaulaPompey: "Re: typo error..."
- In reply to: Uri Dimant: "Re: Continue SP after Database Access Failure"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
