Post reply

Linked Server - Windows Server 2003 and MSDTC Issue

  • 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

     

  • March 6, 2006 at 7:45 am

    #624875

    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

     

  • March 6, 2006 at 11:10 am

    #624975

    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

  • March 6, 2006 at 6:34 pm

    #625085

    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.

  • March 6, 2006 at 8:22 pm

    #625100

    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.

  • February 7, 2007 at 10:12 am

    #688335

    Any resolution to this one yet?

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

  • February 8, 2007 at 3:58 am

    #688504

    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

  • February 8, 2007 at 5:18 am

    #688511

    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...

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

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