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 Service and Full Text Search?

I have a solutions database I am adding Full Text Search to.
Unfortunately, a huge part of the "solutions" resides on a shared lan in the
form of attachments.
I've used FTS before, and I've used Index server before (though never on
files located remotely)
Are there any resources on using both FTS and the Indexing Service together?
I thought I read that it was possible to run all the queries through SQL
server and that SQL server could go out and talk to the Indexing service
behind the sceens.
- thanks
Jack
Jack.fruhatsolcorpdotcom
you can't really use them together in any meaningful way. Your best bet is
to either index everything using IS by spitting your content out row by row
into the file system naming each file after the pk. This way you can track
which row your hits belong to.
Please refer to this link for more info on how to do this.
http://groups.google.com/groups?selm...utp ut=gplain
Alternatively you could push all of your documents into the database.
However it is difficult to search on properties this way. For instance you
can search on documents which contain the word microsoft and are greater
than a certain date, but you have to return the entire results set from
MSSearch and then filter by date. For large row sets this can be a very
expensive operation.
Another option is to add a linked server to index server and then query it
this way, however this is not scalable.
You also get better performance by migrating your content on the LAN
locally, however the performance hit is not that significant while querying,
but is significant when indexing.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jack" <jack.fruhReplacewith@.Solcorp.com> wrote in message
news:uo8VMpKsEHA.624@.TK2MSFTNGP09.phx.gbl...
> I have a solutions database I am adding Full Text Search to.
> Unfortunately, a huge part of the "solutions" resides on a shared lan in
the
> form of attachments.
> I've used FTS before, and I've used Index server before (though never on
> files located remotely)
> Are there any resources on using both FTS and the Indexing Service
together?
> I thought I read that it was possible to run all the queries through SQL
> server and that SQL server could go out and talk to the Indexing service
> behind the sceens.
> - thanks
> Jack
> Jack.fruhatsolcorpdotcom
>