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 |
|
catparks
Starting Member
18 Posts |
 Posted - 2005-06-06 : 14:23:19
|
I am attempting to update a database on one sql server from a database from a different sql server. (server1.db1 to server2.db2) The update works from Query Analyzer, but I get the following error when I execute the same statement in a stored procedure:Server: Msg 7391, Level 16, State 1, Procedure YardiAttributeValuesInsProc, Line 31The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver]Distributed transaction error]OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].I added the statements "set quoted_identifier on;set ansi_defaults on;" in the beginning of the procedure. Any help on resolving this issue is appreciated. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-06-06 : 14:50:32
|
From BOL (error 7391):quote:
This error can occur while processing an INSERT, UPDATE, or DELETE statement inside an explicit or implicit transaction. This indicates that the OLE DB provider does not support distributed transactions, which is needed for data modification statements inside an explicit or implicit transaction. A data modification statement can be executed against such a provider only in the case where the statement is a transaction by itself.Verify that the OLE DB provider specified supports distributed transactions. If the provider does not support distributed transactions, rewrite the data modification statement not to use distributed transactions.
Be One with the OptimizerTG |
 |
|
|
catparks
Starting Member
18 Posts |
Posted - 2005-06-06 : 15:12:28
|
| Thanks for the info., but I don't know how helpful the BOL is in this case. I have this type of query working between 2 other SQL servers - and those linked servers are set up the same way. (They are also the same SQL version.) |
|
|
|
catparks
Starting Member
18 Posts |
Posted - 2005-06-06 : 15:28:49
|
| I also still do not understand why the update works in Query Analyzer, but not in the stored procedure? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-06-06 : 15:46:37
|
| I don't either...Is the SP run from query analyzer or is it run from an application?Can you paste all the code from the sp into a QA window and run it successfully?conversely, can you eliminate all the code from the sp except the linked server statement and have it fail?Be One with the OptimizerTG |
|
|
|
catparks
Starting Member
18 Posts |
Posted - 2005-06-07 : 09:05:13
|
| I was trying to execute the SP from Q.A. I took your suggestion, pasted all the code, and it failed. I didn't get it working again until I took the "Begin Trans" and "Commit Trans" statements out of the query. I even tried using "Begin Distributed Trans" and it failed. Thanks for your help. |
|
|
|
|
|
|
|
|
Distributed Transaction Error