sp_configure 'external scripts enabled', 1;

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!
Kanika SoniAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
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!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Kanika SoniAuthor Commented:
Thanks Pawan, solution worked
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Pawan KumarDatabase ExpertCommented:
Glad to help!
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
Kanika SoniAuthor Commented:
"Hello Vitor, I am putting Go after  sp_configure 'external scripts enabled', 1; " and not restarting the server is the problem."
Vitor MontalvãoMSSQL Senior EngineerCommented:
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"
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.