asked on

Linked Server in Sql server

I have two instances of Sql server - Sql 2005 and Sql 2000. Sql 2005 is configured to work under Windows authentication running under Domain account.
and Sql2000 is configured for windows and Sql authentication and running under system account.

I have configured sql2000 as linked server under sql 2005 instance. I am able to run the stored procedure stored in sql 2000 instance. but when i try to insert locally from linked server procedure i am getting the following error.

OLE DB provider "SQLNCLI" for linked server "blrds" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "blrds" was unable to begin a distributed transaction.

Can some expert help me to overcome this error

Thanks in advance

kaminda

I think this will help you to sort out your problem

http://support.microsoft.com/kb/306212

k_murli_krishna

Please go through these:

The partner transaction manager has disabled its support for remote/network transactions
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/7172223f-acbe-4472-8cdf-feec80fd2e64

MSDTC Troubleshooting
http://www.sqlwebpedia.com/content/msdtc-troubleshooting

venkataramanaiahsr

ASKER

I followed the instructions mentioned in the link http://www.sqlwebpedia.com/content/msdtc-troubleshooting in the sql2005 server .

i am trying to run the qry from sql 2005 server qry analyzer to the linked server (sql2000 server) . Still the same problem . Do i have to do the same changes in Sql 2000 server also.

ASKER CERTIFIED SOLUTION

Darran Brown

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

venkataramanaiahsr

ASKER

Thanks Mr_B. Actually MSDTC change mentioned in the link http://www.sqlwebpedia.com/content/msdtc-troubleshooting to be done in both the servers. After doing these changes , my problem got resolved. I have given No authentication required at both ends under MSDTC security configuaration. Is it Ok or no authentication will cause any security issues?

Darran Brown

Hi
It's a bit hard to confirm without knowing your exact set up but ideally you want mutual Authentication.
If the msdtc security settings weren't set before we could say that nothing else is using them but if you have anything else on the servers apart from SQL you might want to double check. I'm also presuming there in the same domain.

venkataramanaiahsr

ASKER

Thanks MR_B .your solution worked. I am really sorry for not accepting the solution earlier as i had tied up with some other priority activities