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

Indexes stop working

We have about 10 sites using SQL Server 2000 SP1 or 3 or 3a, across two servers. The publisher/distributor performs transactional replication on three databases to the 'secondary' server.

Nearly all of the sites, at one time or another have experienced the indices on one of the tables simply stop working. Its always the same indices on a table that can have between 70,000 and a 300, 000 rows.

Before I start posting schema or index details anyone come across this before?Only once. Actually, now that I think about it, not even once. Do you have Auto-Stats set on for these databases? Or does UPDATE STATISTICS tablename resolve the problem?|||Yeah, that's happened to me a couple of times. Well two or three times, anyway. Or once. Maybe once. No, not even once. What the heck do you mean "stopped working", anyway? Are they not showing up in the mornings, or are your indexes just sitting in their cubes staring at the screensaver?|||auto update statistics was on so switched this off and ran update statistics again manually to no avail. The table in question is as follows:

CREATE TABLE [dbo].[rawdata] (
[evtime] [datetime] NOT NULL ,
[type] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[srcid] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[rawinfo] [varchar] (80) COLLATE Latin1_General_CI_AS NULL ,
[msrepl_tran_version] [uniqueidentifier] NOT NULL ,
[seqno_yr] [int] NOT NULL ,
[seqno] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE INDEX [IX_rawdata] ON [dbo].[rawdata]([type], [evtime]) ON [PRIMARY]
GO
CREATE INDEX [IX_rawdata_1] ON [dbo].[rawdata]([srcid], [evtime]) ON [PRIMARY]
GO
CREATE INDEX [IX_rawdata_2] ON [dbo].[rawdata]([evtime]) ON [PRIMARY]
GO

The problem, when it starts happening, means the following query stops returning data. Using different criteria by matching exactly rather than on a range in evtime works proving the data is there.

select * from rawdata where evtime between '2004-11-01 00:00:00.000' and '2004-11-02 00:00:00.000'

I am not sure whether transactional replication has anything to do with the cause. Because this data is only needed occasionally, we can get away with dropping the subscription, dropping the publication then dropping and recreating the indexes. We then publish and create a subscription to the data. It lasts for about 3 or 4 days then it goes again.

There are no maintenance plans set for this database which is possibly the problem. Running DBCC SHOWCONTIG never completes - it just sits there.

Help!|||You will want to leave Auto-stats on.

How many rows in the table? And how long has DBCC Showcontig gone for, before you give up on it? Have you run DBCC CHECKTABLE on this table?