1

In SQL Server 2016, i can't remote insert with natively compiled stored procedure.

FactInventory_Hot = In-Memory Table

[10.10.10.109].AzureContosoDB.dbo.FactInventory_Hot = Remote In-Memory Table

CREATE PROCEDURE [dbo].[usp_dataInsert]
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS 
BEGIN ATOMIC WITH
    (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

insert into FactInventory_Hot
select 
   [InventoryKey]
  ,[DateKey]
  ,[StoreKey]
  ,[ProductKey]
  ,[CurrencyKey]
  ,[OnHandQuantity]
  ,[OnOrderQuantity]
  ,[SafetyStockQuantity]
  ,[UnitCost]
  ,[DaysInStock]
  ,[MinDayInStock]
  ,[MaxDayInStock]
  ,[Aging]
  ,[ETLLoadID]
  ,[LoadDate]
  ,[UpdateDate]
 from [10.10.10.109].AzureContosoDB.dbo.FactInventory_Hot
END

Here is error:

Msg 2014, Level 16, State 1, Procedure usp_dataInsert, Line 6 [Batch Start Line 0] Remote access is not allowed from within a schema-bound object.

Ugur
  • 11
  • 1
  • What's your question? If this is possible after all, somehow? The answer is no, this is not possible, and it's not hard to see why not. There is literally no point to having a procedure compiled to native for performance if you're going to do network access in it and kill that selfsame performance. Make the procedure non-native instead, it's not like it's going to be much slower. Alternatively, copy over the remote data to a local in-memory table (again, with a non-native statement) and then perform a local-to-local copy in native. – Jeroen Mostert Dec 03 '16 at 19:50

0 Answers0