question

chillw1nston avatar image
chillw1nston asked

querying dmv over linked server

Hi All Is anyone aware of a workaround to be able to query dm_db_index_physical_stats over linked servers. I have played with the linked server settings and RPC and DTC service. Basically i am trying to run similar to SELECT * FROM [ ].[ ].[sys].[dm_db_index_physical_stats](NULL,NULL,NULL,NULL,NULL ) and i get an error of 'Remote table-valued function calls are not allowed.' thanks Ben
linked-serverdmv
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
What if you create a temporary table on the linked server, and get your code to insert the data into that and then fetch it back? Sorry - a bit vague. Don't have the kit here to try that out...
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks guys but the problem is that you cant execute the query against the dmv in the first place, i have tried openquery, execute sql sproc and all sorts but as soon as you reference a dmv it fails!
0 Likes 0 ·
+1 I would actually prefer something like this in production.....
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
not ideal but try OPENQUERY select * from openquery([81.145.148.27], 'select * from [sys].dm_db_index_physical_stats(.......)')
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@chillw1nston this worked against a linked server I have here - maybe your linked server security options need checking
1 Like 1 ·
Much better than my idea...
0 Likes 0 ·
Thanks guys but the problem is that you cant execute the query against the dmv in the first place, i have tried openquery, execute sql sproc and all sorts but as soon as you reference a dmv it fails!
0 Likes 0 ·
@chillw1nston Do you have a nolock hint anywhere in the query? Here us what I found on [connect][1]:
When issuing a select that has uses four part naming to address the table and the table has a (nolock) hint, the t-sql will fail to execute with the error "Remote table-valued function calls are not allowed"
The only resolution they offer is this:
To get the query to work using 4 part naming you have to put the "with" keyword before the (nolock)
There is [another article][2] which says that UDFs still cannot be called with 4-part name, and the only help they offer is this:
Hi, Thanks for your request. We are aware of the restrictions on UDFs with linked server / distributed query. We are looking at relaxing this in a future version of SQL Server.
[1]: http://connect.microsoft.com/SQLServer/feedback/details/126162/remote-table-valued-function-calls-are-not-allowed [2]: http://connect.microsoft.com/SQLServer/feedback/details/485517/udf-calls-are-not-allowed-over-linked-servers
0 Likes 0 ·
Thanks for your help, i will some other ways of doing this, i did hope openquery would work as it seems like a work around for this problem. If i find a solution I will post here. Are dmvs essentially just udfs then?
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.