• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 892
  • Last Modified:

SQL linked server error

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!
1 Solution
I believe that's a bug in SQL Server.

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



SELECT * FROM  LinkedServerName.DBName.OwnerName.TableName
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.
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.
navipartnerAuthor Commented:

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!
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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now