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

SQL linked server error

Hi,
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!
0
navipartner
Asked:
navipartner
1 Solution
 
OtanaCommented:
I believe that's a bug in SQL Server.

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

CREATE VIEW yourView

AS

SELECT * FROM  LinkedServerName.DBName.OwnerName.TableName
0
 
abhijit_kCommented:
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.
0
 
twoboatsCommented:
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.
0
 
navipartnerAuthor Commented:
Hi,

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!
0
 
tinwinaungCommented:
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!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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