Day 28: Linked server errors

This post was published to Vinay Thakur – Sql Server DBA at 5:05:51 PM 11/25/2011

 

 

        Day 28: Linked server errors

Linked server is another good linking took where you can linked the server access the data from different server, linked server could be of any other RDBMS as well linked with OLEDB provider. Detail information about linked server is
here

For linked server there are some common issues are

1. Access/Privilege to access the server
   
2. Proper configuration
   
3. Linked server also requires MSDTC services to be started.
   
4. Check the firewall/security between two servers.
   

 

For sql server 2000

1. Distributed Query Support Using the OLE DB Provider for DB2, When you configured linked server for sql to DB2 and “Allow InProcess option not enabled” you may get below error
   

Msg 7302, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
Cannot create an instance of OLE DB provider “DB2” for linked server “xxx”.

http://msdn.microsoft.com/en-us/library/ee251875(BTS.10).aspx

1. Msg 7303 , Level 16, State 1
   

Could not initialize data source object of OLE DB provider ‘%ls’. %ls

Could be :

1. Verify that these parameters defined for the linked server, specified by executingsp_addlinkedserver, are correct for the given provider. Check the provider’s documentation for the appropriate values for these parameters.
   
2. Permission
   

   http://msdn.microsoft.com/en-us/library/aa226395(SQL.80).aspx
   

3. Server: Msg 7391, Level 16, State 1, Line 1
   The operation could not be performed because the OLE DB provider ‘SQLOLEDB’ was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider ‘SQLOLEDB’ ITransactionJoin::JoinTransaction returned 0x8004d00a].
   

When msdtc services are disabled or due to firewall.

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

1. Here are some good information about how to troubleshoot some of the linked server issues:
   

http://sqlserver2000.databases.aspfaq.com/how-do-i-prevent-linked-server-errors.html