Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: Check If View Exists On A Linked Server

    I am trying to check if a view exists on a linked server using sys.views. I tried to fully qualify it but that produces an error telling me the below, which both the database name is correct as well as the server name. Is it possible to obtain a list of views from a linked server connection?

    Msg 7314, Level 16, State 1, Line 321 The OLE DB provider "SQLNCLI10" for linked server "alpha" does not contain the table ""salesdata"."sys"."views"". The table either does not exist or the current user does not have permissions on that table.


    SQL Server 2008 is the server I want to query from and sql server 2000 is the server I want to query


    EDIT ----
    even if I try to use this syntax it still produces the above said error
    Code:
    select
        count(*)
    from
        alpha.salesdata.INFORMATION_SCHEMA.VIEWS
    EDIT # 2 ----
    I also tried to qualify the views by using the below and still same error
    Code:
    select
        count(*)
    from
        alpha.salesdata.INFORMATION_SCHEMA.VIEWS
    where
        table_schema = 'dbo'
    Last edited by jo15765; 06-03-15 at 18:20.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    SQL Server 2000 did not have the sys.views table. Use
    Code:
    select count(*)
    from sysobjects
    where type = 'V'
    instead.

  3. #3
    Join Date
    Feb 2012
    Posts
    188
    Where can I specify which database to pull that information from?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    select count(*)
    from dbname..sysobjects
    where type = 'V'

Posting Permissions

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