Linked Server - Windows Server 2003 and MSDTC Issue

Question

Linked Server - Windows Server 2003 and MSDTC Issue

DBADave

SSChampion

Points: 10962
More actions
March 3, 2006 at 11:23 am

#168602

I'm receiving the following error:
Server: Msg 7391, Level 16, State 1, Line 2
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. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
The transaction active in this session has been committed or aborted by another session.
I've read the information at the following links:
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
http://support.microsoft.com/?id=817064
Neither worked.
My configuration is Windows Server 2003 SP1 and SQL Server 2000 SE SP3a.
I was able to get the linked server statement to work if I select "No Authentication Required" under "Transaction Manager Communication" within MSTDC of Component Services. However the "No Authentication Required" scares me a bit.
Here's my code.
CREATE TABLE [dbo].[Email] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Originator] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date Received] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Recipients] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CC List] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BCC List] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subject] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Unread] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Attachments] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Message ID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Insert Into Email
Exec SQL6.master.dbo.xp_readmail @peek='true', @suppress_attach='true'
GO
Any thougths?
Thanks, Dave

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

lholman SSCrazy Points: 2810 More actions · Answer 1

Dave,

What is your (target) linked server, is that a Windows 2003 server as well? I had this problem some time back and then made the following changes within the 'Security Configuration' pane in the MSDTC Properties of 'Component Services' on the linked server.

Also have you checked/tried the following?

Check that the 'Distributed Transaction Coordinator' service is running on both servers?
Have you checked your SQL errorlog and Windows evenlogs for DTC errors?
Checked that the service account for your source server has a corresponding login and user permissions to execute the sproc on the linked server?
Run a basic select query for your insert to rule out permissions issues on the sproc?

ll

DBADave SSChampion Points: 10962 More actions · Answer 2

For whatever reason I cannot see your reply between the comments

"...of 'Component Services' on the linked server." and "Also have you checked/tried..." so I don't know what changes you made to the configuration.

The source server is Windows Server 2003 and the destination server is Windows 2000. When I go from Windows Server 2003 to another Windows Server 2003 the command works.

I can also execute the following command without problem.

EXEC @status = SQL6.master.dbo.xp_readmail @peek='true', @suppress_attach='true', @subject = @msgsubject OUTPUT

The problem appears to be related to the Insert statement and a linked server on 2003 connecting to Windows Server 2000.

Dave

Ian Yates SSCoach Points: 19738 More actions · Answer 3

Are both SQL Servers running SP4?

Check permissions - are you using windows auth or SQL Server auth for the linked server? As suggested, try a simple select without any inserts and stored proc executes, etc.

Does the linked server login you are using have access to the database on the linked server? Perhaps try configured the linked server login to use the other server's SA (temporarily of course) to see if permissions are an issue.

DBADave SSChampion Points: 10962 More actions · Answer 4

I'm not sure why you think it's permissions. When I run the following statement it works.

EXEC @status = SQL6.master.dbo.xp_readmail @peek='true', @suppress_attach='true', @subject = @msgsubject OUTPUT

The error occurs while trying to insert the results into a table.

Both servers are SP3a and the linked server is connecting as SA.

Ronald San Juan SSCrazy Points: 2937 More actions · Answer 5

Any resolution to this one yet?

Ronald San Juan | SQL DBA
ID 710124 ~ Code "Northwind"

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 6

The DTC authentication thing is new from windows 2003/XP SP2. So no authentication is required if you have to support windows 2000,NT4...

see http://support.microsoft.com/kb/839279

RichB SSCrazy Eights Points: 9651 More actions · Answer 7

i just spent a few days banging my head against the same thing. Got the MSDTC running nicely in component services, fiddled with this that and the other. Turned out it was getting nailed by our corp firewall. Opened up the relevant ports and it works a treat now.

Also confirm sp_configure 'remote access' is 1...