0

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.

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)
') 
  • can I insert the result into a view? I want to call the view on sql server agent jobs – Edgar Allan Bayron Dec 19 '17 at 14:57
  • 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 Dec 19 '17 at 15:12
  • *managed.. Currently I was able to manage to insert it using DECLARE .. TABLE.. – Edgar Allan Bayron Dec 19 '17 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.