Table-Valued Functions from Linked Server

Question

Why does when I try to run:

SELECT * FROM [servername].master.sys.dm_os_volume_stats(1, 1) AS btb (NOLOCK)

I get error:

Msg 195, Level 15, State 15, Line 45
'servername.master.sys.dm_os_volume_stats' is not a recognized function name.

And when I run it in the server (ex: servername), I get different error:

SELECT * FROM master.sys.dm_os_volume_stats(1, 1) AS btb (NOLOCK)
Msg 317, Level 16, State 1, Line 1
Table-valued function 'dm_os_volume_stats' cannot have a column alias.

But it gets no error if I removed the AS btb (NOLOCK)

My goal is to query from a linked server and get free disk space for each server (using union). Below is part of the query:

FROM [servername].[master].sys.master_files AS f WITH(NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)

But Im still looking for a solution for the issue regarding TVFs.

• Mark Sinkinson: Possible duplicate of Use NOLOCK hint when calling table valued function
• Mark Sinkinson: What SQL Server version is your linked server?
• Edgar Allan Bayron: im using Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Standard Edition (64-bit) on Windows NT 6.3

Answer 1

Just use a passthrough query. And get rid of NOLOCK (it doesn't even do anything here as the catalog is always read with locking read committed semantics).

EG

exec ('
select *
FROM [master].sys.master_files AS f 
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
') at [servername]

or

select * 
from openquery (servername, '
select database_id, file_id, ...
FROM [master].sys.master_files AS f 
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
') 

• Edgar Allan Bayron: can I insert the result into a view? I want to call the view on sql server agent jobs
• Edgar Allan Bayron: initially I manager to retrieve the result from a different server using OPEN QUERY and you answer. I also managed to by pass the error in my question. The next thing to do is if it is possible to have a repeated set of query for each server (using UNION). Thanks !
• Edgar Allan Bayron: *managed.. Currently I was able to manage to insert it using DECLARE .. TABLE..