Linked Server: Table/Columns missing from sys.columns

Question

Post reply

Linked Server: Table/Columns missing from sys.columns

Treehouse

Ten Centuries

Points: 1234
More actions
May 5, 2010 at 3:20 pm

#234660

I have set up a linked server (using the sa account on the linked server) . All appears to be functioning normally.
I can run:
[font="Courier New"]SELECT * FROM MyServer.MyDB.dbo.MyTableName[/font]
and data is returned.
I can run:
[font="Courier New"]SELECT * FROM MyServer.MyDB.Information_Schema.Columns WHERE table_name = 'MyTableName'[/font]
and all the table's columns are returned
But if I run:
[font="Courier New"]SELECT * FROM MyServer.MyDB.sys.columns WHERE object_name(object_id) = 'MyTableName'[/font]
No rows are returned.
- Querying [font="Courier New"]MyServer.MyDB.sys.columns[/font] in this manner for any other table works fine and the correct rows are returned.
- If I run the same query against [font="Courier New"]sys.columns [/font]from a non-linked DB connection to the same server, it works fine.
I am trying to determine what it is about this table that is revealing this peculiar behavior. Quite frankly, I am baffled, but it's been a looong day and I am hoping it's something simple!

Viewing 4 posts - 1 through 3 (of 3 total)

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

Treehouse Ten Centuries Points: 1234 More actions · Answer 1

The plot thickens, a little.

I created a view on the server:

[font="Courier New"]CREATE VIEW MyView AS

SELECT * FROM sys.columns WHERE object_name(object_id) = 'MyTableName'

GO[/font]

If I then select from this view via the linked server, it returns the correct data:

[font="Courier New"]SELECT * FROM MyServer.MyDB.MyView[/font]

It almost seems like its some sort of schema/permissions issue....?

happycat59 One Orange Chip Points: 29389 More actions · Answer 2

The OBJECT_NAME function is running against the database where the query was run from. In the case of you first usage (SELECT * FROM MyServer.MyDB.sys.columns WHERE object_name(object_id) = 'MyTableName') the function is running on the local server NOT on server "MyServer". The query is effectively

- SELECT * FROM MyServer.MyDB.sys.columns

- the results are returned to you local server (including the object_ID)

- the WHERE condition is then evaluated. If there happened to be an object in the database you are connected to and that object happened be called "MyTableName" then you would get a record returned

When you are using the view, the entire query is actually run on the remote server (i.e. "MyServer") not on your local server. The result of executing the SELECT * FROM MyView is returned to the local server

Hope my explanation makes senese

Treehouse Ten Centuries Points: 1234 More actions · Answer 3

Ah, happycat, that makes perfect sense. Looks like the object_id's for all my other tables were in sync between the two databases - but the object_id for the one table in question was different.

Thank you for the explanation!