Results 1 to 3 of 3

Thread: Index on View

  1. #1
    Join Date
    Sep 2004
    Posts
    3

    Unanswered: Index on View

    Is it possilbe to create an index on a view using a linked server table? I am using openquery and I know I can't use rowset functions if I want to index a view.

    I just didn't know if there was any other way to access the linked server data in order to create an index.

    thanks!!

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    From the BOL:

    The ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed. The OBJECTPROPERTY function reports this for views through the ExecIsAnsiNullsOn or ExecIsQuotedIdentOn properties.


    The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.


    The view must not reference any other views, only base tables.

    All base tables referenced by the view must be in the same database as the view and have the same owner as the view.

    My emphasis added. There are additional rules. Your mileage may vary. These actors are professionals; please do not try this at home. Warranty valid for a limited time only please consult your users manual for additional information and any other additional "fine print" as others may see fit to add.


    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Sep 2004
    Posts
    3
    thanks for the quick reply and I apologize cause I am a rookie at this...here is what I have done per a previous post:

    set to on:
    ANSI_NULLS
    ANSI_PADDING
    ANSI_WARNINGS
    ARITHABORT
    CONCAT_NULL_YEILDS_NULL
    QUOTED_IDENTIFIERS

    Set to off:
    NUMERIC_ROUNDABORT

    Then I ran my create one table view (from an mysql ODBC linked server) with SCHEMABINDING:


    CREATE VIEW mysql_dnc with SCHEMABINDING
    AS
    select phone from openquery(mysql_dnc,
    'select phonefrom DNC')

    this doesn't work because of the following issue:
    Server: Msg 1054, Level 15, State 3, Procedure mysql_dnc, Line 5
    Syntax 'Openrowset' is not allowed in schema-bound objects.

    my question is can I access the linked server without using openquery so I can then create an index on it?

    thanks much!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •