SQL linked server error

Posted on 2007-07-26
Last Modified: 2008-08-31
I am using a linked server from a MS SQL Server 2000 server to a MS SQL Server 2005 server.
Now, I try to create a view through Enterprise Manager with the simple command:

SELECT * FROM  LinkedServerName.DBName.OwnerName.TableName

This works when I run it in the "New View" window (with the red exclamation mark), but as soon as I save the view, this error occurs:
ODCB error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoi JoinTransaction returned 0x8004d00a].

Can anyone enlighten me on this subject?

Thank you!
Question by:navipartner
    LVL 11

    Accepted Solution

    I believe that's a bug in SQL Server.

    To avoid it, you should run it in Query Analyzer as:

    CREATE VIEW yourView


    SELECT * FROM  LinkedServerName.DBName.OwnerName.TableName
    LVL 3

    Expert Comment

    Check the Component Service Settings if you are using WINDOWS 2003 and also if SQL2005 has service pack 1 Atleast. this should solve yourproblem as i has the same error earlier.
    LVL 14

    Expert Comment

    Is this fixed?

    If not, I wonder if the SQL 2000 OLEDB provider is compatible with 2005? You might try and link the server with the SQL Server ODBC driver instead.

    Author Comment


    Sorry for the late answer but I have been very busy the last few days.
    Thank you very much for all your input. Otanas solution worked perfectly and for twoboats I can now conclude that 2000 is compatible with 2005 (or maybe the other way around, I do not know, but it works :-)

    Thank you again.
    Best regards!

    Expert Comment

    Otana: Thanks

    Previously, I search google and there has so many solutions -
    Firewall - MS DTC etc..

    But your solution is very simple and good

    It work!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Read about achieving the basic levels of HRIS security in the workplace.
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now