transaction session comitted by another process

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

transaction session comitted by another process

Author

Topic

nitinag
Starting Member

20 Posts

Posted - 2003-09-10 : 20:35:18

I am have a stored proc which loops through a cursor and calls another proc passing the parameters
The other proc runs a stored proc on remote server
returns the results to a temp table and then proceeds
to open another cursor with this resultset and processes the data.

Seems to be running fine if I call 2nd proc directly but from first proc it returns this error.

The transaction active in this session has been committed or aborted by another session.

Server: Msg 7391, Level 16, State 1, Procedure web_LicTracker_Import_Order_Data, Line 46
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. ]

I am going crazy as I need to roll out tomorrow, Please help

Thanks in adavance

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-10 : 20:56:09

Without any code this is going to be extremely hard to help with, but I doubt it is ever going to work right as long as:

1. I am have a stored proc which loops through a cursor and calls another proc passing the parameters
2. The other proc runs a stored proc on remote server
3. returns the results to a temp table and then proceeds to open another cursor

I think if you wrote the data out longhand, gave it to a secretary to type it up on a manual typewriter, put it in an envelope and mail it to the North Pole, then had a paraplegic dog sled team bring it back to you, it *MIGHT* be a little slower than the current process you're using. Every item highlighted above is going to cause problems. Anything you can do to eliminate any of them will go a long way towards solving your problems. Even copying all the data from the remote server to the local one and processing it there could improve performance tremendously.

It's easier to juggle three golf balls than 4 chainsaws, a bowling ball, and two glasses of water.

nitinag
Starting Member

20 Posts

Posted - 2003-09-10 : 21:52:14

Robo,
This is scheduled to run as a midnight job on server , I agree with you that it definitely needs performance improvement by taking into account all of the points mentioned by you but just to give you a idea

1 . Remote table has 15 million rows(and we have 4 tables) so I cannot replicate that data to this server and it needs to be called remotely
2. regarding point #2 , I have to call a remote proc otherwise I will not be able to use local joins/No Locks/Indexes etc
3. I am looking to get the job done by tomorrow(thats what happens when you have impossible deadlines)

I got the problem to the following 2 statements in second procedure,
I am not using any Transactions at all , The exec statement just returns the select statements which I am trying to insert in a local table. Any ideas ???
thanks

on executing these in query analyzer it gives out the error, even after hardcoding the values

INSERT INTO Web_LicTracker_Import_Orders_TEMP(OrderNo,Line,edp, mfrName,productname,ship_date,quantity,PoNumber)
EXEC DMMART02.ISB.dbo.Web_LicenseTracker_Order_History 3109077, '01/01/2000','01/01/2005','MicroSoft'

Server: Msg 7391, Level 16, State 1, Line 1
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. ]

I kind of got to the problem

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-10 : 22:07:01

First thing is to make sure the MS DTC (Distributed Transaction Coordinator) service is running on both servers. Unfortunately I don't know a lot about remote servers since I use linked servers for these kinds of things, but that might be the most immediate solution.

The next suggestion I'd make is to try to set up the remote server as a linked server. This may improve performance, it may not, but it should at least allow a distributed transaction to start in case the remote server does not. If you do get the linked servers set up, just don't write any joins that cross servers (use OPENQUERY to get results from the linked server and work with the results locally)

I don't suppose there is a way of pushing the data to the remote server and having it run the remote procedure there, then return ALL of its results in one shot? I'm guessing you can't add or change any sproc's on the remote server, but if you can that might be the way to go.

HTH

nitinag
Starting Member

20 Posts

Posted - 2003-09-10 : 22:38:29

This is Linked server only(thats how I am calling) it is probably using DTC internally

I am stumped on the fact that if I just run the remote proc , it works and return results normally , however if I try to insert results in local table it comes out with this error

This works and return results

EXEC DMMART02.ISB.dbo.Web_LicenseTracker_Order_History 3109077, '01/01/2000','01/01/2005','MicroSoft'

This gives the distributed transaction error

INSERT INTO Web_LicTracker_Import_Orders_TEMP(OrderNo,Line,edp, mfrName,productname,ship_date,quantity,PoNumber)
EXEC DMMART02.ISB.dbo.Web_LicenseTracker_Order_History 3109077, '01/01/2000','01/01/2005','MicroSoft'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-10 : 22:44:51

Sure DTC is running on both servers? The problem is that the INSERT operation is an atomic transaction, but since it's crossing servers it has to be a distributed transaction.

nitinag
Starting Member

20 Posts

Posted - 2003-09-10 : 23:37:44

yes , I checked both servers under support services in enterprise manager and DTC seem to be running in both. BTW is there a sql command to check it too ??

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-11 : 05:23:22

Maybe worth to try a slightly different approach?

insert into t ...

exec opendatasource('SQLOLEDB','Data Source=RemServer;
User ID=sa;Password=pwd').remDB.dbo.spYourRemProc ...

nitinag
Starting Member

20 Posts

Posted - 2003-09-11 : 11:36:19

getting same error , may be I should try after turning off IMPLICIT transaction for these 2 lines

Server: Msg 7391, Level 16, State 1, Line 1
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. ]

nitinag
Starting Member

20 Posts

Posted - 2003-09-11 : 12:10:25

doesn't work even after turning ON/OFF IMPLICIT Transaction

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-09-12 : 10:56:11

If you are writing the query on a SQL2000 Server, and it is calling a SQL 7.0 Server, this can happen. It has happened to me, when I have written the original sp from my laptops QA, to the Server. When I terminal Serviced into the 7.0 Server, and ran the create procedure from there it worked fine.

Does any of the above sound familiar?

TSQLMan

quote:
Originally posted by nitinag

I am have a stored proc which loops through a cursor and calls another proc passing the parameters
The other proc runs a stored proc on remote server
returns the results to a temp table and then proceeds
to open another cursor with this resultset and processes the data.

Seems to be running fine if I call 2nd proc directly but from first proc it returns this error.

The transaction active in this session has been committed or aborted by another session.

Server: Msg 7391, Level 16, State 1, Procedure web_LicTracker_Import_Order_Data, Line 46
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. ]

I am going crazy as I need to roll out tomorrow, Please help

Thanks in adavance

panoslondon1
Starting Member

2 Posts

Posted - 2006-05-31 : 12:19:32

It may be too late!! but I had the same problem and found this article...

http://support.microsoft.com/default.aspx?scid=kb;en-us;329332

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources