How to Troubleshoot SQL Server Connection Problems

Jun 2nd

Posted by Brumedishetty in SQL Server

No comments

In this blog we shall take a look at the steps to troubleshoot connection problems in SQL Sever. We need to keep in mind, there are lot of things that might be part of such an issue and also we need to consider which user or what application is reporting this connection problems. The exact cause of the connection failure can only be troubleshooted by testing and trying multiple remedial steps, eliminate that step and keep moving forward.

 

  • Is SQL Server currently running or SQL Server accepting new connections?
    • Make sure that SQL Server on that machine is currently running, before making any other attempts / trials. SQL Server status should be checked that it is not stopped  or paused ( of course the error message indicates if the SQL Server is not accepting any new connections). Either way see that SQL Server is running currently. Start SQL Server if needed from Configuration Manager or Services etc.
  • Are the required Protocols Enabled?
    • Ensure that all required protocols are enabled on the SQL Server. There is nothing wrong with enabling all protocols, but a word of caution about the VIA protocol, it should be disabled on most of the systems (depends on Server Hardware) and should be enabled only upon verifying with the Hardware Vendor.
  • Check the SQL Server Authentication Mode.
    • This step is also important, any connection coming to SQL Server either has to be a Windows User or a SQL Server user. If you are trying to connect using a SQL Server login, make sure the authentication mode on the SQL is Mixed Mode, else the login attemps will fail and you will not be able to connect to SQL Server.
  • Is the User / Login status active or enabled?
    • Make sure you are connecting to the SQL Server using an account which is not disabled. If a different user complains about connection issues (different credentials), make sure that account is not disabled in SQL Server (if SQL Login) or the Windows Active Directory account is not expired / locked.
  • Check the Firewall settings.
    • Make sure that the firewall is not blocking the port on which SQL Server listens. 
  • Check the SQL connection information.
    •  You need to check if the SQL Server being referred in the connection string is a default instance or a named instance. If the instance is a named instance, you will need to ensure that the connection string includes the instance information also, i.e, it has to be something like the IP Address,Port # such as 192.168.10.123,3456 or the Servername\Instance such as PhysicalServer\InstanceName.

Comments / feedback will be highly appreciated.

– Bru Medishetty

, ,

Dropping a Column from a table using T-SQL – SQL Server

May 30th

Posted by Brumedishetty in SQL Server

No comments

This blog explains how to drop a column from a table using T-SQL query. It might be easy to do it from SSMS, on the other side, it is not always convenient to do it from SSMS. When you have to repeat the task of dropping additional columns at a later time, you will have to go back to the object explorer repeatedly and choose those column to be dropped, where as using T-SQL query you just need to change the column name to be dropped. Now let’s look at the script to do it.

ALTER TABLE EmployeeTable
DROP COLUMN Gender 
GO 

 

The Syntax to drop a table column is quite simple, it goes this way “ALTER TABLE YOURTABLENAME DROP COLUMN YOURCOLUMNNAME“.

To use the code in this example, first you will need to create the table EmployeeTable using the script in another blog Creating Primary Key in a SQL Server table

Comments / feedback will be highly appreciated.

–Bru Medishetty

, ,

Creating Primary Key in a SQL Server table

May 24th

Posted by Brumedishetty in SQL Server

3 comments

In this blog we shall learn how to create a Primary Key on a table using T-SQL.

A Primary Key on a table can be either created at the time of creating the table or after the table is created by altering the table and adding the Primary Key Constraint.  This blog covers the first method of creating the table and Primary Key at the same time. The important change in the Create table script is to include a section inside the table schema definition by mentioning the keyword CONTRAINT followed by the Primary Key Name followed by the Ket words PRIMARY KEY.   

CREATE TABLE EmployeeTable
(
   EmployeeID INT , 
   FName VARCHAR (25), 
   LName VARCHAR (25), 
   Gender VARCHAR (1), 

   CONSTRAINT [PK_EMP_EMPID] PRIMARY KEY 

   (


      EmployeeID ASC 
   )
)ON [PRIMARY] 
GO 

Some points to remember about Primary Keys

  • A table can consist of one and only one Primary Key.
  • By default a clustered Primary Key is created.
  • A Primary Key on a table can be based on one or more columns of that table.
  • A Primary Key ensure no duplicate values can exist in that column(s).
  • A Primary Key on one column can be used as a parent key on another table and helps in ensuring referential integrity between those tables.
  • A Primary Key is required when creating a Transactional Publication in Replication.
 
 You can download the scripts used in this blog Here
, ,
«12345»1020...Last »