Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Posts
    30

    Unanswered: Need to access INFORMATION_SCHEMA on linked server

    Hi,
    I'm working with MSSQL2K+SP3a, Standard Edition. I defined linked server (MSDE).
    On the local server, I can do
    select * from [testDB].[INFORMATION_SCHEMA].[TABLES] -- local

    select * from [testSRV].[testDB_far].[dbo].[sysobjects] -- linked

    but not

    select * from [testSRV].[testDB_far].[INFORMATION_SCHEMA].[TABLES]

    How can I access the INFORMATION_SCHEMA on the linked server ?

    Thanks,
    Helena

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    create a view in linkedserver on DB which u want to see the table information and access that view from ur local server.
    Note: give permission if its required to access that view.
    Code:
    --- create view script--
    create view dbo.vINFORMATION_SCHEMATABLES
    as
    SELECT     *
    FROM         INFORMATION_SCHEMA.TABLES
    go
     
    -- select statement from local server
    select * from [testSRV].[testDB_far].[dbo].[vINFORMATION_SCHEMATABLES]
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Cheat?
    Code:
    EXECUTE [testSrv].[testDB_far].dbo.sp_executesql
       N'SELECT * FROM INFORMATION_SCHEMA.TABLES'
    -PatP

  4. #4
    Join Date
    Sep 2004
    Posts
    30
    Thanks for the fast replay .
    Mallier - I can’t change anything in the remote db.
    Pat - you solution works perfect .

    Thanks to all
    Helena

Posting Permissions

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