Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Practice Test
Tip of the Day : SQL Server Database Design - Internet Movie Database (IMDb)
FAQ - SQL Server

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

 21.  A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Error Message:

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

Causes:

This error occurs when you are assigning the column values from a SELECT statement into local variables but not all columns are assigned to a corresponding local variable.

View Error Message Details and Solution / Workaround

To illustrate, using the [dbo].[Customers] table in the Northwind database, the following SELECT statement will generate the error:

DECLARE @CompanyName NVARCHAR(40)
DECLARE @ContactName NVARCHAR(30)

SELECT @CompanyName = [CompanyName],
@ContactName = [ContactName],
[ContactTitle]
FROM [dbo].[Customers]
WHERE [CustomerID] = 'ALFKI'

The error is caused by the [ContactTitle] because it is not assigned to a local variable.

Solution/Workaround:

To avoid this error, make sure that all columns are assigned to a local variable.

DECLARE @CompanyName  NVARCHAR(40)
DECLARE @ContactName  NVARCHAR(30)
DECLARE @ContactTitle NVARCHAR(30)

SELECT @CompanyName  = [CompanyName],
@ContactName  = [ContactName],
@ContactTitle = [ContactTitle]
FROM [dbo].[Customers]
WHERE [CustomerID] = 'ALFKI'

Alternatively, if you will not be using the column, simply remove it from the SELECT statement.

DECLARE @CompanyName  NVARCHAR(40)
DECLARE @ContactName  NVARCHAR(30)

SELECT @CompanyName  = [CompanyName],
@ContactName  = [ContactName]
FROM [dbo].[Customers]
WHERE [CustomerID] = 'ALFKI'

If you really need to do both, meaning to assign the value to local variables and to return the columns as a result set, you have to do it in 2 steps instead of combining them into one SELECT statement:

DECLARE @CompanyName NVARCHAR(40)
DECLARE @ContactName NVARCHAR(30)

SELECT @CompanyName = [CompanyName],
@ContactName = [ContactName],
[ContactTitle]
FROM [dbo].[Customers]
WHERE [CustomerID] = 'ALFKI'

SELECT [ContactTitle]
FROM [dbo].[Customers]
WHERE [CustomerID] = 'ALFKI'

 22.  ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Error Message:

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

Causes:

As the message describes, this message occurs when you are doing a SELECT DISTINCT combined with the ORDER BY clause and one of the columns in the ORDER BY is not specified as one of the columns in the SELECT DISTINCT.

View Error Message Details and Solution / Workaround

To illustrate, using the [dbo].[Orders] table in the Northwind database, suppose you want to get a list of unique Customer IDs that had orders ordered by the date of the order with the newest orders first, the following SELECT statement will generate the error above:

SELECT DISTINCT [CustomerID]
FROM [dbo].[Orders]
ORDER BY [OrderDate] DESC

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

Solution/Workaround:

To avoid encountering this error, instead of using the DISTINCT clause, you can use the GROUP BY clause to get the unique records from a table.  The query above can be re-written as follows:

SELECT [CustomerID]
FROM [dbo].[Orders]
GROUP BY [CustomerID]
ORDER BY MAX([OrderDate]) DESC

This query will return the unique Customer IDs ordered by the newest orders in descending order.


 23.  String or binary data would be truncated.  The statement has been terminated.

Error Message:

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

Causes:

This error is usually encountered when inserting a record in a table where one of the columns is a VARCHAR or CHAR data type and the length of the value being inserted is longer than the length of the column.

View Error Message Details and Solution / Workaround

To illustrate, let’s say you have the following table:

CREATE TABLE [dbo].[Students] (
[StudentID]        INT,
[FirstName]        VARCHAR(10),
[LastName]         VARCHAR(10)
)

INSERT INTO [dbo].[Students] ( [StudentID], [FirstName], [LastName] )
VALUES ( 12345, 'Rumpelstiltskin', '' )

Since the [FirstName] column will only accept 10 characters, the INSERT statement will fail because the length of the value being inserted is more than 10 characters.

The error can also be encountered when decreasing the length of a VARCHAR or CHAR column in a table that already contains data and the new length of the column is not long enough to accommodate the longest value in the column.

CREATE TABLE [dbo].[Students] (
[StudentID]        INT,
[FirstName]        VARCHAR(20),
[LastName]         VARCHAR(20)
)

INSERT INTO [dbo].[Students] ( [StudentID], [FirstName], [LastName] )
VALUES ( 12345, 'Rumpelstiltskin', '' )

ALTER TABLE [dbo].[Students] ALTER COLUMN [FirstName] VARCHAR(10)

Solution/Workaround:

To prevent this error from happening when inserting data to your table or when updating existing data in your table, always make sure that the string value you are trying to insert into your table can fit to the column you’ve specified.  If you really need to insert the data to the table, your only option is to increase the length of the VARCHAR/CHAR column using the ALTER TABLE command:

ALTER TABLE [dbo].[Students] ALTER COLUMN [FirstName] VARCHAR(50)

If the error is caused by the decreasing of the length of the column, first check the maximum length that the column have and use that as the new length of the column.  To determine the maximum length of a particular column, you can do the following:

SELECT MAX(LEN([FirstName])) FROM [dbo].[Students]

This will return the length of the longest [FirstName].  Then use this value as the new length of your column if you need to decrease its length.


 24.  Procedure or function Stored Procedure or Function Name has too many arguments specified.

Error Message:

Server: Msg 8144, Level 16, State 2, Procedure Stored Procedure or Function Name, Line 0
Procedure or function Stored Procedure or Function Name has too many arguments specified.

Causes:

As the message describes, this error is encountered when you are passing arguments or parameters to a function or stored procedure which is more than what the function or stored procedure is expecting.

View Error Message Details and Solution / Workaround

To illustrate, let’s say you have the following function definition:

CREATE FUNCTION [dbo].[ufn_Concat]
( @pString1 VARCHAR(10), @pString2 VARCHAR(10) )
RETURNS VARCHAR(20)
AS
BEGIN
RETURN ISNULL(@pString1 + ' ', '') + ISNULL(@pString2, '')
END

This function expects only 2 arguments, namely @pString1 and @pString2.  To use this function, you do the following:

SELECT [dbo].[ufn_Concat] ( [FirstName], [LastName] ) AS [FullName]
FROM [dbo].[Customers]

The error will be encountered you pass more than 2 arguments or parameters to the function, as follows:

SELECT [dbo].[ufn_Concat] ( [FirstName], [MiddleName], [LastName] ) AS [FullName]
FROM [dbo].[Customers]
Server: Msg 8144, Level 16, State 2, Line 1
Procedure or function dbo.ufn_Concat has too many arguments specified.

Solution/Workaround:

To avoid this error from happening, always make sure that you pass the same number of arguments that a stored procedure or function is expecting.  To know the parameters expected by a stored procedure, you can use the sp_help system stored procedure and pass the name of the stored procedure as the parameter.


 25.  An insufficient number of arguments were supplied for the procedure or function Stored Procedure or Function Name.

Error Message:

Server: Msg 313, Level 16, State 2, Line 1
An insufficient number of arguments were supplied for the procedure or function 
Stored Procedure or Function Name.

Causes:

As the message describes, this error is encountered when you are passing arguments or parameters to a function or stored procedure which is less than what the function or stored procedure is expecting.

View Error Message Details and Solution / Workaround

To illustrate, let’s say you have the following function definition:

CREATE FUNCTION [dbo].[ufn_Concat]
    ( @pString1 VARCHAR(10), @pString2 VARCHAR(10), @pString3 VARCHAR(10) )
    RETURNS VARCHAR(30)
    AS
    BEGIN
    RETURN ISNULL(@pString1 + ' ', '') + 
    ISNULL(@pString2 + ' ', '') +
    ISNULL(@pString3, '')
    END
    

This function expects 3 arguments, namely @pString1, @pString2 and @pString3.  To use this function, you do the following:

SELECT [dbo].[ufn_Concat] ( [FirstName], [MiddleName], [LastName] ) AS [FullName]
    FROM [dbo].[Customers]
    

The error will be encountered you don’t pass 3 arguments or parameters to the function, as follows:

SELECT [dbo].[ufn_Concat] ( [FirstName], [LastName] ) AS [FullName]
    FROM [dbo].[Customers]
    
Server: Msg 313, Level 16, State 2, Line 1
    An insufficient number of arguments were supplied for the procedure or function dbo.ufn_Concat.
    

Solution/Workaround:

To avoid this error from happening, always make sure that you pass the same number of arguments that a stored procedure or function is expecting.  To know the parameters expected by a stored procedure, you can use the sp_help system stored procedure and pass the name of the stored procedure as the parameter.

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