Very Common SQL Server Errors And Resolutions Part 2

In this article I would like to list out most common errors and their solutions that occur in SQL Server. This article will also you tell how to reproduce them.

Error: 1
Msg 537, Level 16, State 2, Line 2
Invalid length parameter passed to the LEFT or SUBSTRING function.
Or
Msg 536, Level 16, State 1, Line 2
Invalid length parameter passed to the left function.

Cause:
You will get this error when you try to select some string based on negative value of length parameter in LEFT or SUBSTRING functions.

How to re-produce:
DECLARE @inPut VARCHAR(30) = 'VIJAYA KADIYALA'
SELECT LEFT(@inPut, CHARINDEX('Z', @inPut) - 1)
Or
DECLARE @inPut VARCHAR(30) = 'VIJAYA KADIYALA'
SELECT LEFT(@inPut, -10)

Solution:
Make use SIGN user defined function (UDF) to convert the sign to suppress the error.

DECLARE @inPut VARCHAR(30) = 'VIJAYA KADIYALA'
SELECT LEFT(@inPut,(case SIGN(CHARINDEX('Z', @inPut)) WHEN -1 THEN -1 else 1 end ) - 1)


Error:2
Msg 245, Level 16, State 1, Line 13
Conversion failed when converting the varchar value ',' to data type int.

Cause:
You will get this error when you try to convert INT columns to string or VARCHAR.
Another reason could be doing some operations with INT column data type to look like string or VARCHAR.

How to re-produce:
SELECT 1 + ',' + 2 + ',' + 3 AS COL

Solution:

Use CAST or CONVERT function to convert INT data type values into VARCHAR

SELECT (CAST(1 AS VARCHAR(10)) + ',' + CAST(2 AS VARCHAR(10)) + ',' + CAST(3 AS VARCHAR(10))) as col


Error:3
Msg 8107, Level 16, State 1, Line 1
IDENTITY_INSERT is already ON for table Table_name. Cannot perform SET operation for table Table_Name.

Cause:
At any point in time you can have only one set whose IDENTITY_INSERT ON.
You will get this error when you try to set IDENTITY_INSERT ON for a second table when IDENITY_INSERT is already on for the First Table.

How to re-produce:
create table t1(T1_ID INT IDENTITY(1,1))
SET IDENTITY_INSERT T1 ON

create table t2(T2_ID INT IDENTITY(1,1))
SET IDENTITY_INSERT T2 ON


Solution:
When you are using IDENTITY_INSERT then always use this in batch mode.

SET IDENTITY_INSERT T1 ON
GO
-- do you work
GO
SET IDENTITY_INSERT T1 OFF
GO


Error:4
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'T1' that match the referencing column list in the foreign key 'FK_T2'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Cause:
You are trying to create a foreign key column and referencing a column in parent table where there is no primary key or unique key defined.

How to re-produce:
create table t1(T1_ID INT IDENTITY(1,1))

create table t2(T2_ID INT IDENTITY(1,1))

ALTER TABLE T2 ADD CONSTRAINT FK_T2 FOREIGN KEY(T2_ID) REFERENCES T1(T1_ID)

Solution:
Make sure you have Primary key or unique key defined on the column that you are going to reference in Foreign Key Constraint.

Error: 5
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'T2', because it does not exist or you do not have permission.

Cause:
You are trying to drop a table where you are not the owner of the table or table doesn’t exist in your schema or database.

How to re-produce:
DROP TABLE T2

Solution:
Before you perform any DDL operation make sure you have the permissions on the object.