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 |
|
JeffMbham
Starting Member
4 Posts |
 Posted - 2002-04-30 : 15:17:00
|
| I'm running SQL Server 2000 and I have a linked server setup to another SQL Server 2000 box. I've got a stored procedure that creates a temporary table and is pulling information from local tables and from tables located on the linked server. When I run this stored procedure it bombs on me when it hits the linked server part returning an error message of: Server: Msg 7391, Level 16, State 1, Procedure app_GetOfficeOrders, Line 29The 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. Now I can run a query against a table located on the linked server that returns just a table count and everything works fine. I'm just having problems with that one stored procedure. Any suggestions would be appreciated.P.S. I have verified that it isn't a time-out error and it isn't a permissions issues. Thanks, |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-04-30 : 15:47:06
|
| A couple of things you can try...Make sure that MSDTC service is running. Try issuing SET XACT_ABORT ON command before you make a call to the linked server.---------------Strong SQL Developer wanted in the Boston area. Please e-mail if interested.Edited by - izaltsman on 04/30/2002 15:48:58 |
 |
|
|
JeffMbham
Starting Member
4 Posts |
Posted - 2002-04-30 : 16:11:38
|
| Thanks for the tip but its an all or nothing deal. So I can't just have one portion rolled back. Also, I verified that the MSDTC is running. |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-04-30 : 16:51:11
|
quote:
Thanks for the tip but its an all or nothing deal. So I can't just have one portion rolled back.
Umm... Are you saying that you can't use XACT_ABORT because you require that your transaction stays atomic?In that case don't worry -- XACT_ABORT will roll back your entire transaction as soon as it encounters any runtime errors... The integrigy of transaction will be preserved. So go ahead and try it. It might help.---------------Strong SQL Developer wanted in the Boston area. Please e-mail if interested. |
|
|
|
|
|
|
Linked Server: Error 7391