linked server

[supernova122]

I use a trigger which I update a linked server and this is working very good in SQL 2000. But after I migrate the database to SQL 2005. The trigger is not working with linked server any more. I remember I need to start the distributed transaction coordinator in service manager. But I couldn't find anything similar to this in SQL 2005. Is it somewhere I need to start? I got an error message like following:

Msg 7391, Level 16, State 2, Procedure EAI_ADM_STUDENT_ins_upd_trg, Line 804

The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "10.2.0.119" was unable to begin a distributed transaction.

Please help. Thanks.

 

[supernova122]

Can anyone please help? I am stuck here. Thanks.

 

[ptheriault]

I believe you have to enable the OLE Automation in the surface area configuration tool.

- Paul
- Database performance looks fine, it must be the Network!

 

[supernova122]

Thanks, How do I do it?

 

[ptheriault]

from you start menu navigate to Microsoft SQL Server 2005 -> Configurataion tools. Run the SQL Server surface area config tool. Click on Surface Area Config Feartures. From there you will find a list of features that you might want to enable including OLE.

- Paul
- Database performance looks fine, it must be the Network!

 

[supernova122]

The OLE automation is started. But the problem is still there.

 

[ptheriault]

Here is a really good link that might be able to help you with this problem.

http://blogs.msdn.com/mab/archive/2005/12/30/508273.aspx

- Paul
- Database performance looks fine, it must be the Network!

 

[supernova122]

Sorry. I find the link mentioned is not helpful to me.

 

[ptheriault]

Have you enabled the Distributed transaction Coordinator on both servers?

- Paul
- Database performance looks fine, it must be the Network!

 

[supernova122]

Yes, I did.

 

[ptheriault]

have you tried to just query a table from a database on the link server? Have you tried another drive other than SQL Native client? (SQLNCLI) Is there are firewall between these servers?

- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

Are both server SQL 2005? If they are not then the problem is with the provider, SQLNCLI. you can't use that connect a sql 2005 server with a 2000 server.

- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

I found this link that might be helpful
It deals with new security of DTC on a win2003 server.

http://msdn.microsoft.com/library/d...html/2627a956-60b3-4d26-bc04-e0676ec97786.asp

- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

here are two more link thatI hope will help you.

http://support.microsoft.com/default.aspx?scid=kb;en-us;329332&Product=sql

http://www.ureader.com/message/4142664.aspx

- Paul
- Database performance looks fine, it must be the Network!

 

[supernova122]

Thanks a lot, Paul. I will read it and try it and let you know. Cheers.

 

[supernova122]

Sorry. I read it and tried it and still doesn't work.