|
|
Error Message:
Server: Msg 4502, Level 16, State 1,
Procedure Object Name, Line 4
View or function 'View or Function Name' has more
column names specified than columns defined.
Server: Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'View or Function Name'
because of binding errors.
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]
Solution/Workaround:
To avoid this error, you have to refresh the view by calling the 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.
|