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

Indexing Star Schemas

Hi
Can anyone add to / comment on / improve / criticise this logic for
indexing a fact table in a star schema:
Presuming a fact table with 4 dimensions:
* Brand
* Product
* Time
* Store
1. Clustered index on Time dimension
EVERY query is going to filter based on date, so this is an obvious
choice.
It is NOT a composite index for several reasons:
a) keep the record ID narrow so the index (& thus non-clustered
indexes) load into memory easier/faster.
b) common queries don't always a particular other dimension, which
would mean the index would only be useful to the Time level anyway.
c) common queries on product and store are not very selective
2. Non-clustered indexes on Foreign Keys
Create non-clustered indexes on each of the other 3 foreign keys, for
obvious reasons: Brand, Produce, Store.
3. Composite index on all dimensions
Some common queries use all directions. A complete composite index
would assist Cartesian product lookups. Create the index in order of
selectivity: Brand, Product, Store
(I'm not sure if Time would need to be explicity declared in this
index - as this is a non-clustered index, Time is included
"behind-the-scenes" as part of the RID anyway - but does it need to be
explicity declared?)
4. Only remaining index
The only remaining index would be Product, Store. This is done in
order of selectivity.
This means that any combination of 1, 2, 3 or all 4 of the dimensions
as criteria in the query will be catered for.
Does that make for a good strategy? Any comments / further
suggestions?
Thanks
Sean
It appears that you are indexing for select queries against the data
warehouse. Are you reporting directly from the data warehouse or building
cubes and reporting from them?
If you will be building cubes and reporting from them, then the indexes on
the tables should be geared toward integrity ( like the multicolumn index) ,
and the selecttions related to cube build and ETL interaction...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sean" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0410080749.cff18d3@.posting.google.co m...
> Hi
> Can anyone add to / comment on / improve / criticise this logic for
> indexing a fact table in a star schema:
> Presuming a fact table with 4 dimensions:
> * Brand
> * Product
> * Time
> * Store
> 1. Clustered index on Time dimension
> EVERY query is going to filter based on date, so this is an obvious
> choice.
> It is NOT a composite index for several reasons:
> a) keep the record ID narrow so the index (& thus non-clustered
> indexes) load into memory easier/faster.
> b) common queries don't always a particular other dimension, which
> would mean the index would only be useful to the Time level anyway.
> c) common queries on product and store are not very selective
> 2. Non-clustered indexes on Foreign Keys
> Create non-clustered indexes on each of the other 3 foreign keys, for
> obvious reasons: Brand, Produce, Store.
> 3. Composite index on all dimensions
> Some common queries use all directions. A complete composite index
> would assist Cartesian product lookups. Create the index in order of
> selectivity: Brand, Product, Store
> (I'm not sure if Time would need to be explicity declared in this
> index - as this is a non-clustered index, Time is included
> "behind-the-scenes" as part of the RID anyway - but does it need to be
> explicity declared?)
> 4. Only remaining index
> The only remaining index would be Product, Store. This is done in
> order of selectivity.
> This means that any combination of 1, 2, 3 or all 4 of the dimensions
> as criteria in the query will be catered for.
>
> Does that make for a good strategy? Any comments / further
> suggestions?
> Thanks
> Sean
|||if you use cubes, i personally wouldn't index a single thing
it's just a waste of time.
keep the clustered on time-- it's generally really helpful
what does the index tuning wizard reccomend?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OSmxSFSsEHA.2808@.TK2MSFTNGP14.phx.gbl...
> It appears that you are indexing for select queries against the data
> warehouse. Are you reporting directly from the data warehouse or building
> cubes and reporting from them?
> If you will be building cubes and reporting from them, then the indexes on
> the tables should be geared toward integrity ( like the multicolumn index)
,
> and the selecttions related to cube build and ETL interaction...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Sean" <plugwalsh@.yahoo.com> wrote in message
> news:3698af3c.0410080749.cff18d3@.posting.google.co m...
>