|
|
Error Message
Server: Msg 8158, Level 16, State 1, Line 1
'<Object Name>' has more 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 less 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]
)
AS
SELECT * FROM [dbo].[Customer]
WHERE [State] = 'NY'
GO
Msg 8158, Level 16, State 1, Procedure Customer_NY, Line 1
'Customer_NY' has more columns than were specified in the column list.
As can be seen, there are only 2 column names specified in the column list in the CREATE VIEW statement but the SELECT statement is returning 3 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)
)
GO
CREATE VIEW [dbo].[AllUsers] (
[UserID], [UserName], [FirstName], [LastName]
)
AS
SELECT * FROM [dbo].[Users]
GO
ALTER TABLE [dbo].[Users]
ADD [Gender] CHAR(1)
GO
SELECT * FROM [dbo].[AllUsers]
GO
Msg 8158, Level 16, State 1, Procedure AllUsers, Line 2
'AllUsers' has more 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 8158, Level 16, State 1, Procedure sp_refreshsqlmodule_internal, Line 75
'AllUsers' has more 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 new column is added to 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] )
GO
Msg 8158, Level 16, State 1, Line 3
'Currency' has more 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 only contains 1 column name.
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] )
AS
(SELECT * FROM [dbo].[Student]
WHERE [GPA] >= 3.5)
SELECT * FROM [HonorStudents]
GO
Msg 8158, Level 16, State 1, Line 2
'HonorStudents' has more 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 only 3 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. The first method is to provide the correct number of columns in the column list when defining the view:
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 * FROM [dbo].[Customer]
WHERE [State] = 'NY'
GO
The second method is to list the columns in the SELECT statement part of the view creation instead of using the asterisk "*". This method is used if not all columns from the source table or tables are needed by the view:
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]
)
AS
SELECT [CustomerID], [CustomerName]
FROM [dbo].[Customer]
WHERE [State] = 'NY'
GO
Yet 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 *
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 new column is added to 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 three ways of overcoming this error:
-- Option 1: Including the Newly Added Column in the Column List
ALTER VIEW [dbo].[AllUsers] (
[UserID], [UserName], [FirstName], [LastName], [Gender]
)
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 by supplying a column name for each column value present in the constructor:
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: Specifying All Columns in the Column List
WITH [HonorStudents] ( [StudentID], [FirstName], [LastName], [GPA] )
AS
(SELECT * FROM [dbo].[Student1]
WHERE [GPA] >= 3.5)
SELECT * FROM [HonorStudents]
GO
-- Option 2: Specifying the List of Columns in the SELECT Statement
WITH [HonorStudents] ( [StudentID], [FirstName], [LastName] )
AS
(SELECT [StudentID], [FirstName], [LastName]
FROM [dbo].[Student1]
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].[Student1]
WHERE [GPA] >= 3.5)
SELECT * FROM [HonorStudents]
GO
|