Post reply

invalid for schema binding

  • January 13, 2009 at 12:14 pm

    #198219

    Hello gurus,

    I want to create a view with the option 'schemabinding' so i can be able to add an index to it but is to a table from a linked server.

    I receive an error message:,

    Cannot schema bind view 'checks' because name 'servername.dbo.table2' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

    CREATE VIEW [dbo].[All_Distributions]

    WITH SCHEMABINDING

    AS

    SELECT [FirstName], [LastName], [Contacts].[UserID], [Contacts].[ContactDescription], [Contacts].[IsPublic]

    FROM dbo.table1

    UNION ALL

    SELECT top 100 percent [FirstName], [LastName], [Contacts].[UserID], [Contacts].[ContactDescription], [Contacts].[IsPublic]

    FROM servername.dbo.table2

    ORDER BY ID_PK

  • January 13, 2009 at 12:18 pm

    #926163

    You can't schema-bind a remote table. Schema binding doesn't work that way, since the local server can't force the remote server to hold onto the schema definition. Not possible.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 13, 2009 at 12:27 pm

    #926177

    thank you!

  • January 15, 2009 at 12:58 pm

    #927648

    To see more details about Indexable view please check this link.

    http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

    -Roy

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply