December 3, 2008 at 3:28 pm
Hello everyone,
I would like to get the column names from a table on a linked server.
My query is something like this:
Select Column_Name from ServerOne.Sales.Information_Schema.Columns
Where Table_Name like 'Mirror_History'
The error is"
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "SeaSql1" does not
contain the table ""SourceData"."Information_Schema"."columns"".
I am querying from a 64 bit EE SS2K5. ServerOne is a 32 bit SS2K EE. The query succeeds if I address a linked server that is SS2K5.
I can successfully obtain a resultset of data from a table on the SS2K server, but cannot seem to reach Information_Schema.
TIA,
Elliott
December 3, 2008 at 4:29 pm
IIRC INFORMATION_SCHEMA worked differently on SQL Server 2000, I do not believe that they worked cross-database, whic would certianly make it unlikely that they would work cross-server.
I would recommend using SYSCOLUMNS and SYSOBJECTS instead.
[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]
December 3, 2008 at 4:37 pm
Thanks.
I can see how to use sysobjects and syscolumns locally, how are they addressed cross-server?
Elliott
December 3, 2008 at 4:41 pm
Just add the server name, DB and owner name:
Select * from RemoteSrv.RemoteDB.dbo.SYSCOLUMNS
[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]
December 3, 2008 at 4:46 pm
Got it. Thanks. I didn't think that "dbo" applied to a system table.
BTW--I have several jobs that depend on dynamically creating a query to check Information_schema where the subject database is defined in run time. By using the qualified definition of Information_Schema, (MyDatabase.Information_Schema.Tables for instance), it works fine. Even across servers, at least when they are both SQL 2005.
Elliott
December 3, 2008 at 4:50 pm
Elliott Berkihiser (12/3/2008)
Got it. Thanks. I didn't think that "dbo" applied to a system table.
"dbo" applies on SQL 2000. On 2005, most system objects are now "sys".
Glad I could help.
[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]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply