|
|
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.
|