Create a schema bound view on a non-schema bound view or base table

Posted on 2012-08-15
Last Modified: 2013-02-14
I need to create a view with an index field. I know that in order to do this the view must be schemabound. The view i am trying to create is based on another view. When i try to create the schema bound view however i get an error message saying that the base view is not schemabound. Unfortunately, the base view is created by a 3rd party application so i don't want to mess with that too much. Is there a way to create a schemabound view based on a table or view that is not schemabound itself.

Error output below.

Cannot schema bind view 'ContactsByProjectID'. 'dbo.A_PROJECTID' is not schema bound.
Question by:Forensicon
    LVL 41

    Expert Comment

    You cannot create a view WITH SCHEMABINDING when its definition mentions another view that was not itself created WITH SCHEMABINDING.

    So what you could try is create a stage table with the result of the third party view

    select * into stagetable from dbo.A_ProjectID

    then you create your new view based on the stage table
    LVL 12

    Accepted Solution

    2 options:

    1.> Either make the base view as schema bound too. Just adding WITH SCHEMABINDING into the view definition won't have any impact on the way the base view is being called so you could safely do it.
    2.> Create one more copy of the same base view (with some other name) as schema bound view this time and write your view on top of this new view. Even though it is redundancy in terms of code maintenance but it might give you what you want.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now