MicrosoftRServer逐行插入-MicrosoftRServerRowbyRowInsert

4  

Here you go. You don't need to set the compute context to SQL Server. But you do have to grant login permissions to the local users running the R external processes. They are all added to a local group called SqlRUserGroup, you just need to replace 'dbrownebook' with your server name.

干得好。您不需要将计算上下文设置为SQL Server。但您必须向运行R外部进程的本地用户授予登录权限。它们都被添加到名为SqlRUserGroup的本地组中，您只需要将'dbrownebook'替换为您的服务器名称。

Note that you don't add a database user for the sqlrusergroup, but only add a login. SQL R Services will impersonate the user calling sp_execute_external_script. This is explained in: https://docs.microsoft.com/en-us/sql/advanced-analytics/r/security-considerations-for-the-r-runtime-in-sql-server

请注意，您不为sqlrusergroup添加数据库用户，而只是添加登录名。 SQL R服务将模拟调用sp_execute_external_script的用户。解释如下：https：//docs.microsoft.com/en-us/sql/advanced-analytics/r/security-considerations-for-the-r-runtime-in-sql-server

use master
go

create login [dbrownebook\sqlrusergroup] from windows

create database [External]

go

use [External]
go

create schema imp
go
create table imp.USA_Cities_Map(test_insert int)
go


/****** Object:  StoredProcedure [dbo].[Rscript_geocodeUSACities_TEST]    Script Date: 8/8/2017 11:40:40 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE OR ALTER PROCEDURE [dbo].[Rscript_geocodeUSACities_TEST]
    @usrOutputFilePath varchar(150)
    ,@usrOutputFileName varchar(150)

AS
BEGIN

    SET NOCOUNT ON;

DECLARE @rScript nvarchar(max) = N'

sqlServerConnString <- "Server=.;Database=External;Trusted_COnnection=true"
sqlTable <- RxSqlServerData(table = "imp.USA_Cities_Map", cOnnectionString= sqlServerConnString)

rxDataStep(data=dfInputData,outFile=sqlTable,append="rows")
rxDataStep(data=dfInputData,outFile=sqlTable,append="rows")
rxDataStep(data=dfInputData,outFile=sqlTable,append="rows")

'

EXECUTE  sp_execute_external_script
                @language = N'R'
              , @script = @rScript
              ,@input_data_1 =N'select 5 as test_insert'
            ,@input_data_1_name =N'dfInputData'
              ;

END

GO

exec [Rscript_geocodeUSACities_TEST] '',''

go
select * from imp.USA_Cities_Map