Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

INFORMATION_SCHEMA.COLUMNS reference Expand / Collapse
Author
Message
Posted Monday, August 18, 2008 1:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 04, 2008 9:52 AM
Points: 28, 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.
Post #554543
Posted Monday, August 18, 2008 1:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 10,910, Visits: 12,547
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

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
Post #554560
Posted Monday, August 18, 2008 2:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 04, 2008 9:52 AM
Points: 28, Visits: 68
Thank you!
Post #554579
Posted Monday, August 18, 2008 2:30 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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."
Post #554599
Posted Tuesday, August 19, 2008 8:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 04, 2008 9:52 AM
Points: 28, Visits: 68
I've ended up using syscolumns, which creates no problem accessing it.

Thank you!
Post #555038
Posted Thursday, January 31, 2013 7:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 12:45 PM
Points: 415, Visits: 235
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
Post #1414136
Posted Wednesday, April 17, 2013 10:58 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 10, 2014 6:20 PM
Points: 220, Visits: 695
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.
Post #1443387
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse