Deadlock for remote SELECT statement

Last Post 14 Sep 2005 11:21 PM by NR75. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
jchung05
New Member
New Member

--
08 Sep 2005 10:47 AM
Hi all
I have a query statement from remote box (linkedserver) but sometimes the SELECT statement hung a long time (1 hour but usually about 1/2 minutes)..I don't know why the select statement would issue a lock in the remote box blocked the local process and generated deadlock situation. I would like to see any I can issue a lock time out..I can see one SET LOCK_TIMEOUT...but not sure how to use it in the connection if the query already launched to the remote box.. can someone guide me through this? or if you better idea for me to resolve the deadlock issue that would be great appreciate..thanks in advance!

Bst Rgds,
Jimmy
jchung05
New Member
New Member

--
08 Sep 2005 01:57 PM
I did try this before but it's not allowed and returned the following message

Server: Msg 7377, Level 16, State 1, Line 1
Cannot specify an index or locking hint for a remote data source.
NR75
New Member
New Member

--
14 Sep 2005 11:21 PM
What is your query doing? Is it just a select, or is it an insert of remote data on a local server?
Note that you can use locking hints if you do the select as a stored proc on the remote server and then just call the remote stored proc in local server.
SQLUSA
New Member
New Member

--
06 Oct 2005 12:52 AM
Set connection and query time out to 77777 in the properties window.


Kalman Toth, SQL Guru
SQL Server 2005 Training
http://www.sqlusa.com
You are not authorized to post a reply.

Acceptable Use Policy