INFORMATION_SCHEMA on another database

I want to get the max lenght of a column on a table in anther database. I
have a linked server pointing to that database. INFORMATION_SCHEMA will not
work as it reports only works on the current database.
How can I do this.
Thanks
kevinJust tried this on my server, using a four-part name to reference the table:
SELECT linked_srv.catalog.information_schema.[columns]
Worked fine. Is the other database a SQL server db?
"kevin" wrote:

> I want to get the max lenght of a column on a table in anther database. I
> have a linked server pointing to that database. INFORMATION_SCHEMA will n
ot
> work as it reports only works on the current database.
> How can I do this.
> Thanks
> kevin|||Perhaps you can use stored procedures like below for the remote server?
sp_catalogs
sp_linkedservers
sp_indexes
sp_primarykeys
sp_foreignkeys
sp_tables_ex
sp_columns_ex
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"kevin" <kevin@.discussions.microsoft.com> wrote in message
news:AAB87C6E-97BF-4039-A93D-F072284EC25E@.microsoft.com...
>I want to get the max lenght of a column on a table in anther database. I
> have a linked server pointing to that database. INFORMATION_SCHEMA will n
ot
> work as it reports only works on the current database.
> How can I do this.
> Thanks
> kevin|||Mark;
when connected to MyLocalServer, if I execute this:
SELECT *
from MyLinkedServer.MyLinkedDB.information_schema.[columns]
I get
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'MyLinkedServer' does not contain table
'"MyLinkedDB"."information_schema"."columns"'. The table either does not
exist or the current user does not have permissions on that table.
from MyLinkedServer I get what I expect.
I know that the linked server is set up properly because I have SP's running
.
The linked server is using
"Mark Williams" wrote:
> Just tried this on my server, using a four-part name to reference the tabl
e:
> SELECT linked_srv.catalog.information_schema.[columns]
> Worked fine. Is the other database a SQL server db?
> "kevin" wrote:
>|||In 2000, the info schema views only exists physically in the master database
, which is most likely
why you get this error. Use the system tables or the system stored procedur
es I posted in the other
post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"kevin" <kevin@.discussions.microsoft.com> wrote in message
news:1E532DC4-6127-433D-972A-1C0E134AA9F2@.microsoft.com...
> Mark;
> when connected to MyLocalServer, if I execute this:
> SELECT *
> from MyLinkedServer.MyLinkedDB.information_schema.[columns]
> I get
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'MyLinkedServer' does not contain table
> '"MyLinkedDB"."information_schema"."columns"'. The table either does not
> exist or the current user does not have permissions on that table.
> from MyLinkedServer I get what I expect.
> I know that the linked server is set up properly because I have SP's runni
ng.
> The linked server is using
> "Mark Williams" wrote:
>

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

INFORMATION SCHEMA And LInked Server

I know I've done this before and I can't find, and it's kinda of foggy here today...

anyone see anything with this

EXEC sp_addlinkedserver
'PAERSCBVD0045',
N'SQL Server'

Connect as X002548 since logins are on both serves

*/

-- Tables in 14 not in 45

SELECT S14.TABLE_NAME
FROM INFORMATION_SCHEMA.Tables S14
LEFT JOIN INFORMATION_SCHEMA.PAERSCBVD0045.dbo.MEP.Tables S45
ON S14.TABLE_SCHEMA = S45.TABLE_SCHEMA
AND S14.TABLE_NAME = S45.TABLE_NAME
WHERE S45.TABLE_SCHEMA = IS NULL
AND S45.TABLE_NAME = IS NULLOK, syntax is

SELECT *
FROM PAERSCBVD0045.MEP.INFORMATION_SCHEMA.Tables S45

But now I get

Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

And I know the put me in as windows login as sa

I tried

EXEC sp_addlinkedsrvlogin 'X002548'

But got

Server: Msg 823, Level 24, State 2, Procedure sp_addlinkedsrvlogin, Line 1
I/O error (torn page) detected during read at offset 0x00000000786000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf'.

Connection Broken

Which doesn't look good, so I did a checkdb on the linked server and got no errors

Maybe masters got a problem on mine

Wonder if it's a collation thing|||Ah, OK, Not sure if this is the problem, but master on my box has consistencey errors

Server: Msg 8928, Level 16, State 1, Line 1
Object ID 6, index ID 0: Page (1:963) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 6, index ID 0, page (1:963). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
DBCC results for 'master'.
DBCC results for 'sysobjects'.
There are 1269 rows in 23 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 104 rows in 4 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 4851 rows in 84 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 2011 rows in 945 pages for object 'syscomments'.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'syscomments' (object ID 6).|||OK, more succienct messages

Server: Msg 8928, Level 16, State 1, Line 1
Object ID 6, index ID 0: Page (1:963) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 6, index ID 0, page (1:963). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'syscomments' (object ID 6).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'master'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (master ).

It appears to be in syscomments. Great lo these many years and finally a system problem. Can I fix a system table?|||Sure you can. With a master rebuild, of course. And you do have a backup around, don't you? ;-)|||Of course. I found the earliest and made a copy and saved it locally

But I don't think that's really causing my linked server problem, but this is the first time I've ever seen this

And these are more like warning no?

Maybe I should bounce the box and see what happens

Indexing Service linked server

How can I setup a linked Indexing Service that is running on a remote machine (not on the same sqlserver machine).
We need to be able to link Indexing Service servers remotely from the sqlserver.
Thanks
M. Castellanos
you can't. Create a linked server to the local indexing service and then
query the catalog on the remote server like this
select * from openquery(LocalLinkedServer,'Select DocTitle, vpath, size,
create from RemoteServerName.RemoteCatalogName..Scope() where
contains(''test'')')
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"MC" <MC@.discussions.microsoft.com> wrote in message
news:DE637C5B-1EEB-4A31-8F11-0169F663A7A1@.microsoft.com...
> How can I setup a linked Indexing Service that is running on a remote
machine (not on the same sqlserver machine).
> We need to be able to link Indexing Service servers remotely from the
sqlserver.
> Thanks
> --
> M. Castellanos