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
No comments:
Post a Comment