Unable to select from function

Question

Unable to select from function

backintheday

SSC Rookie

Points: 38
More actions
March 12, 2016 at 12:47 pm

#305630

I am unable to select from the following function - even though it looks like I'm doing everyting correctly and trying two different methods. The function compiles successfully.
ALTER FUNCTION dbinv.ufn_Get_Linked_Servers ()
RETURNS @tab_Linked_Servers TABLE (
srv_name SYSNAME
, srv_procedurename NVARCHAR(128)
, srv_product NVARCHAR(128)
, srv_datasource NVARCHAR(4000)
, srv_providerstring NVARCHAR(4000)
, srv_location NVARCHAR(4000)
, srv_cat SYSNAME)
AS
BEGIN
-- Method 1
--DECLARE @sql VARCHAR(4000);
--SET @sql = 'INSERT INTO @tab_Linked_Servers EXEC sp_linkedservers';
--EXEC sp_executesql @sql;
--RETURN;
-- Method 2
EXEC sp_linkedservers;
INSERT @tab_Linked_Servers
SELECT * FROM @tab_Linked_Servers;
RETURN;
END;
GO
SELECT * FROM dbinv.ufn_Get_Linked_Servers();
Either method returns "Only functions and some extended stored procedures can be executed from within a function."

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

sgreiner (3/12/2016)
Either method returns "Only functions and some extended stored procedures can be executed from within a function."

And that's exactly the problem. A lot of things are not allowed in user-defined functions, and running sp_linkedservers is one of them.

What exactly are you trying to achieve? Why do you want to do this in a user-defined function? Perhaps you should instead query sys.servers directly?

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

sgreiner (3/12/2016)
I am unable to select from the following function - even though it looks like I'm doing everyting correctly and trying two different methods. The function compiles successfully.
ALTER FUNCTION dbinv.ufn_Get_Linked_Servers ()
RETURNS @tab_Linked_Servers TABLE (
srv_name SYSNAME
, srv_procedurename NVARCHAR(128)
, srv_product NVARCHAR(128)
, srv_datasource NVARCHAR(4000)
, srv_providerstring NVARCHAR(4000)
, srv_location NVARCHAR(4000)
, srv_cat SYSNAME)
AS
BEGIN
-- Method 1
--DECLARE @sql VARCHAR(4000);
--SET @sql = 'INSERT INTO @tab_Linked_Servers EXEC sp_linkedservers';
--EXEC sp_executesql @sql;
--RETURN;
-- Method 2
EXEC sp_linkedservers;
INSERT @tab_Linked_Servers
SELECT * FROM @tab_Linked_Servers;
RETURN;
END;
GO
SELECT * FROM dbinv.ufn_Get_Linked_Servers();
Either method returns "Only functions and some extended stored procedures can be executed from within a function."

Hugo is spot on. A lot of things aren't allowed in UDFs. And it would be really cool if they did allow certain things in functions to make extracting results from stored procedures a whole lot easier. SQL Server DOES support INSERT/EXEC but you also have to pre-create the target table, etc, etc.

There's is, however, a trick that most people frown on and can get you into trouble in certain areas but I don't believe you need to do that trick here because the code is so bloody simple.

So, here's the simple code to use instead of a function.

SELECT RowNum = ROW_NUMBER() OVER (ORDER BY (SRV_Name))

,*

FROM OPENROWSET('SQLOLEDB','Trusted_Connection=YES','SET FmtOnly OFF; EXEC sp_linkedservers')

;

If you need to store the result into a table, the simple addition of an INTO will do the trick...

SELECT RowNum = ROW_NUMBER() OVER (ORDER BY (SRV_Name))

,*

INTO #LinkedServer

FROM OPENROWSET('SQLOLEDB','Trusted_Connection=YES','SET FmtOnly OFF; EXEC sp_linkedservers')

;

And, yes... you can add a WHERE clause, ORDER BY, or what have you to the outer SELECT just as if you were querying from a table.

Do be advised that OPENROWSET requires a certain configuration to work as noted in Books Online...

Remarks
OPENROWSET can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled. When these options are not set, the default behavior does not allow for ad hoc access.

If you can't use OPENROWSET for one reason or another, then you can still define the table and then do an INSERT/EXEC to load the table with the results of the stored procedure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Danish Dynamo SSC-Addicted Points: 464 More actions · Answer 3

Here's a solution without all the if's and butt's

-- 1) Get the code from sp_linkedservers

sp_helptext sp_linkedservers

-- 2) Copy the code into inline table valued udf

CREATE FUNCTION dbinv.ufn_Get_Linked_Servers ( )

RETURNS table

AS

RETURN

( SELECT TOP 100 PERCENT

SRV_NAME = srv.name

,SRV_PROVIDERNAME = srv.provider

,SRV_PRODUCT = srv.product

,SRV_DATASOURCE = srv.data_source

,SRV_PROVIDERSTRING = srv.provider_string

,SRV_LOCATION = srv.location

,SRV_CAT = srv.catalog

FROM sys.servers srv

ORDER BY 1

)

GO

-- 3) try it out

SELECT * FROM dbinv.ufn_Get_Linked_Servers()

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

The Danish Dynamo (3/14/2016)
Here's a solution without all the if's and butt's
-- 1) Get the code from sp_linkedservers
sp_helptext sp_linkedservers
-- 2) Copy the code into inline table valued udf
CREATE FUNCTION dbinv.ufn_Get_Linked_Servers ( )
RETURNS table
AS
RETURN
( SELECT TOP 100 PERCENT
SRV_NAME = srv.name
,SRV_PROVIDERNAME = srv.provider
,SRV_PRODUCT = srv.product
,SRV_DATASOURCE = srv.data_source
,SRV_PROVIDERSTRING = srv.provider_string
,SRV_LOCATION = srv.location
,SRV_CAT = srv.catalog
FROM sys.servers srv
ORDER BY 1
)
GO
-- 3) try it out
SELECT * FROM dbinv.ufn_Get_Linked_Servers()

Love the alternative. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)