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,
Helenacreate 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.

-- 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]|||Cheat?EXECUTE [testSrv].[testDB_far].dbo.sp_executesql
N'SELECT * FROM INFORMATION_SCHEMA.TABLES'-PatP|||Thanks for the fast replay .
Mallier - I cant change anything in the remote db.
Pat - you solution works perfect .

Thanks to all
Helena

Need some assistance linking an Oracle database

I'm trying to set up an Oracle database as a linked server in SQL server 7 but having some problems.

The client software is installed and seems to be working fine (Used access to link some tables). I set the product name to Oracle 8.1 and the Datasource to the system dns name. I left provider string, location and catalog blank. Under the security tab I had it set to map to the oracle account and also tried the impersonate option, but always got the same error.

Error: 7399 OLE DB provider 'MSDASQL' reported an error. Driver's SQLSetConnectAttr failed]
ORA-12640: Authentication Adapter initialization failed]

This is my first time trying to set up and Oracle database as a linked server, any help is appreciated.Made a few changes and now getting the following error.

"Error 7399: OLE DB provider 'MSDAORA' reported an error"

I get this error when trying to view the list of tables under the linked server in enterprise manager. When i run a basic query through query analyzer, i get this expanded error message.

"OLE/DB provider returned message: ORA-12154: TNS:could not resolve service name"

Using Oracle ODBC Test utility in the Oracle client I am able to connect to the database and run queries.|||The most common problem in using Linked Server and other distributed services is SQL Server Agent did not have Network permissions which means your SQL Server is installed with the Microsoft provided local systems account, I maybe wrong but I think in 7.0 you may have to reinstall SQL Server with a Service account. You may not find the info about service account in SQL Server 7.0 BOL(books Online) because it took a while to get Microsoft to require the Service account. Hope This helps.

Kind regards,
Gift Peddie|||The error I would get is the following. "OLE/DB provider returned message: ORA-12154: TNS:could not resolve service name".

I haven't been able to find a good step by step guide to follow to link to an Oracle database. Trying to reinstall the server might not be an option. If I could find something that did say it was the problem you mentioned, then it would probably be worth it.

Can anyone shed some light on this problem. Or managed to link to an oracle databse from SQL?|||Try these two urls, the first has most known SQL Server Errors and the solutions complined over the years by Neil Pikes and the second is one of the best Oracle info site on the Web including Oracle BOL(Books online). Hope this helps.
http://www.mssqlserver.com/faq
http://www.dbasupport.com

kind regards,
Gift Peddie

Need Linked Server to show more db tables

Moved a database over to a new SQL server. That database had a couple
of stored procedures that call two different databases. I linked the
servers, but I know that the tables that show up correspond to the
default database on the security account i have setup to connect to the
server. Is there any way to allow connections to other databases/tables
with the same link?You can connect to more than just what you see - just use
the four part name qualifier of:
LinkedServerName.Database.Owner.Object
That's controlled by permissions, not by what you "see" in
Enterprise Manager listed as the tables for the linked
server. The tables displayed are based on what the default
database for the login is if you have not specified a
Catalog (database) for the linked server property. If you
want to change the database you are initially connected to,
enter the database name in the Catalog property for the
linked server.
-Sue
On 17 Oct 2005 13:42:22 -0700, danny.spence@.gmail.com wrote:

>Moved a database over to a new SQL server. That database had a couple
>of stored procedures that call two different databases. I linked the
>servers, but I know that the tables that show up correspond to the
>default database on the security account i have setup to connect to the
>server. Is there any way to allow connections to other databases/tables
>with the same link?

Need Linked Server to show more db tables

Moved a database over to a new SQL server. That database had a couple
of stored procedures that call two different databases. I linked the
servers, but I know that the tables that show up correspond to the
default database on the security account i have setup to connect to the
server. Is there any way to allow connections to other databases/tables
with the same link?
You can connect to more than just what you see - just use
the four part name qualifier of:
LinkedServerName.Database.Owner.Object
That's controlled by permissions, not by what you "see" in
Enterprise Manager listed as the tables for the linked
server. The tables displayed are based on what the default
database for the login is if you have not specified a
Catalog (database) for the linked server property. If you
want to change the database you are initially connected to,
enter the database name in the Catalog property for the
linked server.
-Sue
On 17 Oct 2005 13:42:22 -0700, danny.spence@.gmail.com wrote:

>Moved a database over to a new SQL server. That database had a couple
>of stored procedures that call two different databases. I linked the
>servers, but I know that the tables that show up correspond to the
>default database on the security account i have setup to connect to the
>server. Is there any way to allow connections to other databases/tables
>with the same link?

Need Help! Connection Count to Linked DB2 Server Reaches limit (SQL1040N) when execute dyn

Need Help! Connection Count to Linked DB2 Server Reaches limit
(SQL1040N) when execute dynamic generated OPENQUERY T-SQL
Hello, every body.
I created a linked server to DB2 8.1 database which called
GRR_DB2Server. In my stored procedure p_FetchRawData, I need to read
some data from this linked server GRR_DB2Server and insert them into
local SQLServer table SQLServer_A.
Query to GRR_DB2Server joins 3 large DB2 tables DB2_A, DB2_B, DB2_C
(every table has about 1 million records), and part of the query
condition stored as record in table SQLServer_B in local SQLServer.
At first I directly join these 4 tables in one T-SQL statements, but to
my disappointment I found the performance very low afer some practice.
So I changed the T-SQL to use cursor to loop for fetching every row
data in SQLServer_D condition table to some procedure variables, and
then in this loop I generated dynamic T-SQL string which orgnize the
condition and form one OPENQUERY statement.
The pseud code something like this:
CREATE PROCEDURE p_FetchRawData variable_list
AS
BEGIN
.=2E..
DECLARE condition_cursor CURSOR LOCAL FORWARD_ONLY FOR
SELECT * FROM local_condition_table
OPEN condition_cursor
FETCH NEXT FROM condition_cursor INTO
local_variables
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
SET @.Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @.Dynamic_STR +
')'
EXEC @.Dynamic_SQL
END
.=2E..
END
But when execute this stored procedure p_FetchRawData, when the loop
count is too big, then I got the error:
[OLE/DB provider returned message: SQL1040N
=E4=B8=8E=E6=95=B0=E6=8D=AE=E5=BA=93=E7=9B=B8=E8=BF=9E=E7=9A=84=E5=BA=94=E7= =94=A8=E7=A8=8B=E5=BA=8F=E5=B7=B2=E8=BE=BE=E5=88=B0=E6=9C=80=E5=A4=A7=E6=95= =B0=E7=9B=AE=E3=80=82
SQLSTATE=3D57030]
OLE DB error trace [OLE/DB Provider 'IBMDADB2'
IDBInitialize::Initialize returned 0x80040e69].
I understood this error meaning which said too many OPENQUERY
connection. I just wonder why every DYNAMIC T-SQL EXECECUTION keeps
their connections to linked server? How to fail these connections when
every OPENQUERY execution finished?
Thanks.
Regards,
Ling, Xiao-liSorry, the pseud code should lik this, just pseud code, in case someone
will question the pseud code validity:
CREATE PROCEDURE p_FetchRawData variable_list
AS
BEGIN
.=2E..
DECLARE condition_cursor CURSOR LOCAL FORWARD_ONLY FOR
SELECT * FROM local_condition_table
OPEN condition_cursor
FETCH NEXT FROM condition_cursor INTO
local_variables
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
SET @.Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @.Dynamic_STR +
')'
EXEC (@.Dynamic_SQL)
FETCH NEXT FROM condition_cursor INTO
local_variables
END
.=2E..
END
alingsjtu@.gmail.com =E5=86=99=E9=81=93=EF=BC=9A
> CREATE PROCEDURE p_FetchRawData variable_list
> AS
> BEGIN
> ...
> DECLARE condition_cursor CURSOR LOCAL FORWARD_ONLY FOR
> SELECT * FROM local_condition_table
> OPEN condition_cursor
> FETCH NEXT FROM condition_cursor INTO
> local_variables
> WHILE @.@.FETCH_STATUS =3D 0
> BEGIN
> SET @.Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
> SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @.Dynamic_STR +
> ')'
> EXEC @.Dynamic_SQL
> END
> ...
> END
> But when execute this stored procedure p_FetchRawData, when the loop
> count is too big, then I got the error:
> [OLE/DB provider returned message: SQL1040N
> =E4=B8=8E=E6=95=B0=E6=8D=AE=E5=BA=93=E7=9B=B8=E8=BF=9E=E7=9A=84=E5=BA=94==E7=94=A8=E7=A8=8B=E5=BA=8F=E5=B7=B2=E8=BE=BE=E5=88=B0=E6=9C=80=E5=A4=A7=E6==95=B0=E7=9B=AE=E3=80=82
> SQLSTATE=3D57030]
> OLE DB error trace [OLE/DB Provider 'IBMDADB2'
> IDBInitialize::Initialize returned 0x80040e69].
> I understood this error meaning which said too many OPENQUERY
> connection. I just wonder why every DYNAMIC T-SQL EXECECUTION keeps
> their connections to linked server? How to fail these connections when
> every OPENQUERY execution finished?
> > Thanks.
> > Regards,
> Ling, Xiao-li