0

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.

Geekn
  • 2,650
  • 5
  • 40
  • 80

1 Answers1

0

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'
Martin Cairney
  • 1,714
  • 1
  • 6
  • 18
  • I started using the function because I was unable to create temp or table variables in a view that would be necessary to return results back from a stored procedure. So then I tried the function knowing I could at least create variables and stuff, but then ran across the stored procedure point you mentioned. That's where I kind up threw my hands up and posted this in stack. I was trying to make the example simple, but may have made it more confusing. The real question I'm trying to get help with is how would you access a view on a remote Azure SQL Server? – Geekn Oct 04 '20 at 16:12
  • I just realized something...I can create an EXTERNAL TABLE on a VIEW. Should have tried that. – Geekn Oct 04 '20 at 16:22
  • Also, a Stored Procedure can return results without a View - it is simply the final SELECT statement in the Stored Procedure. INSERT INTO ... EXEC proc is a valid construct – Martin Cairney Oct 05 '20 at 21:51