ERROR!!! “A network-related or instance-specific error occurred while establishing a connection to SQL Server…”

Issue:

Sometimes you may get into issues while connecting to SQL Server with below error message

1

“A network-related error or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

 

Solution:

There could be several reasons for you getting this error.  Below are few things you need to check to troubleshoot and resolve this issue.

1. Make sure the server you are trying to connect is up and running.  Ping the Server IP (where SQL is installed) from the client machine.

ping

 

 

2. If the Server is up, then make sure the SQL Server is up and running. To check that, go to SQL Server configuration manager (Start > All Programs > SQL Server 2005/2008/R2> Configuration Tools > SQL Server Configuration Manager)

Click on SQL Server Serivices on the left hand side and ensure the SQL Server (MSSQLSERVER) Service is running.

 

2

 

 

3. Check that the SQL Browser service is running. If you have installed a SQL Server named instance and not configured a specific TCP/IP port, incoming requests will be listening on a dynamic port. To resolve this you will need to have the SQL Browser service enabled and running.

 

3

 

4.  Check for TCP/IP and Named Pipes protocols and port. Open SQL Server Configuration Manager and check the SQL Server Network Configuration protocols. You should enable Named Pipes (if it is applicable to your environment) and TCP/IP protocol.

 

4

 

5.  For the TCP/IP protocol, right click and select properties to check the TCP/IP communication port as well. The default port is 1433, which can be changed for security purposes if needed.

 

5

 

6.  Try to telnet the SQL Server from Client machine with SQL port. Ensure that SQL port is open in Windows Firewall.

 

7

 

7. Make sure Remote connection is allowed in SQL Server. Open SSMS, connect to server, right-click server and select Properties. Click the Connections node and then Under Remote server connections, ensure Allow remote connections to this server is checked

 

6

 

 

Hope this was helpful !!!

Troubleshooting a Failed Maintenance Plan DBCC

Problem:

I recently ran into an odd little problem with a Maintenance Plan that I would like to share. I had a plan that was running the Check Database Integrity Task that suddenly started failing. Nothing about the databases had changed and when i ran DBCC CHECKDB manually it completed successfully. The job history showed a meaningless, truncated message:


 

Job History:

Executing query “DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp…”.: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:14:30 AM Finished: Elapsed: 1.185 seconds. The package execution failed. The step failed.

Maintenance Plan Error History:

Alter failed for Server “ServerNAme”


 

 

Error

 

Solution: 

At some point, the Allow Updates configuration option was set to 1. In SQL Server 2000 and older, this option allowed users to make updates directly to the system tables. In SQL Server 2005 and newer, the system tables are gone, replaced with the Resource Database and system views. This option is no longer supported in SQL Server 2005 and beyond, and though you can set Allow Updates to 1 with no error, as soon as you run RECONFIGURE, you will receive the error that ad hoc updates are not supported. Because Allow Updates had been set to 1 and my Maintenance Plan runs the RECONGIFURE statement, this error was thrown and the Maintenance Plan failed.

All I had to do was run the below command to set Allow Updates back to 0 and then everything started working again.

 

Command:

sp_configure  ‘Allow Updates’, 0

Reconfigure with override

 

ErrorResolved

 

Linked Server Error–remote proc transaction promotion

Problem:  

I have two SQL Servers Server A and Server B. Both have SQL Server 2008. I needed to fetch data from server B to server A and insert it into a table on server A.  For this purpose, I had created a stored proc on server B and for calling that SP I have added server B as linked server on server A.  When I executed this SP from server A using server B, it returns data without any issue.

However when I try to insert this data to a local table on server A, I am getting following error:

OLE DB provider “SQLNCLI10” for linked server “LinkedServerName” returned message “The transaction manager has disabled its support for remote/network transactions.”.
Msg 7391, Level 16, State 2, Server <ServerName, Procedure <SP Name>, Line 47
The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “LinkedServerName” was unable to begin a distributed transaction.

 

Solution:

Resolved the issue by changing the linked server property with below command.

EXEC sp_serveroption @server = ‘ServerB’,@optname = ‘remote proc transaction promotion’, @optvalue = ‘false’ ;