Transactions involving Openrowset or Linked server failure

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

SQL Server Development (2000)

Transactions involving Openrowset or Linked server failure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-12 : 09:10:08

Henry Jartu writes "I have DML statements failing when used in within a BEGIN TRAN context and runs sucessfully outside of a BEGIN TRAN context. I checked several sites and no one seems to have an answer but many are experiencing this problem. The error reported is:
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [SQLSTATE 42000] (Error 7391) [SQLSTATE 01000] (Error 7312). The step failed."

izaltsman
A custom title

1139 Posts

Posted - 2002-02-12 : 10:14:31

Just to throw a few ideas at you:

-Make sure MSDTC is started.
-Try using BEGIN DISTRIBUTED TRANSACTION instead of you vanilla BEGIN TRAN

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-02-12 : 10:32:06

I've found that commands that involve more than one transaction have to be run using dynamic SQL to get them to work with linked servers.

Nazim
A custom title

1408 Posts

Posted - 2002-02-12 : 10:38:25

I dont think so Scott.
its just that . you cant execute DDL Statments(including select into) and neither the READTEXT,WRITETEXT And UPDATETEXT with with linked servers .

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

Edited by - Nazim on 02/12/2002 10:45:46

hjartu
Starting Member

3 Posts

Posted - 2002-02-12 : 10:45:34

Works 30 % of the time, never consistent.
Works 100% of the time when explicit tran statements are commented off.

CREATE PROC ProcedureName
AS
SET NOCOUNT ON
BEGIN TRAN
INSERT DestinationTableName SELECT a.*, null, 1 FROM OPENROWSET('SQLOLEDB','ServerName';'UserName';'UserPassword', 'select * from dbName.dbo.TableName') AS a --'
IF @@ERROR <> 0
BEGIN
GOTO Rollback_Section
END
COMMIT TRAN
BEGIN TRAN
INSERT DestinationTableName SELECT a.*, null, 1 FROM OPENROWSET('SQLOLEDB','ServerName';'UserName';'UserPassword', 'select * from dbName.dbo.TableName') AS a --'
iF @@ERROR <> 0
BEGIN
GOTO Rollback_Section
END
COMMIT TRAN
GOTO Commit_Section

Rollback_Section:
ROLLBACK TRANSACTION
RETURN -9

Commit_Section:
RETURN -8
SET NOCOUNT OFF
GO

Nazim
A custom title

1408 Posts

Posted - 2002-02-12 : 10:50:15

If this procedure is used oftenly . why dont you create a linked server and use OPENQUERY Instead of OPENROWSET.

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

hjartu
Starting Member

3 Posts

Posted - 2002-02-12 : 10:55:27

Because of the reason for not using linked servers. Management of security and permissions when linked server is used by more than one application and references more than one DB with varying permissions.
In addition this problem occurs with linked servers as well.

Nazim
A custom title

1408 Posts

Posted - 2002-02-12 : 23:54:29

Isnt it possible when the stored procedure fails , the server you are accessing might be down? .

i would suggest you to run a the OPENROWSETQUERY immediatly seperately whenever the sp doesnt works. i have a feeling it might be coz of connection problem.

Follow ilya's suggestion and execute your query

quote:

Just to throw a few ideas at you:
-Make sure MSDTC is started.
-Try using BEGIN DISTRIBUTED TRANSACTION instead of you vanilla BEGIN TRAN

HTH

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

Jayzee
Starting Member

1 Post

Posted - 2002-02-13 : 00:47:56

Hi,

I had the similar problem when I tried to run the transactions between 2 servers. We were using dynamic IP's assignments between 2 servers which was the root cause because the name resolution procedure failed and the famous Error 7391 occurs.
I then(after a long search to our problem) assigned the static IP at both server and our problem is solved.
Also look at Q273027 article of Microsoft knowledge Base.
Also try to use Set XACT_Abort ON when you transaction containing other transactions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources