Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-04-03 : 16:25:22
|
neil writes "SQL Server 7 & Win NT 4 Server SP6
Using TSQL & SP, Linked Server is to Oracle 8
I need to read data from Oracle into SQL7 (via linked server) on a polled basis every 10 - 15 seconds. (I know I shouldn't poll, but I cannot use SP on the Oracle system as I cannot get ownership)
The TSQL SP ....
SampleNow:
SELECT * FROM OPENQUERY(svrOracle, 'SELECT ....
to read the data from the Oracle DB
WAITFOR DELAY '00:00:10'
GOTO SampleNow
The SP Works fine!
However I need to guard against the remote Linked server (svrORACLE) not responding / being unavailable (eg for DB maint, or network fault)
Currently this causes fatal errors in the TSQL and causes the script to abort, this cannot be trapped within TSQL!
Currently the only way I can get around this is to use the Job agent...
Step
1 - Ping Remote Oracle Server
- on success goto next step, on failure goto step 4
2 - Check if linked server in sysservers add if not
- on success goto next step, on failure goto step 4
3 - Select Data using OPENQUERY
- on success goto next step, on failure goto step 4
4 - WAITFOR DELAY '00:05:00'
- on success goto step 1, on failure 'Quit with Failure'
The job Agent solution works, however it seems to be a crude method for detecting that there is a linked server fault!
Are there better ways of checking the availability of a linked server prior to using it, or other ways to prevent or trap the Fatal Error in TSQL?
Thanks in advance for your advice." |
|
|
|
|
|
linked server - handling fatal errors in TSQL