How can we INFORMATION_SCHEMA on a different server?

We've working on a system that is being used to populate various tables in
databases on multiple servers.
If the application is on Server 'A' and is trying to find information about
stored procedures on Server 'B' we're getting problems with the following
query
SELECT LTRIM(RTRIM(routine_name)) as Save_Query
FROM [B].[dbName].INFORMATION_SCHEMA.routines
ORDER BY routine_name
the error message is
OLE DB provider 'Darmstadtium' does not contain table
'"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
or the current user does not have permissions on that table.
Does anybody have any ideas for getting this working?
There is a logon for Server B with the same logon that Server A is using and
they have System Administrator as server roles.What OLE DB provider is "Darmstadtium"? What kind of server is server
B? Microsoft SQL Server? If so, what version of SQL Server? Can you
query any object on the remote server? (Every login to a SQL server
should be able to read master.dbo.sysobjects - can you successfully
execute "SELECT * FROM B.master.dbo.sysobjects"?)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Hamish Laws wrote:

>We've working on a system that is being used to populate various tables in
>databases on multiple servers.
>If the application is on Server 'A' and is trying to find information about
>stored procedures on Server 'B' we're getting problems with the following
>query
>SELECT LTRIM(RTRIM(routine_name)) as Save_Query
>FROM [B].[dbName].INFORMATION_SCHEMA.routines
>ORDER BY routine_name
>
>the error message is
>OLE DB provider 'Darmstadtium' does not contain table
>'"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
>or the current user does not have permissions on that table.
>Does anybody have any ideas for getting this working?
>There is a logon for Server B with the same logon that Server A is using an
d
>they have System Administrator as server roles.
>|||I think you will have a problem with the INFORMATION_SCHEMA views. The views
actually only exists in
the master database (in 2000, in 7.0 and 2005 they are in each database), he
nce your problem. Try
the system tables instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hamish Laws" <HamishLaws@.discussions.microsoft.com> wrote in message
news:A2CE4BDB-284D-46E5-B27D-1E9237553167@.microsoft.com...
> We've working on a system that is being used to populate various tables in
> databases on multiple servers.
> If the application is on Server 'A' and is trying to find information abou
t
> stored procedures on Server 'B' we're getting problems with the following
> query
> SELECT LTRIM(RTRIM(routine_name)) as Save_Query
> FROM [B].[dbName].INFORMATION_SCHEMA.routines
> ORDER BY routine_name
>
> the error message is
> OLE DB provider 'Darmstadtium' does not contain table
> '"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exis
t
> or the current user does not have permissions on that table.
> Does anybody have any ideas for getting this working?
> There is a logon for Server B with the same logon that Server A is using a
nd
> they have System Administrator as server roles.|||"Mike Hodgson" wrote:

> What OLE DB provider is "Darmstadtium"?
Sorry, bad editing on my part.
Darmstadtium is the actual name of the server I called 'B'

> What kind of server is server
> B? Microsoft SQL Server? If so, what version of SQL Server?
2000

> Can you
> query any object on the remote server? (Every login to a SQL server
> should be able to read master.dbo.sysobjects - can you successfully
> execute "SELECT * FROM B.master.dbo.sysobjects"?)
>
Yep, I can query other objects on the server.
If I connect to the second server using query analyzer and run the query
direct it works fine so it looks to me like the view isn't available as part
of the connection on a remote server
I took Tibor Karaszi's advice and rewrote it to use sysobjects.
Not as elegant but I'm getting the details I need out.
[vbcol=seagreen]
> Hamish Laws wrote:
>|||"Tibor Karaszi" wrote:

> I think you will have a problem with the INFORMATION_SCHEMA views. The vie
ws actually only exists in
> the master database (in 2000, in 7.0 and 2005 they are in each database),
hence your problem. Try
> the system tables instead.
>
Thanks for that.
I've taken your advice and I'm getting the information out of sysobjects
without a hassle.

> "Hamish Laws" <HamishLaws@.discussions.microsoft.com> wrote in message
> news:A2CE4BDB-284D-46E5-B27D-1E9237553167@.microsoft.com...
>
>