SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


INFORMATION_SCHEMA.COLUMNS reference


INFORMATION_SCHEMA.COLUMNS reference

Author
Message
levsha_z
levsha_z
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 68
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.
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44707 Visits: 14925
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
levsha_z
levsha_z
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 68
Thank you!
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35024 Visits: 9518
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
levsha_z
levsha_z
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 68
I've ended up using syscolumns, which creates no problem accessing it.

Thank you!
Matt Slocum
Matt Slocum
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 Visits: 277
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
MMartin1
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7233 Visits: 2033
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.

----------------------------------------------------
How to post forum questions to get the best help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search