Querying information_schema on another server?
-
I use queries on the information_schema.columns object to compare tables. It works fine as long as the databases are on the same server. You can preface it with the database name:
select * from database.information_schema.columns
However, I get an error when I prefix the above with a server name (the name or the ip address), like:
select * from server.database.information_schema.columns
--or--
select * from [199.55.55.55].database.information_schema.columns
Am I making a syntax error? Thanks!
smv929 -
What error are you getting?
K. Brian Kelley
@kbriankelley -
the posted syntax looks fine.
for me, the following works:
select * from mycomputer.tempdb.information_schema.columns
-
Before you issue a command like
"select * from server.database.information_schema.columns"
Have you configured "server" as a linked server?
To qualify a column in the form of "server.database.table.column" is OK as long as you configure "server" as a linked server and set security for this server
Sorry if I miss the point but you never mentioned that you have configured "server" properly.
-
I reproduced the problem and I think it's a good candidate for a SQL (or MDAC) bug report.
Steps to reproduce:
a) I used an already configured linked server (with sa permissions)
#1 SELECT * FROM [server2].[master].[INFORMATION_SCHEMA].[VIEWS]
works
#2 SELECT * FROM [server2].[Pubs].[INFORMATION_SCHEMA].[VIEWS]
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'server2' does not contain table '"pubs"."INFORMATION_SCHEMA"."VIEWS"'.
The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error:
OLE DB provider does not contain the table: ProviderName='server2', TableName='"pubs"."INFORMATION_SCHEMA"."VIEWS"'].
Accessing anything else works.
-
Hi lakusha!
I reproduce your "error" in my onw evironment and I got the same results... the only difference is that ALL Information Schema Views are unavailable through any database that is not master, not just this one...
I'm not sure yet why this happens but it seems to be a bug in sqlsrv...
-
>the only difference ...
That's what I meant. I also tried accessing views owned by other users (not DBO and not INFORMATION_SCHEMA) and it works. Also tried upgrading MDAC on both servers (just in case).
I still have the feeling that I missed something but I can't put my finger on it
In the mean time, we're back to directly accessing system tables again
Good week end everybody!
Eric
-
Aha! The Information Schema views are only in master with SQL Server 2000. You can actually see this if you use the Object Browser in Query Analyzer. Expand views under Master and there they are. Pick a user database and you won't find 'em. Microsoft's own revised language indicate a special schema exists for INFORMATION_SCHEMA within each database but that the schema is only visible in the master database.
Here's a KB article indicating the bug:
http://support.microsoft.com/default.aspx?scid=kb;en-us;294350
Since BOL (revised after SP3) only indicates a 3-part naming convention, my guess is a lnked server connection isn't going to be able to see any INFORMATION_SCHEMA views outside the server itself.
The trick is to use OPENQUERY, which runs the query on the remote server and gets back the resultset (a pass-through query). For instance:
SELECT *
FROM
OPENQUERY(Server2, 'SELECT * FROM pubs.INFORMATION_SCHEMA.VIEWS')
K. Brian Kelley
@kbriankelley -
Weird behavior...
Good to know it Brian... thanks for the info!
-
Thanks Brian!
I knew they were only in master but were usable in each db (aka sp_ procedures) but your idea with OPENQUERY is a nice workaround. Much better than our current workaround. Your explanation about 3-part naming also explains why openquery works (always like to know why :rolleyes
. I knew I was missing something 
Eric
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply