SQL Server – Table-Valued Functions from Linked Server

functionslinked-serversql serversql server 2014

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.

Best Answer

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).

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

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)
')

Related Solutions

Sql-server – Getting error when trying to use OPENQUERY Msg 7321, Level 16, State 2, Line 1

I would suggest you change your original SQL Statement as follows:

SELECT * 
FROM OPENQUERY([XXX], 'SELECT * FROM database.table where left(MBCUNO, 1) = ''a'' and (MBCONO=650) ' )

This has the two variable swopped around, and the like % exchanged for a left(mbcuno,1).

Ensure that all your table & column references are correct

SQL Server Linked Servers – Limitation of 10 Branches in CASE Expression

Clearly there isn't a nested CASE expression here.

Not in the query text, no. But the parser always expands CASE expressions to the nested form:

SELECT CASE SUBSTRING(p.Name, 1, 1)
        WHEN 'a' THEN '1' 
        WHEN 'b' THEN '2' 
        WHEN 'c' THEN '3' 
        WHEN 'd' THEN '4' 
        WHEN 'e' THEN '5' 
        WHEN 'f' THEN '6' 
        WHEN 'g' THEN '7' 
        WHEN 'h' THEN '8' 
        WHEN 'i' THEN '9' 
        WHEN 'j' THEN '10' 
        WHEN 'k' THEN '11'  
    END
FROM AdventureWorks2012.Production.Product AS p

Local query plan

That query is local (no linked server) and the Compute Scalar defines the following expression:

Nested CASE expression

This is fine when executed locally, because the parser does not see a nested CASE statement over 10 levels deep (though it does pass one on to the later stages of local query compilation).

However, with a linked server, the generated text may be sent to the remote server for compilation. If that is the case, the remote parser sees a nested CASE statement more than 10 levels deep and you get error 8180.

Another oddity. This inline table-valued function produces the same error

The in-line function is expanded in-place into the original query text, so it's no surprise the same error results with the linked server.

But a similar multi-statement TVF works fine

Similar, but not the same. The msTVF involves an implicit conversion to varchar(max), which happens to prevent the CASE expression being sent to the remote server. Because the CASE is evaluated locally, a parser never sees an over-nested CASE and there is no error. If you change the table definition from varchar(max) to the implicit type of the CASE result - varchar(2) - the expression is remoted with the msTVF and you will get an error.

Ultimately, the error occurs when an over-nested CASE is evaluated by the remote server. If the CASE is not evaluated in the Remote Query iterator, no error results. For example, the following includes a CONVERT that is not remoted, so no error occurs even though a linked server is used:

SELECT CASE CONVERT(varchar(max), SUBSTRING(p.Name, 1, 1))
        WHEN 'a' THEN '1' 
        WHEN 'b' THEN '2' 
        WHEN 'c' THEN '3' 
        WHEN 'd' THEN '4' 
        WHEN 'e' THEN '5' 
        WHEN 'f' THEN '6' 
        WHEN 'g' THEN '7' 
        WHEN 'h' THEN '8' 
        WHEN 'i' THEN '9' 
        WHEN 'j' THEN '10' 
        WHEN 'k' THEN '11'  
    END
FROM SQL2K8R2.AdventureWorks.Production.Product AS p

CASE not remoted

Best Answer

Related Solutions

Sql-server – Getting error when trying to use OPENQUERY Msg 7321, Level 16, State 2, Line 1

SQL Server Linked Servers – Limitation of 10 Branches in CASE Expression

Related Question