Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Practice Test
Tip of the Day : SQL Server Tutorial - INSERT Statement 101
FAQ - SQL Server

FAQ - SQL Server 2005
Home > Frequently Asked Questions > SQL Server Error Messages
SQL Server Error Messages - Frequently Asked Questions

 1.  Invalid length parameter passed to the substring function.

Error Message:

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

Causes:

This error is caused by passing a negative value to the length parameter of the SUBSTRING, LEFT and RIGHT string functions.  This usually occurs in conjunction with the CHARINDEX function wherein the character being searched for in a string is not found and 1 is subtracted from the result of the CHARINDEX function. 

LEFT(@String, CHARINDEX(' ', @String) - 1)

If the character is not found in a string, a space in this example, the CHARINDEX function will return a value of 0.  Subtracting 1 to this will become -1 and using this as the length parameter in the SUBSTRING or LEFT functions will result to this error.

View Error Message Details and Solution / Workaround

To further illustrate, assume that we want to get the first name from a full name wherein the first name is separated from the last name by a space.  Here's how the query will look like:

DECLARE @FullName VARCHAR(50)
SET @FullName = 'John Doe'

SELECT LEFT(@FullName, CHARINDEX(' ', @FullName) - 1) AS [FirstName]

The result of this query will be John.  Using the same query but with the full name just containing a last name will yield an error:

DECLARE @FullName VARCHAR(50)
SET @FullName = 'McGyver'

SELECT LEFT(@FullName, CHARINDEX(' ', @FullName) - 1) AS [FirstName]

Solution/Workaround:

To avoid this error, always make sure that you pass a non-negative value to the length parameter of the SUBSTRING, LEFT and RIGHT functions.  If used in conjunction with the CHARINDEX function, you can use the NULLIF function to check if the character separator is found and return a NULL value if not found.  Here's how the query above that produces the error will look like:

DECLARE @FullName VARCHAR(50)
SET @FullName = 'McGyver'

SELECT LEFT(@FullName, NULLIF(CHARINDEX(' ', @FullName) - 1, -1)) AS [FirstName]

Since there's no space in the full name, a NULL value is returned as the first name.


 2.  Divide by zero error encountered.

Error Message:

Server: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Causes:

This error is caused by performing a division operation wherein the denominator or the divisor is 0.  This error is not encountered when the denominator or divisor is NULL because this will result to a NULL value.

View Error Message Details and Solution / Workaround

Solution/Workaround:

There are three ways to avoid the "Division by zero encountered" error in your SELECT statement and these are as follows:

  • CASE statement
  • NULLIF/ISNULL functions
  • SET ARITHABORT OFF and SET ANSI_WARNINGS OFF

Using the CASE statement, your query will look like the following:

SELECT CASE WHEN [Denominator] = 0 THEN 0 ELSE [Numerator] / [Denominator] END AS [Percentage]
FROM [Table1]

If the denominator or divisor is 0, the result becomes 0. Otherwise, the division operation is performed.

Using the NULLIF and ISNULL functions, your query will look like the following:

SELECT ISNULL([Numerator] / NULLIF([Denominator], 0), 0) AS [Percentage]
FROM [Table1]

What this does is change the denominator into NULL if it is zero.  Then in the division, any number divided by NULL results into NULL.  So if the denominator is 0, then the result of the division will be NULL.  Then to return a value of 0 instead of a NULL value, the ISNULL function is used.

Lastly, using the SET ARITHABORT and SET ANSI_WARNINGS, your query will look like the following:

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

SELECT [Numerator] / [Denominator]

With both ARITHABORT and ANSI_WARNINGS set to OFF, SQL Server will return a NULL value in a calculation involving a divide-by-zero error.  To return a 0 value instead of a NULL value, you can put the division operation inside an ISNULL function:

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

SELECT ISNULL([Numerator] / [Denominator], 0)

 3.  Syntax error converting the varchar value to a column of data type int.

Error Message:

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value to a column of data type int.

Causes:

There are many ways this error may be encountered but the common reason is that you are trying to convert, either implicitly or explicitly, a varchar value to an int data type and the varchar value cannot be converted to an int data type.  You may have a varchar column that may have integer values on most records but where some have non-integer values.

One other common reason why this is encountered is when creating a dynamic SQL statement and the query involves combining a varchar variable with an integer variable.

View Error Message Details and Solution / Workaround

DECLARE @SQL VARCHAR(2000)
DECLARE @ID INT

SET @ID = 124
SET @SQL = 'SELECT * FROM [dbo].[Customers] WHERE [ID] = ' + @ID

The reason why the error is encountered in this scenario is because an integer data type has a higher precedence over a varchar data type.  Since the integer data type has a higher precedence, the varchar data type is implicitly converted by SQL Server to an integer data type, and not the other way around as you would have assumed.

Solution/Workaround:

For the case of a varchar column that contains integer values but with a few non-integer values, you can use the ISNUMERIC function to determine if the column can be converted to an integer value or not.  To determine the rows where the column cannot be converted to an integer, you can do the following query:

SELECT * FROM [dbo].[Table1] WHERE ISNUMERIC([VarcharIntColumn]) = 0

For the case of the dynamic SQL wherein a varchar variable is concatenated with an integer variable, you have to explicitly convert the integer variable to a varchar data type using either the CAST or CONVERT function.

DECLARE @SQL VARCHAR(2000)
DECLARE @ID INT

SET @ID = 124
SET @SQL = 'SELECT * FROM [dbo].[Customers] WHERE [ID] = ' + CAST(@ID AS VARCHAR(10))

 4.  Insert Error: Column name or number of supplied values does not match table definition.

Error Message:

Server: Msg 213, Level 16, State 5, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

Causes:

This error occurs when doing an INSERT where the columns list is not specified and the values being inserted, either through the VALUES clause or through a SELECT subquery, are either more than or less than the columns in the table.

View Error Message Details and Solution / Workaround

To illustrate, here are examples on when the error can occur:

-- Sample #1: Using INSERT INTO ... VALUES
CREATE TABLE [dbo].[Customers] ( [ID] INT, [Name] VARCHAR(100))

INSERT INTO [dbo].[Customers]
VALUES (1, 'John', 'Doe')

-- Sample #2: Using INSERT INTO ... SELECT FROM
CREATE TABLE [dbo].[Client] ( [ID] INT, [Name] VARCHAR(100))

INSERT INTO [dbo].[Client]
SELECT [ID], [Name], [Address]
FROM [dbo].[NewClient]

Solution/Workaround:

To avoid this problem, make sure that the values specified in the VALUES clause or in the SELECT subquery match the number of columns in the INSERT clause.  In addition to this, you must specify the columns in the INSERT INTO clause.  Although the column list in the INSERT INTO statement is optional, it is recommended that it is always specified so that even if there are any modifications made on the table, either new columns are added or inserted in the middle of the table or columns are deleted, the INSERT statement will not generate this error.  (Of course, a different error message will be generated if a column is deleted from the table that is being referenced by the INSERT statement).

Given the samples above, here's how to avoid the error:

-- Sample #1: Using INSERT INTO ... VALUES
CREATE TABLE [dbo].[Customers] ( [ID] INT, [Name] VARCHAR(100))

INSERT INTO [dbo].[Customers] ( [ID], [Name] )
VALUES (1, 'John Doe')

-- Sample #2: Using INSERT INTO ... SELECT FROM
CREATE TABLE [dbo].[Client] ( [ID] INT, [Name] VARCHAR(100))

INSERT INTO [dbo].[Client] ( [ID], [Name] )
SELECT [ID], [Name]
FROM [dbo].[NewClient]

 5.  Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF.

Error Message:

Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table <Table Name> 
when IDENTITY_INSERT is set to OFF.

Causes:

This error occurs when you are trying to insert into a table that contains an identity column and you are assigning a value to the identity column instead of letting SQL Server assign the value.

View Error Message Details and Solution / Workaround

To illustrate, here's an example on how it can be encountered:

CREATE TABLE [dbo].[IdentityInsert] (
[ID] INT NOT NULL IDENTITY(1,1),
[Name] VARCHAR(50))
GO

INSERT INTO [dbo].[IdentityInsert] ( [ID], [Name] )
VALUES (1, ‘Old MacDonald’)
GO

Solution/Workaround:

There are two ways to avoid this error.  First is not to include the identity column in the INSERT statement and let SQL Server assign the next identity value to the record.

INSERT INTO [dbo].[IdentityInsert] ( [Name] )
VALUES (‘Old MacDonald’)
GO

The second method is with the setting of the IDENTITY_INSERT property to ON for the table.

SET IDENTITY_INSERT [dbo].[IdentityInsert] ON

INSERT INTO [dbo].[IdentityInsert] ( [ID], [Name] )
VALUES ( 2, 'Yankee Doodle')

SET IDENTITY_INSERT [dbo].[IdentityInsert] OFF

Setting the IDENTITY_INSERT to ON allows explicit values to be inserted into the identity column of a table.  At any given time, only one table in a session can have the IDENTITY_INSERT property set to ON.  If a table already has this property set to ON, and a SET IDENTITY_INSErt on statement is issued for another table, SQL Server returns an error message that states Set IDENTITY_INSERT is already ON and reports the table it is set ON for.  Execute permissions for the SET IDENTITY_INSERT default to the sysadmin fixed server role and the db_owner and db_ddladmin fixed database roles, and the object owner.

More Frequently Asked Questions - SQL Server Error Messages Next Page  
Related Articles :