Search code examples
sqlsql-serverlinked-server

SQL Server : Linked Server row physical location

I can query the physical location of a row in Microsoft SQL Server by %%physloc%% identifier. For example like this

select %%physloc%% from [testdb].[dbo].[table1_t4];

I have created a linked server of this SQL Server instance with dbserv instance. I can query the row of this instance like:

select * from [dbserv].[testdb].[dbo].[table1_t4];

But when I query the physical location of the rows 

select %%physloc%% from [dbserv].[testdb].[dbo].[table1_t4];

This throws the error:

Invalid column name '%%'.

How do I workaround by obtaining the physical location of the row of the linked server for my application?

Solution

  • Linked servers are all connected through OLE DB Providers like ODBC. While the "Virtual Column %%phys_loc%%" is an undocumented feature in SQL Server 2008. As such, OLE DB providers (barring the SQL Server native driver) aren't likely to support it, but I can't seem to find the documentation for it specifically.

    Testing it out here, it also looks like it will only work when connected to the server locally, otherwise I get the same error you post here. Quoting the identifier throws an invalid column name error.

    I don't believe you will be able to query this identifier through linked server at all.