Linked server hang-ups

[Michael Gardner]

I am pulling data on a regular basis from a remote sybase
database via a linked server and openquery into SQL
2000. I ran into a problem this weeked where a corupted
index on a rather large table on the remote database
caused the retrieval to hang for two days. The processes
showed the job id as waiting on oledb and showed the
remote server name. I killed the job and the status went
to "KILLED/ROLLED BACK" but still didn't go away. I
waited several hours and still nothing. I found a
procedure (sp_status?) that would give status of a
procedure being rolled back and it returned 100%
complete, 0 seconds remaining, yet the job and all
associated locks were still there. Finally I did a
stop/start on the server.

I have since found that it appears the default timeout
setting on a linked server is 0 or "wait forever". On
the third tab of the remote server I found timeout
options which i set to 60 connect and 60 query timeout.
Some initial testing looked ok and SQL was retuning a
timeout error instead of waiting.

Today the remote index problem arose again and SQL hung
again and wouldn't timeout. After a stop/start and
another test, then it was timing out. Ahhhhh!!!!!

Is there any other options i can set in the stored
procedure to force a timeout if the other side isn't
responding? I looked at the ODBC driver options in
Control/Panel, Data Sources that the linked server is
using but there are no options there.

This is the first time I've run into any job hanging in
SQL that I couldn't clear and had to restart.

[Gustavo Jacob]

Hi,

I'm also having this problem.. And also have to stop/strat my server. But my
linked server is an ORCLE.
Does someone has teh solution. We starting to have this problem more and mor
often, wich is causing so much problems.

Tanks.

"Michael Gardner" <michael...@cingular.com> escreveu na mensagem
news:37b001c26021$d189ee20$39ef2ecf@TKMSFTNGXA08...