Thursday, August 16, 2012

[SQLSTATE 42000] (Error 10060) OLE DB provider "SQLNCLI10" for linked server

[SQLSTATE 42000] (Error 10060)  OLE DB provider "SQLNCLI10" for linked server

Error Message
Executed as user: MyServer\Myaccount. TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. [SQLSTATE 42000] (Error 10060)  OLE DB provider "SQLNCLI10" for linked server "" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412)  OLE DB provider "SQLNCLI10" for linked server "MYSERVER" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". [SQLSTATE 01000] (Error 7412).  The step failed.
Open Component Services via

 Run then dcomcnfg

To resolve this problem, follow these steps:
  1. Click Start, point to All Programs, point to Administrative Tools, and then click Component Services.
  2. In the Component Services Wizard, expand Component Services, and then double-click Computers.
  3. Right-click My Computer, and then click Properties.
  4. Click the MS DTC tab, and then click Security Configuration.
  5. In the Security Configuration dialog box, click to select the Network DTC Access check box.
  6. Under Network DTC Access, click Network Transactions.

    Note If you installed Service Pack 1 for Windows Server 2003, you can click Allow Inbound and Allow Outbound.
  7. Make sure that DTC Logon Account is set to NT Authority\NetworkService.
  8. Click OK.
  9. In the message box, click Yes to continue.
  10. In the DTC Console Message dialog box, click OK.
  11. In the System Properties dialog box, click OK.
  12. Reboot the computer for these changes to take effect.

    Note In some cases, you must start the DTC service before you start the SQL Server service so that the linked server distributed queries work well.
  13. Add above DTC configuration on both server
  14. You need  add XACT_ABORT ON on your stored procedure

No comments: