|
|
Error Message
Server: Msg 8159, Level 16, State 1, Line 1
'<Object Name>' has fewer columns than were specified
in the column list.
Causes
This error message may be encountered with different SQL Server objects. As will be shown below, this error message can be encountered when creating a view, refreshing a view definition, using a row constructor or table-value constructor or when using a common table expression (or CTE).
|
When creating views, this error may be encountered when the columns listed in the CREATE VIEW statement is more than the columns specified in the SELECT statement part of the view. To illustrate, here’s a view that will generate this error:
CREATE TABLE [dbo].[Customer] (
[CustomerID] INT NOT NULL IDENTITY(1, 1),
[CustomerName] VARCHAR(100),
[State] CHAR(2)
)
GO
CREATE VIEW [dbo].[Customer_NY] (
[CustomerID], [CustomerName], [State]
)
AS
SELECT [CustomerID], [CustomerName]
FROM [dbo].[Customer]
WHERE [State] = 'NY'
GO
Msg 8159, Level 16, State 1, Procedure Customer_NY, Line 1
'Customer_NY' has fewer columns than were specified in the column list.
As can be seen, there are 3 column names specified in the column list in the CREATE VIEW statement but the SELECT statement is returning only 2 columns.
This error message can also be encountered in a previously created view as illustrated in the following script:
CREATE TABLE [dbo].[Users] (
[UserID] INT NOT NULL IDENTITY(1, 1),
[UserName] VARCHAR(50),
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50),
[Gender] CHAR(1)
)
GO
CREATE VIEW [dbo].[AllUsers] (
[UserID], [UserName], [FirstName], [LastName], [Gender]
)
AS
SELECT * FROM [dbo].[Users]
GO
ALTER TABLE [dbo].[Users]
DROP COLUMN [Gender]
GO
SELECT * FROM [dbo].[AllUsers]
GO
Msg 8159, Level 16, State 1, Procedure AllUsers, Line 2
'AllUsers' has fewer columns than were specified in the column list.
Msg 4413, Level 16, State 1, Line 2
Could not use view or function 'dbo.AllUsers' because of binding errors.
EXECUTE [dbo].[sp_refreshview] 'AllUsers'
GO
Msg 8159, Level 16, State 1, Procedure sp_refreshsqlmodule_internal, Line 75
'AllUsers' has fewer columns than were specified in the column list.
If a view is created using the SELECT * notation instead of specifying each column to be included in the view and a column is dropped from the table being referenced in the view, performing a SELECT statement on the view will generate this error as shown above. Similarly, if the view is refreshed using the [dbo].[sp_refreshview] system stored procedure, the error is also generated because of the mismatch in the number of columns specified in the column list of the view and in the columns included in the SELECT statement part of the view.
Another way of encountering this error message is with the use of row constructor or table-valued constructor as derived table. Introduced in SQL Server 2008, row constructors consist of a single VALUES clause with multiple value lists enclosed in parenthesis and separated by commas. The following script illustrates how this error message may be generated with a row constructor or table-valued constructor:
SELECT *
FROM (VALUES ( 'USD', 'U.S. Dollar' ),
( 'EUR', 'Euro' )) [Currency] ( [Code], [Name], [Description] )
GO
Msg 8159, Level 16, State 1, Line 3
'Currency' has fewer columns than were specified in the column list.
As can be seen from the row constructor, the values list contains values for 2 columns but the columns list contains 3 column names.
Lastly, this error message can also be encountered when using common table expressions (CTE). A common table expressions (CTE) specifies a temporary named result set and is derived from a simple query and defined within the execution scope of a SELECT, INSERT, UPDATE or DELETE statement. The following script illustrates how this error can be generated when using a common table expression:
CREATE TABLE [dbo].[Student] (
[StudentID] INT NOT NULL IDENTITY(1, 1),
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50),
[GPA] DECIMAL(4, 3)
)
GO
WITH [HonorStudents] ( [StudentID], [FirstName], [LastName], [GPA], [Gender] )
AS
(SELECT * FROM [dbo].[Student]
WHERE [GPA] >= 3.5)
SELECT * FROM [HonorStudents]
GO
Msg 8159, Level 16, State 1, Line 2
'HonorStudents' has fewer columns than were specified in the column list.
As can be seen from this script, there are 4 columns in the [dbo].[Student] table but 5 columns are specified in the column list of the common table expression (CTE).
Solution / Work Around:
From the different ways of generating this error message, the common in all of them is the mismatch in the number of columns specified in a column list and in the number of columns specified in the column values.
In the first scenario, there are a few ways of avoiding this error message. One method is to provide the same number of columns in the SELECT statement part of the view definition as what is specified in the column list:
CREATE TABLE [dbo].[Customer] (
[CustomerID] INT NOT NULL IDENTITY(1, 1),
[CustomerName] VARCHAR(100),
[State] CHAR(2)
)
GO
CREATE VIEW [dbo].[Customer_NY] (
[CustomerID], [CustomerName], [State]
)
AS
SELECT [CustomerID], [CustomerName], [State]
FROM [dbo].[Customer]
WHERE [State] = 'NY'
GO
Another method is to not include the list of columns in the CREATE VIEW statement and just let the view determine the columns from the SELECT statement part of the view creation:
CREATE TABLE [dbo].[Customer] (
[CustomerID] INT NOT NULL IDENTITY(1, 1),
[CustomerName] VARCHAR(100),
[State] CHAR(2)
)
GO
CREATE VIEW [dbo].[Customer_NY]
AS
SELECT [CustomerID], [CustomerName]
FROM [dbo].[Customer]
WHERE [State] = 'NY'
GO
These same methods can be applied to the second scenario that generates this error message. In the second scenario, this error occurs when a column is dropped from a table that participates in a view. Doing a SELECT on the view or refreshing the view using the [dbo].[sp_refreshview] system stored procedure will generate this error. Since the view already exists, the methods outlined earlier can be used but with the ALTER VIEW statement to update the structure and definition of the view. Here are the ways of overcoming this error:
-- Option 1: Excluding the Dropped Column from the Column List
ALTER VIEW [dbo].[AllUsers] (
[UserID], [UserName], [FirstName], [LastName]
)
AS
SELECT * FROM [dbo].[Users]
GO
-- Option 2: Specifying the Columns in the SELECT Statement
ALTER VIEW [dbo].[AllUsers] (
[UserID], [UserName], [FirstName], [LastName]
)
AS
SELECT [UserID], [UserName], [FirstName], [LastName]
FROM [dbo].[Users]
GO
-- Option 3: Not Including the List of Columns in the ALTER View Statement
ALTER VIEW [dbo].[AllUsers]
AS
SELECT *
FROM [dbo].[Users]
GO
In the case of the row constructor or table-valued constructor, the error can easily be overcome either by supplying an additional column value in the list of VALUES to match the number of columns specified in the column list or byremoving the last column name in the list of columns to match each column value present in the constructor:
-- Option 1: Providing a Column Value for the Third Column
SELECT *
FROM (VALUES ( 'USD', 'U.S. Dollar', 'United States Dollar' ),
( 'EUR', 'Euro', 'Euro Currency' )) [Currency] ( [Code], [Name], [Description] )
GO
-- Option 2: Removing the Extra Column
SELECT *
FROM (VALUES ( 'USD', 'U.S. Dollar' ),
( 'EUR', 'Euro' )) [Currency] ( [Code], [Name] )
GO
Lastly, in the case of the common table expression (CTE), the same methods used in the views earlier can be applied to this scenario, as can be seen in the following scripts:
-- Option 1: Matching the Columns List with the Columns in the SELECT Statement
WITH [HonorStudents] ( [StudentID], [FirstName], [LastName], [GPA] )
AS
(SELECT * FROM [dbo].[Student]
WHERE [GPA] >= 3.5)
SELECT * FROM [HonorStudents]
GO
-- Option 2: Specifying the List of Columns in the SELECT Statement
WITH [HonorStudents] ( [StudentID], [FirstName], [LastName], [GPA] )
AS
(SELECT [StudentID], [FirstName], [LastName], [GPA]
FROM [dbo].[Student]
WHERE [GPA] >= 3.5)
SELECT * FROM [HonorStudents]
GO
-- Option 3: Not Including the List of Columns in the CTE Header Definition
WITH [HonorStudents]
AS
(SELECT * FROM [dbo].[Student]
WHERE [GPA] >= 3.5)
SELECT * FROM [HonorStudents]
GO
|