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 |
|
nishithrn
Yak Posting Veteran
58 Posts |
 Posted - 2004-07-28 : 06:34:00
|
| Hello All,I have two boxes of sql server, server A and server B. I am running and insert statement from server A on server B. (server B is configured as linked sevrr on server A). The insert statement runs implicitly. But when I run the same insert statement explicitly:begin transelect @@trancountinsert into [SYNDEV-MUM3].Umesh.dbo.test1values (5,'Shalit')--rollback--commitI get the following error:Server: Msg 7395, Level 16, State 2, Line 1Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A nested transaction was required because the XACT_ABORT option was set to OFF.[OLE/DB provider returned message: Cannot start more transactions on this session.]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionLocal::StartTransaction returned 0x8004d013: ISOLEVEL=4096].As stated in the above error, I use the SET XCAT_ABORT ON before begin tran:SET XCAT_ABORT ON begin transelect @@trancountinsert into [SYNDEV-MUM3].Umesh.dbo.test1values (5,'Shalit')--rollback--commitHere, the query goes in a loop, with no reply.Can somebody please help in this regard..???Thanks in advance....Nishith |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-28 : 08:36:15
|
| Have you tried BEGIN DISTRIBUTED TRANSACTION?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-28 : 08:36:15
|
| Have you tried BEGIN DISTRIBUTED TRANSACTION?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
nishithrn
Yak Posting Veteran
58 Posts |
Posted - 2004-07-29 : 01:36:21
|
| Yes, I have used BEGIN DISTRIBUTED TRANSACTION, but to no success... |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-29 : 08:17:27
|
| Are you running this in Query Analyzer?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
nishithrn
Yak Posting Veteran
58 Posts |
Posted - 2004-07-29 : 08:47:43
|
| Yes ... |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-29 : 08:55:57
|
| Put a GO after your SET XACT_ABORT ON statement. What error did you get when you used the BEGIN DISTRIBUTED TRANSACTION? You do have DTC running on both servers, right? Is this Windows 2000 or 2003?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
nishithrn
Yak Posting Veteran
58 Posts |
Posted - 2004-07-30 : 00:38:29
|
| Tried as u mentioned, but with no success. Also, both the boxes are in Windows 2000 with SQL 2000 as the database. Hope this helps...Tx ... |
|
|
|
|
|
|
|
|
XCAT_ABORT