INFORMATION_SCHEMA access across servers

• sarmistead

  SSCommitted

  Points: 1671

  More actions

  February 25, 2003 at 2:43 pm

  #63262

  Hi all,

  Does anyone have a way to select from a database's INFORMATION_SCHEMA that resides on another server? I have a linked server (DBSRVR) defined that works fine for standard selects, but when I do something like this:

  select COLUMN_NAME from DBSRVR.MISDataWAB.INFORMATION_SCHEMA.COLUMNS

  I get back this error:

  OLE DB provider 'DBSRVR' does not contain table '"MISDataWAB"."INFORMATION_SCHEMA"."COLUMNS"'. The table either does not exist or the current user does not have permissions on that table.

  If I remove the server name in the select and exec it on DBSRVR, the information is returned as expected.

  Am I missing something simple, or is it impossible to access INFORMATION_SCHEMA data from a remote server?

  Thx for your help,

  Steve Armistead,

  Database Administration

  Panther Systems Northwest, Inc.

  Vancouver, WA

  Steve

• sarmistead

  SSCommitted

  Points: 1671

  More actions

  February 25, 2003 at 3:06 pm

  #449751

  I found a solution to this problem - I had to alter the linked server definition to allow more security contexts to connect (had to set up a user name and pw on the remote server that the local server could connect "as"). Problem resolved.

  Steve Armistead,

  Database Administration

  Panther Systems Northwest, Inc.

  Vancouver, WA

  Steve