By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,908 Members | 2,055 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,908 IT Pros & Developers. It's quick & easy.

Select in a transaction hangs when selecting from remote table

P: n/a

I have 2 sql servers (A and B) and have established a link between
them using sp_addlinkedserver. I have created a table (test) on sql
server A. I have created a view (test) on sql server B that selects
from the table (test) on sql server A. When I select from the view on
sql server B I am able to retrieve rows from the view(on B)/table(on
A) with the following query:

Query on sql server B (where test is the view)

select * from test

However when I encolse it within a transaction the query hangs, I have
to kill the sql server process to cancel the query. The query that
hangs is :

begin tran
select * from B

Could someone tell me if I have missed anything. I have verified that
a linked server exists, MSDTC is running on both the servers and
remote_proc_trans is enabled.

Also, if instead of B i set up a linked server between A and some
other server, C all works well. I guess I am missing some settings for
the sql server B that will enable remote transactions. Please let me
know any comments on this.

Jul 20 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.