Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Differences Between ISNULL and COALESCE Functions
Error Messages
Home > SQL Server Error Messages > Msg 4502 - View or function 'View or Function Name' has more column names specified than columns defined.
SQL Server Error Messages - Msg 4502 - View or function 'View or Function Name' has more column names specified than columns defined.

Error Message

Server: Msg 4502, Level 16, State 1, Line 1
View or function 'View or Function Name' has more 
column names specified than columns defined.

Causes:

This error occurs when a column being referenced by a view has been dropped from the base table being referenced by the view and a SELECT statement is performed on the view.

To illustrate, suppose you have the following table and view:

CREATE TABLE [dbo].[Customers] (
    [CustomerID]    INT,
    [CustomerName]  VARCHAR(100),
    [Address]       VARCHAR(100),
    [Gender]        CHAR(1)
)
GO

CREATE VIEW [dbo].[CustomersView]
AS
SELECT * FROM [dbo].[Customers]
GO

Doing a SELECT * FROM [dbo].[CustomersView] statement will give you the rows from the table with all the columns of the [dbo].[Customers] table.

The error above will be generated if one of the columns in [dbo].[Customers] is suddenly dropped from the table and a SELECT statement is performed on the view:

ALTER TABLE [dbo].[Customers] DROP COLUMN [Gender]
SELECT * FROM [dbo].[CustomersView]
Server: Msg 4502, Level 16, State 1, Line 1
View or function 'CustomersView' has more column names specified than columns defined.

Server: Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'CustomersView' because of binding errors.

Solution / Work Around:

To avoid this error, you have to refresh the view by calling the [dbo].[sp_refreshview] system stored procedure:

EXECUTE sp_refreshview '[dbo].[CustomersView]'

The sp_refreshview system stored procedure refreshes the metadata for the specified view as persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

Related Articles :