Distributed transaction problem

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)

Distributed transaction problem

Author

Topic

Kristen
Test

22859 Posts

Posted - 2005-09-01 : 15:44:23

I have a simple SProc that does something like


SELECT *
INTO	##TEMP_MyTable
FROM	OPENQUERY(ORACLE_SERVER, 
		'SELECT *
		FROM MyOracleTable
		ORDER BY MyColumn'
	)

When I run the SProc it populates the ##TEMP table

However, when I do


BEGIN TRANSACTION
    EXEC dbo.MySProc
[b]COMMIT/b]

I get

Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].

Is there some way to solve this (easily!)?

I just want to have transaction on SQL Server, that I can roll back. I don't care at all about the SELECT on to Oracle

Thanks

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-01 : 16:44:11

I got 7391 too doing similar stuff.
Never solved it

.
Sorry.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-01 : 16:48:07

First thought - make the remote call into a local #temp table outside your transaction, then from within your transaction insert to ##temp from #temp.

EDIT:
or is the objective to be able make a distributed transaction?

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2005-09-01 : 17:24:21

"or is the objective to be able make a distributed transaction?"

I'd like to avoid that really,. There is no concurrency issue with the Oracle DB, and I'm not doing any Updates on the Oracle DB - so no locks etc. required, and no atomicity with Oracle.

BUT ... Once I've pulled the data into SQL Server I would like to have a transaction around the subsequent updates (all within SQL Server).

Perhaps I should do

CREATE PROCEDURE usp_MyWrapper
AS
Get data from Oracle
EXEC usp_DoTheWork
GO

CREATE PROCEDURE usp_DoTheWork
AS
BEGIN TRANSACTION
UPDATE 1
UPDATE 2
...
COMMIT
GO

Maybe that will work and not be seen as a distributed transaction?

Kristen

tcuster
Starting Member

1 Post

Posted - 2005-09-14 : 16:23:52

If you commit the transaction before executing the linkedServer query, you'll be released. This is just a hack, but it may be a last resolve.

COMMIT TRANSACTION
select * from OPENQUERY(LINKEDSERVERNAME, '{call ProcedureName}');

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-14 : 16:40:37

Your SELECTs shouldn't be inside a transaction anyway. So your proposed solution of usp_DoTheWork is what you want.

Tara

Kristen
Test

22859 Posts

Posted - 2005-09-15 : 02:19:30

Thanks Tara. My example was a bit "canned" though.

Basically I ahd an SProc that does


CREATE PROCEDURE usp_MySProc
AS
SELECT *
INTO	##TEMP_MyTable
FROM	OPENQUERY(ORACLE_SERVER, 
		'SELECT *
		FROM MyOracleTable
		ORDER BY MyColumn'
	)

UPDATE U
SET MyColumn = TempColumn
FROM MyLocalTable U
    JOIN ##TEMP_MyTable
        MyPK = TempPK
GO

and what I was trying to do to test the SProc, without it actually doing anything which might update the data incorrectly, was:

BEGIN TRANSACTION
EXEC usp_MySProc
SELECT SomeStuffToCheckItWorked
ROLLBACK

and that's where I was falling foul of the distributed transaction.

I just stuck a transaction around the UPDATE within the Sproc, and put my SELECT SomeStuffToCheckItWorked in my SProc - but that type of way of working always bothers me that I'm testing a "hybrid" rather than the actual thing, and it can therefore behave differently when converted to the "real code".

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources