Information Schema Query on linked server fails.

Hi, We had a simple application which compared schemas on local servers. We
are updating it to compare schemas across servers. Unfortunately the query
which refers to the Information_Schema fails, and I cant find any syntax to
make it work!
Here is the simplified version of the query.
SELECT 1 FROM [Matrix].[ReviewRecorder].[DBO].INFORMATION_SCHEMA.TABLES
tried the following combinations out of frustration, but none worked.
SELECT 1 FROM [Matrix].[ReviewRecorder].[INFORMATION_SCHEMA].TABLES
SELECT 1 FROM [Matrix].[ReviewRecorder].[DBO].[INFORMATION_SCHEMA].[TABLES]
Obviously Matrix is the name of the remote Database Server, and it has been
linked already to the local Database.
The error i get is
--
The object name 'Matrix.ReviewRecorder.DBO.INFORMATION_SCHEMA.' contains
more than the maximum number of prefixes. The maximum is 3.
--
sp_linkedservers shows
--
Matrix SQLOLEDB SQL Server Matrix NULL NULL NULL
--
HELP !
Thanks
MohammedHi
Have you tried using select * from
{RemoteSvr}.{dbname}.information_schema.tables
Substitute the name in {RemoteSvr} and {dbname}
John
"MLokhandwala" wrote:

> Hi, We had a simple application which compared schemas on local servers. W
e
> are updating it to compare schemas across servers. Unfortunately the query
> which refers to the Information_Schema fails, and I cant find any syntax t
o
> make it work!
> Here is the simplified version of the query.
> SELECT 1 FROM [Matrix].[ReviewRecorder].[DBO].INFORMATION_SCHEMA.TABLES
> tried the following combinations out of frustration, but none worked.
> SELECT 1 FROM [Matrix].[ReviewRecorder].[INFORMATION_SCHEMA].TABLES
> SELECT 1 FROM [Matrix].[ReviewRecorder].[DBO].[INFORMATION_SCHEMA].[TABLES]
> Obviously Matrix is the name of the remote Database Server, and it has bee
n
> linked already to the local Database.
> The error i get is
> --
> The object name 'Matrix.ReviewRecorder.DBO.INFORMATION_SCHEMA.' contains
> more than the maximum number of prefixes. The maximum is 3.
> --
> sp_linkedservers shows
> --
> Matrix SQLOLEDB SQL Server Matrix NULL NULL NULL
> --
> HELP !
> Thanks
> Mohammed|||Yes,
I have tried all combinations, including dropping the owner name etc. but no
luck.
Any other suggestions are welcome.
Still awaiting a solution.
Mohammed
"John Bell" wrote:
> Hi
> Have you tried using select * from
> {RemoteSvr}.{dbname}.information_schema.tables
> Substitute the name in {RemoteSvr} and {dbname}
> John
> "MLokhandwala" wrote:
>|||Hi
It seems 4 part naming only works in master!!!! You could try either calling
a stored procedure in the remote database or creating a view e.g.
-- On Remote Server database run:
CREATE VIEW MyTables AS SELECT * FROM INFORMATION_SCHEMA.TABLES
-- From Local Server Access Remove server
SELECT * FROM Matrix.ReviewRecorder.dbo.MyTables
John
"MLokhandwala" wrote:
> Yes,
> I have tried all combinations, including dropping the owner name etc. but
no
> luck.
> Any other suggestions are welcome.
> Still awaiting a solution.
> Mohammed
>
> "John Bell" wrote:
>|||If you are willing to use Java, there is a free open-source tool called
SchemaCrawler on SourceForge that can compare schemas between databases
on two different servers. Download SchemaCrawler from:
http://sourceforge.net/project/show...group_id=148383