Very Common SQL Server Errors And Resolutions Part 3

In this article I would like to list out most frequently occurred errors and their solutions. This article will also you tell how to reproduce them.

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

Cause:
This error comes into the picture when you are performing operations on CHAR column and trying to convert into INT.

How to re-produce:
SELECT 'VIJAY KADIYALA' + 2

Solution:
Explicitly convert the value 2 using CAST or CONVERT function
SELECT 'VIJAY KADIYALA' + CAST(2 AS VARCHAR(2))

Error: 2
Msg 141, Level 15, State 1, Line 4
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Cause:
This error comes into the picture when you are assigning all the column values from SELECT statement into Local variable don’t match.

How to re-produce:
DECLARE @FirstName VARCHAR(30)
DECLARE @LastName VARCHAR(30)

SELECT @FirstName = FIRST_NAME,@LastName = LAST_NAME,MIDDLE_NAME FROM EMPLOYEES
WHERE LAST_NAME = 'Kadiyala'

Solution:
Match the Local variables with the No.Of Column you ate retrieving from SELECT statement.


Error: 3
Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Cause:
This error comes into the picture when you are using the DISTINCT Clause in the SELECT statement and the column which appears in the ORDER BY doesn’t part of the SELECT clause.

How to re-produce:
SELECT DISTINCT FIRST_NAME,LAST_NAME FROM EMPLOYEES
ORDER BY MIDDLE_NAME

Solution:
When you are using DISTINCT clause make sure all the Order by Columns are also part of SELECT clause.

Error: 4
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

Cause:
This error comes into the picture when length of the value being inserted is longer than the length of the column.

How to re-produce:
INSERT INTO EMPLOYEES(FIRST_NAME) VALUES ('12345678901234567890123456789011')
The length of FIRST_NAME column is 30 Characters only.

Solution:
Make sure you pass the values based on the column Length.