R Plot Rendering SQL Server 2016 Using sp_execute_external_script

Question

Has anyone figured out how to render a plot using sp_execute_external_script where the plot is returned in the SQL Management Studio result window or the message window or a popup window? I was able to output a plot to the file system but that is not ideal.

Below is how I got a plot to output to the file system.

EXECUTE  sp_execute_external_script
                @language = N'R'
              , @script = N' trellis.device(device="png", filename="c:/output/HistPlot.png");
              myHist=histogram(~col1, data=InputDataSet);
              print(myHist)'
              , @input_data_1 = N'SELECT  col1, col2 from ds.dbo.[sample]'

Management Studio is a database client and not designed or intended to run graphics. Import data into R with your stored procedure then run plot in R, the end use tool. — Parfait
– Parfait, Commented Nov 7, 2015 at 3:48
Additionally, SQL is a special-purpose language for database retrieval and management needs. It is not a general purpose language to run filesystem and command line processes. — Parfait
– Parfait, Commented Nov 7, 2015 at 3:50
Thank you for your feedback Parfait. I ask the question because all over MSDN regarding SQL Server R Services they explicitly talk about using SQL Server to run R scripts and return plots to embed in applications. They even provide a flow showing nothing but SQL Server and an app where that app is displaying R results from SQL Server. link — ansek
– ansek, Commented Nov 8, 2015 at 10:55

Gustavo Morales · Accepted Answer · 2016-06-06 19:38:24Z

5

Until Microsoft provide visualization capability, you could use SSMSBoost which is a free utility that could solve your problem.

Once installed, please highlight the cell with the binary picture to be read. Right click and select Visualize as and then choose picture.

Right click and select Visualize as and then choose picture

The image is going to be displayed into Paint:

edited Jun 6, 2016 at 19:38

Gustavo Morales

2,6709 gold badges34 silver badges40 bronze badges

answered Jun 6, 2016 at 17:57

Cyrille Tijsseling

861 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ansek Over a year ago

I finally got the latest release installed and this is exactly what i seek! Thank you!

Umachandar - Microsoft · Accepted Answer · 2015-11-09 19:02:31Z

4

We are looking at providing visualization capability in SSMS for graphics returned from R script. But for now you have to open the image in an application that can display it (SSRS, Internet Explorer) and so on. MSDN has samples that show how to use the plots in SSRS. You can find the samples here.

answered Nov 9, 2015 at 19:02

Umachandar - Microsoft

4843 silver badges5 bronze badges

2 Comments

Minu Over a year ago

Your link takes me to the AdventureWorks Database download page. Can you please update it?

Dima Fomin Over a year ago

here mssqltips.com/sqlservertip/4127/…

Ust Oldfield · Accepted Answer · 2015-11-09 10:38:37Z

2

You probably want to use SSRS to display output in a plot. SSMS isn't designed to display graphics. You can display the output of a stored procedure in SSRS and the recent additions to SSRS in CTP3 means that you can produce some really nice looking charts.

answered Nov 9, 2015 at 10:38

Ust Oldfield

211 bronze badge

2 Comments

Minu Over a year ago

I'm using SQL Server 2016 with R Services. I created a stored procedure for a histogram, but cannot view it. Can you help me understand how to call / display the output of a stored procedure in SSRS? Directing towards any documentation / tutorial will also be helpful.

Dima Fomin Over a year ago

There small tutorial how to visualize R plot saved as binary to table in SSRS mssqltips.com/sqlservertip/4127/…

Collectives™ on Stack Overflow

R Plot Rendering SQL Server 2016 Using sp_execute_external_script

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related