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.This is a multi-part message in MIME format.
--010400070406000708090003
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
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 and
>they have System Administrator as server roles.
>
--010400070406000708090003
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>What OLE DB provider is "Darmstadtium</tt><tt>"? 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"?)</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Hamish Laws wrote:
<blockquote cite="midA2CE4BDB-284D-46E5-B27D-1E9237553167@.microsoft.com"
type="cite">
<pre wrap="">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.
</pre>
</blockquote>
</body>
</html>
--010400070406000708090003--|||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), hence 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 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.|||"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.
> 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 and
> >they have System Administrator as server roles|||"Tibor Karaszi" wrote:
> 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), 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...
> > 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.
>
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment