Solved

Linked Servers to Oracle-ODBC HANGS How do I kill store procedure?

Posted on 2001-08-07
2
272 Views
Last Modified: 2011-10-03
I have a Linked Server to the Oracle Database and occasionally my SQL7 - Store Procedure HANGS.
It Hangs in the Oracle OLEDB  (also hangs in the ODBC) --- Since this is an automated process...is there a function or codes that I can incorporate in my store procedure to capture this hanging problem/process --- because I need to automatically terminate the store procedure and re-query.

0
Comment
Question by:emailkimchan
2 Comments
 
LVL 18

Accepted Solution

by:
nigelrivett earned 50 total points
Comment Utility
You can put in a scheduled task which looks at master..sysprocesses for this hanging process - or probably better to have a table

Process
   spid
   starttime
   endtime
   logontime

The oracle process puts it's spid, logontime and starttime and logontime in this table at start and either deletes the entry or updates the endtime on completion (the logontime is so that you can tell if the spid is still the same one and it hasn't crashed before competion).

The scheduled task can then kill the process.
It depends on how this is hanging as to whether a kill will work but is probably the best you can do - could also send an email to tell someone to look at it.
0
 

Author Comment

by:emailkimchan
Comment Utility
THANK YOU..THANK YOU..
and thank you for the "GREAT" suggestions!!!!!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now