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

SQL Server 2005 Error:
“A network-related 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. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) ”

Things to check:
1. Make sure your database engine is configured to accept remote connections
• Start > All Programs > SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration
• Click on Surface Area Configuration for Services and Connections
• Select the instance that is having a problem > Database Engine > Remote Connections
• Enable local and remote connections
• Restart instance

2. Check the SQL Server service account
• If you are not using a domain account as a service account (for example if you are using NETWORK SERVICE), you may want to switch this first before proceeding

3. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings in your ASweb P.NET application
• Usually the format needed to specify the database server is machinenameinstancename
• Check your connection string as well

<connectionStrings>

<add name=”SampleConnectionString” connectionString=”Data Source=machinenameinstancename;Initial Catalog=AdventureWorks;Integrated Security=SSPI;Min Pool Size=5;Max Pool Size=60;Connect Timeout=30″ providerName=”System.Data.SqlClient”/>

</connectionStrings>

4.You may need to create an exception on the firewall for the SQL Server instance and port you are using
• Start > Run > Firewall.cpl
• Click on exceptions tab
• Add the sqlservr.exe (typically located in C:Program Files (x86)Microsoft SQL ServerMSSQL.xMSSQLBinn), and port (default is 1433)
• Check your connection string as well

5. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings

6. Check SQLBrowser; check that it is running. You may also need to create an exception in your firewall for SQLBrowser.

7. Check that you have connectivity to the SQL Server. Note what you are using to connect: machine name, domain name or IP address? Use this when checking connectivity. For example if you are using myserver
• Start > Run > cmd
•netstat -ano| findstr 1433
•telnet myserver 1433
•ping -a myserver

Check what ports are IP addresses are being returned.

Alternative:
If you still can’t get any connection, you may want to create a SQL account on the server, a corresponding SQL user on the database in question, and just use this username/password combo in your web application.

Connection to target machine could not be made in a timely fashion

Problem

Actually the error is (note spelling mistake on fashion):

“Connection to target machine could not be made in a timely fasion”

Scenario

One of our DBAs was testing the SQL cluster failover. After he failed over from Node 1 to Node 2, he can no longer bring up SQL Server Configuration Manager (SSCM). Whenever he tried, he will wait for about 30-40 seconds before he gets an error message : Connection to target machine could not be made in a timely fasion

Continue reading

View or function ‘sys.dm_exec_sessions’ has more column names …

Problem

Agent message code 4502. View or function ‘sys.dm_exec_sessions’ has more column names specified than columns defined.

Scenario

Our replication on our clustered database just started to fail. We were getting the following error:

Agent message code 4502. View or function ‘sys.dm_exec_sessions’ has more column names specified than columns defined.

Ok, this is a weird message. How can sys.dm_exec_sessions have more columns that specified?

Continue reading

Drop and Re-Add Server Registration When Renaming Computer

Problem:

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, ‘SQLVMSQL02’. (Replication.Utilities)

Scenario:

1. We configured database mirroring on 2 SQL Server instances.

2. We changed the computer name from SQLVM to SQL2K5.

3. We tried launching the Database Mirroring monitor, but we got the following error:

SQL Server replication requires the actual server name to make a connection
to the server. Connections through a server alias, IP address, or any other
alternate name are not supported. Specify the actual server name, 'SQLVMSQL02'.
(Replication.Utilities)

If you just look at the error, it sounds a bit misleading because we don’t have replication configured in either of the servers.

Continue reading

What to do if you accidentally lock yourself out of SQL Server (SINGLE_USER mode)?

Problem:

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database <your db name> cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.”

Continue reading

White Papers: Backup/Recovery, Performance Tuning

Here are some older Quest White Papers (specific to SQL Server 2000, but general concepts and practices still applicable to SQL Server 2005/2008):

 

Backup/Recovery

SQL Server Backup Recovery & Troubleshooting. Part 1
SQL Server Backup Recovery & Troubleshooting. Part 2

 

Performance Tuning

Part 1: Analyzing and Optimizing T-SQL Query Performance on MS SQL Server using SET and DBCC
Part 2: Index Tuning Strategies
Part 3: Query Optimization Strategies
Part 4. Analyzing and Optimizing T-SQL Query Performance on Microsoft SQL Server using SHOWPLAN Output and Analysis

Fixing Collation: The Experiments

We’re trying to solve a few collation issues we have been having, and I had collation overload this week.

The Scenario
•    Some of our SQL Server instanced were installed using the default collation SQL_Latin1_General_CP1_CI_AS
•    We need to have case insensitive, accent insensitive – Latin1_General_CI_AI
•    We changed some of our database collations to Latin1_General_CI_AI

The Problems

•    We want to change the model database’s collation, so that all new databases get the collation we want
•    We want to change the tempdb collation, so that all temporary objects don’t have to use explicit collation

We use temporary tables in some of our important stored procedures and UDFs. It will be an issue if we have different collations between our user databases and tempdb. Yup, trying to do a query that involved these two tables will result in an error similar to:

Cannot resolve the collation conflict between “Latin1_General_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation. (Microsoft SQL Server, Error: 468)

Background Check Continue reading

Using DDL Triggers

Sample script below:

   1: -- ==========================================================================
   2: -- Object       : DDLTrigger.sql
   3: -- Object Type  : Script
   4: -- Description  : Various examples
   5: -- Notes        : 
   6: -- ==========================================================================
   7:
   8: -- DDL Triggers
   9: -- ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d79e5725-adea-4934-9474-9cd975adb6d8.htm
  10:
  11: -- DDL Trigger Events
  12: -- http://msdn.microsoft.com/en-us/library/ms189871.aspx
  13:
  14: -- EVENTDATA
  15: -- ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/03a80e63-6f37-4b49-bf13-dc35cfe46c44.htm
  16: -- EVENTDATA function returns XML data which contains the information about server 
  17: -- or database events. 
  18:
  19: -- sample EVENTDATA()
  20: /*
  21: <EVENT_INSTANCE>
  22:   <EventType>CREATE_TABLE</EventType>
  23:   <PostTime>2008-10-05T01:23:12.030</PostTime>
  24:   <SPID>55</SPID>
  25:   <ServerName>JUBILEESQL01</ServerName>
  26:   <LoginName>JUBILEEAdministrator</LoginName>
  27:   <UserName>dbo</UserName>
  28:   <DatabaseName>AdventureWorks</DatabaseName>
  29:   <SchemaName>dbo</SchemaName>
  30:   <ObjectName>t</ObjectName>
  31:   <ObjectType>TABLE</ObjectType>
  32:   <TSQLCommand>
  33:     <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
  34:                 QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
  35:     <CommandText>CREATE TABLE t
  36:         (
  37:             id int
  38:         )
  39: </CommandText>
  40:   </TSQLCommand>
  41: </EVENT_INSTANCE>
  42:
  43:  */
  44:
  45: -- ---------------------------------------------------------------------
  46: -- database scope
  47: -- SSMS: Database > Programmability > Database Triggers
  48: -- ---------------------------------------------------------------------
  49: USE AdventureWorks
  50: GO
  51:
  52: IF EXISTS(
  53:   SELECT *
  54:     FROM sys.triggers
  55:    WHERE name = N'tr_db_ddl_NoCreateTable'
  56:      AND parent_class_desc = N'DATABASE'
  57: )
  58: BEGIN
  59:     DROP TRIGGER tr_db_ddl_NoCreateTable ON DATABASE
  60: END
  61: GO
  62:
  63: CREATE TRIGGER tr_db_ddl_NoCreateTable
  64: ON DATABASE -- current database
  65: FOR CREATE_TABLE, DROP_TABLE
  66: AS
  67: BEGIN
  68:     SELECT
  69:         'tr_db_ddl_NoCreateTable' 'Trigger',
  70:         'You are not permitted to perform selected action' 'Msg2',
  71:         EVENTDATA() 'EVENTDATA()'
  72:     ROLLBACK
  73: END
  74: GO
  75:
  76: -- test
  77: CREATE TABLE t
  78: (
  79:     id int
  80: )
  81: GO
  82:
  83:
  84: -- ---------------------------------------------------------------------
  85: -- server scope
  86: -- SSMS: Server Objects > Database Triggers
  87: -- ---------------------------------------------------------------------
  88: USE AdventureWorks
  89: GO
  90:
  91: IF EXISTS(
  92:    SELECT *
  93:    FROM sys.server_triggers
  94:    WHERE name = N'tr_srv_ddl_DisplayServerEvents'
  95: )
  96: BEGIN
  97:     DROP TRIGGER tr_srv_ddl_DisplayServerEvents ON ALL SERVER
  98: END
  99: GO
 100:
 101:
 102: CREATE TRIGGER tr_srv_ddl_DisplayServerEvents
 103: ON ALL SERVER
 104: FOR CREATE_DATABASE, CREATE_LOGIN
 105: AS
 106: BEGIN
 107:     SELECT
 108:         'tr_srv_ddl_DisplayServerEvents' 'Trigger',
 109:         'You are not permitted to perform selected action' 'Msg',
 110:         EVENTDATA() 'EVENTDATA()'
 111:     ROLLBACK
 112: END
 113: GO
 114:
 115: -- test
 116: CREATE DATABASE db
 117: GO

It is also interesting to note that creating a server-scoped trigger for DDL_SERVER_SECURITY_EVENTS

Msg 1082, Level 15, State 1, Procedure tr_srv_ddl_DisplayServerEvents, Line 11
“DROP_SERVER_ROLE_MEMBER” does not support synchronous trigger registration.

The trigger that fails follows:

   1: CREATE TRIGGER tr_srv_ddl_DisplayServerEvents
   2: ON ALL SERVER
   3: FOR DDL_SERVER_SECURITY_EVENTS
   4: AS
   5: BEGIN
   6:     SELECT
   7:         'tr_srv_ddl_DisplayServerEvents' 'Trigger',
   8:         'You are not permitted to perform selected action' 'Msg',
   9:         EVENTDATA() 'EVENTDATA()'
  10:     ROLLBACK
  11: END
  12: GO
  13:

Not sure why. Seems the DDL_SERVER_SECURITY_EVENTS should include only server-scoped events.

DDL Trigger Events

Will continue to dig in; when I find additional details I will post a follow up entry to this one.

Troubleshooting Database Mail

If you have configured Database Mail, and SQL Server still isn’t able to send emails out, here are a few things to check/do:

  1. Enable TCP/IP
    • Start > SQL Server Surface Area Configuration > Surface Area Configuration for Services and Connections
  2. Enable Mail Profile
    • Right click on SQL Server Agent > Properties > Alert System > Enable Mail  Profile
  3. Restart SQL Server Agent