How to create a view where data resides in a view on remote Azure SQL Server

Question

I'm trying to create a view of data that resides in remote Azure SQL Server. I can't seem to create a table or temp table in the view to store the results of the sp_exeucte_remote call because that is not allowed so I tried to use a function, but then I get an error complaining about the following error based on the provided function definition.

Invalid use of a side-effecting operator 'INSERT EXEC' within a function

CREATE OR ALTER FUNCTION [dbo].[fn_Test]()
RETURNS @Results TABLE
(
    [ID] INT,
    [$ShardName] VARCHAR(500)
)
AS
BEGIN
    INSERT INTO @Results
    EXEC sp_execute_remote N'MyExternalDatasource', N'SELECT 1' 
    RETURN;
END

How can one create a view of data that exists on a remote Azure SQL Server where that data also exists as a view? FYI - the server where I'm trying to create the view is also Azure SQL Server.

Answer 1

Why are you using a FUNCTION?

As per Microsoft Documentation you cannot call a Stored Procedure within a function.

Also, your remote execution is only returning 1 column and you haven't defined the destination column to insert to.

The fact that the remote object is a VIEW does not matter. If we assume that the VIEW on the remote database has columns also named ID and [$shardname] then why not just use something like:

CREATE TABLE #results ([ID] int, [$shardname] varchar(500))
INSERT #results ([ID], [$shardname])
EXEC sp_execute_remote N'ExternalSource', N'SELECT [ID], [$shardname] FROM RemoteTableName'