sp_configure 'external scripts enabled', 1;

Kanika Soni
Kanika Soni used Ask the Experts™
on
I am trying to execute external script in SQL server 2016. For that the following script executed

sp_configure 'external scripts enabled', 1;
GO
reconfigure;
GO

Then I restarted the service.

However, after executing following script run_value is still 0

code trying to run is..

EXEC sp_execute_external_script
      @language = N'R'
     ,@script = N'      res <-quantile(InputDataSet$Ages);
                  print(res)'
     ,@input_data_1 = N'SELECT Ages = DATEDIFF(YEAR,[BirthDate],GETDATE())
                        FROM [AdventureWorksDW2016CTP3].[dbo].[DimCustomer];'
;

Please help!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Expert
Awarded 2016
Top Expert 2016
Commented:
Hi Kanika,
Please try this.

<R language from SQL Server ?>

--Step 1

--
EXEC SP_CONFIGURE 'external scripts enabled', 1;
RECONFIGURE;
--

Open in new window


--Step 2


RESTART your server


then try this--

--

EXEC sp_execute_external_script
      @language = N'R'
     ,@script = N'      res <-quantile(InputDataSet$Ages);
                  print(res)'
     ,@input_data_1 = N'SELECT Ages = DATEDIFF(YEAR,[BirthDate],GETDATE())
                        FROM [AdventureWorksDW2016CTP3].[dbo].[DimCustomer];'
;
--

Open in new window


Hope it helps!
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Do you have data in [AdventureWorksDW2016CTP3].[dbo].[DimCustomer] table?
If so can you share the result for the query?
SELECT DATEDIFF(YEAR,[BirthDate],GETDATE())
FROM [AdventureWorksDW2016CTP3].[dbo].[DimCustomer]

Open in new window

Author

Commented:
Thanks Pawan, solution worked
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Glad to help!
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Weird that you chose a comment that is exactly what you said in your question:
I am trying to execute external script in SQL server 2016. For that the following script executed

 sp_configure 'external scripts enabled', 1;
 GO
 reconfigure;
 GO

 Then I restarted the service.

 However, after executing following script run_value is still 0

 code trying to run is..

 EXEC sp_execute_external_script
       @language = N'R'
      ,@script = N'      res <-quantile(InputDataSet$Ages);
                   print(res)'
      ,@input_data_1 = N'SELECT Ages = DATEDIFF(YEAR,[BirthDate],GETDATE())
                         FROM [AdventureWorksDW2016CTP3].[dbo].[DimCustomer];'
Can you tell what was the difference from the comment you choose as solution?

Author

Commented:
"Hello Vitor, I am putting Go after  sp_configure 'external scripts enabled', 1; " and not restarting the server is the problem."
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Ok but you told that you did restart the service:
"Then I restarted the service."

Anyway if you didn't restart the service the behavior wouldn't be return 0 but an error.
"However, after executing following script run_value is still 0"

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial