I got read only access to Views and when i am trying to query the View i got this error message. Can anyone help me understand what is the actual problem and how to fix it. FYI.. this is the 1st time i am viewing this table . Here is the error message.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ProductCategoryL2Name'.
Could not use view or function 'DB.Product' because of binding errors.
It sounds like the view was created and then one of the underlying tables was changed. I.e., ProductCategoryL2Name no longer exists or was renamed. You can try this to get the view's definition, but the sys tables might be locked down. Your best bet is to go talk to whoever owns the database and ask them to fix it (which can be quite a rabbit hole in large organizations or on consulting gigs).
SELECT sm.definition
FROM [YourDB].sys.sql_modules AS sm
JOIN [YourDB].sys.objects AS o
ON sm.object_id = o.object_id
WHERE sm.object_id = OBJECT_ID('YourDB.dbo.ViewName')
To correct the wrong column name using the SSMS tool, right click the table with issue in the "View" folder then click "Design" and correct it from there, make sure to save when you click close.
when deploying changes to our systems it is not uncommon to get this error message Could not use view or function because of binding errors.
This happens because a view can become outdated because of changes to the underlying objects upon which the view depends.
you can use sp_refreshview to for correction - or better - prevention - if you do a sp_refreshview @viewname='the_view_I_have_just_changed' every time you change a view or function.
example:
use my_database
exec sp_refreshview @viewname='dbo.vw_select_applicant'
exec sp_refreshview @viewname='dbo.vw_search'
execute as login='cola'
--to check the permissions
select top 10 * from dbo.vw_select_applicant
select top 10 * from dbo.vw_search
The binding error occurs because the columns the view is expecting to find in its SELECT statement no longer exist.
