Linked servers in a view
-
Created a view awhile ago, and it linked to another server. Everything was working just fine for months.
Server A: sql 2000, accounting server, win 2k3 R2
Server B: sql 2005, project DB, win 2k3 R2
View on Server A linking to Server B
Recently put service packs on both servers, and updated the main accounting application on A. Well tried to actually. There was a view that was causing it to throw SQLOLEDB errors when it installed. Deleted the view, and the program installed without a hitch. Tried to recreate the view afterwards and I started getting this error:
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Read the following KB article
http://support.microsoft.com/kb/839279
Both of the servers had the Distributed Transaction Coordinator service running as Network Service and it was enabled. Changed the stuff for the first 2 steps it suggested on Server A, but not B and still did not work. The windows Firewall is not enabled on these machines.
One other thing, the link between servers still works - I can run the select portion of the view in QA, but the moment I throw Create View in there it errors out.
Anyone have suggestions on how best to resolve this or other resources to look into?
Much appreciated!
Bob
-
Have you also checked the DTC configuration on Server B? We had similiar issues between our servers running PeopleSoft, and we had to modify the DTC settings on both servers to correct the problem.
-
That did it. Thanks for the tip!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply