INFORMATION_SCHEMA.COLUMNS reference

Question

INFORMATION_SCHEMA.COLUMNS reference

levsha_z

SSC Eights!

Points: 862
More actions
August 18, 2008 at 1:24 pm

#191317

How do you reference INFORMATION_SCHEMA.COLUMNS
from a linked server?
When I try doing that, I get the below error message:
select * from [server_2000].dbst.information_schema.columns
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "server_2000" does not contain the table ""dbst"."information_schema"."columns"". The table either does not exist or the current user does not have permissions on that table.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You need to include the user accessing the view across the linked server rights to the database and the view. A basic user does not have access to INFORMATION_SCHEMA.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

levsha_z SSC Eights! Points: 862 More actions · Answer 2

levsha_z

SSC Eights!

Points: 862

August 18, 2008 at 2:03 pm

#859659

Thank you!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Note that this is different on SQL Server 2005 than it is on SQL Server 2000. You would probably not have the same problem on a remote linked 2005 server.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

levsha_z SSC Eights! Points: 862 More actions · Answer 4

I've ended up using syscolumns, which creates no problem accessing it.

Thank you!

Matt Slocum Default port Points: 1497 More actions · Answer 5

I know this is an old post, but it helped me to generate the following script that works against a SQL 2000 linked server (Update DBServerName, DBName, and TableName appropriately):

SELECT SC.name FROM DBServerName.DBName.dbo.sysobjects SO

JOIN DBServerName.DBName.dbo.syscolumns SC ON SO.id = SC.id

WHERE SO.Name = 'TableName'

AND SO.Type = 'U'

ORDER BY SC.Name

Thanks!

Matt

MMartin1 One Orange Chip Points: 27795 More actions · Answer 6

INFORMATION_SCHEMA columns do not have exactly the same information as sys.columns, but it may work depending on the need. Overall, sql server 2000 just does not have all of the system views in 2005 and up.

----------------------------------------------------