<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/saving-input-and-output-with-sp_execute_external_script/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://tomaztsql.wordpress.com/2017/04/10/saving-input-and-output-with-sp_execute_external_script/"> R – TomazTsql</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>Again I was at the point, where I needed to store and save to external file all the R code that was executed through sp_execute_external_script.</p>
<p>Soon, you will find out several interesting things. To show the example, I will start with following example:</p>
<pre>USE [WideWorldImporters];
GO

EXEC sys.sp_execute_external_script
     @language = N'R'
    ,@script = N'
       d &lt;- InputDataSet 
       c &lt;- data.frame(Num_V1 = c(1,2,3))
       c
       OutputDataSet &lt;- c'
    ,@input_data_1 = N'SELECT 1 AS Nmbrs_From_R'

WITH RESULT SETS ((Numbers_From_R INT));</pre>
<p>The result is a column called “Numbers” with three rows, represented from the data frame. This is very easy and straight-forward.</p>
<h4>DMV</h4>
<p>By using dynamic management view <em>sys.dm_exec_query_stats</em> as following:</p>
<pre>SELECT
     QM_ST.[TEXT] AS [Query]
    ,DM_QS.last_execution_time
    ,DM_QS.query_hash
    ,DM_QS.query_plan_hash
 FROM 
    sys.dm_exec_query_stats AS DM_QS
    CROSS APPLY sys.dm_exec_sql_text(DM_QS.sql_handle) AS QM_ST
ORDER BY 
    DM_QS.last_execution_time DESC</pre>
<p>Surprisingly I get only the following query returned:</p>
<pre>sp_execute_external_script: SELECT 1 AS Nmbrs_From_R</pre>
<p>which is far what was executed in the first place!</p>
<h4>EXECUTION PLANS</h4>
<p>When using <em>sys.dm_exec_query_plan</em> dynamic management view to generate executed query plan, I get similar result with no R code and little sign of SQL query that was introduced to sp_execute_external_query procedure.</p>
<p><img sizes="(max-width: 618px) 100vw, 618px" data-image-description="" data-orig-file="https://tomaztsql.files.wordpress.com/2017/04/2017-04-10-20_28_38-query_plan1-xml-microsoft-sql-server-management-studio.png?w=450#038;h=417" data-orig-size="1030,696" data-image-title="2017-04-10 20_28_38-query_plan1.xml – Microsoft SQL Server Management Studio" class="wp-image-4190" data-comments-opened="1" data-recalc-dims="1" data-attachment-id="4190" data-permalink="https://tomaztsql.wordpress.com/2017/04/10/saving-input-and-output-with-sp_execute_external_script/2017-04-10-20_28_38-query_plan1-xml-microsoft-sql-server-management-studio/#main" alt="2017-04-10 20_28_38-query_plan1.xml - Microsoft SQL Server Management Studio" data-medium-file="https://tomaztsql.files.wordpress.com/2017/04/2017-04-10-20_28_38-query_plan1-xml-microsoft-sql-server-management-studio.png?w=450#038;h=417?w=300" data-large-file="https://tomaztsql.files.wordpress.com/2017/04/2017-04-10-20_28_38-query_plan1-xml-microsoft-sql-server-management-studio.png?w=450#038;h=417?w=700" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://tomaztsql.files.wordpress.com/2017/04/2017-04-10-20_28_38-query_plan1-xml-microsoft-sql-server-management-studio.png?w=450#038;h=417"></p>
<p>Relative the same results emerges when showing actual execution plan in SSMS. Only XML-UDX is showed.</p>
<p><img sizes="(max-width: 509px) 100vw, 509px" data-image-description="" data-orig-file="https://tomaztsql.files.wordpress.com/2017/04/2017-04-10-20_33_13-saving_input_output_r_sp_execute_external_script-sql-sicn-kastrun-plan-spar_s.png?w=456" data-orig-size="509,547" data-image-title="2017-04-10 20_33_13-Saving_Input_Output_R_sp_execute_external_script.sql – SICN-KASTRUN.Plan (SPAR_s" data-comments-opened="1" class="wp-image-4195" data-recalc-dims="1" data-attachment-id="4195" data-permalink="https://tomaztsql.wordpress.com/2017/04/10/saving-input-and-output-with-sp_execute_external_script/2017-04-10-20_33_13-saving_input_output_r_sp_execute_external_script-sql-sicn-kastrun-plan-spar_s/#main" alt="2017-04-10 20_33_13-Saving_Input_Output_R_sp_execute_external_script.sql - SICN-KASTRUN.Plan (SPAR_s" data-medium-file="https://tomaztsql.files.wordpress.com/2017/04/2017-04-10-20_33_13-saving_input_output_r_sp_execute_external_script-sql-sicn-kastrun-plan-spar_s.png?w=456?w=279" data-large-file="https://tomaztsql.files.wordpress.com/2017/04/2017-04-10-20_33_13-saving_input_output_r_sp_execute_external_script-sql-sicn-kastrun-plan-spar_s.png?w=456?w=509" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://tomaztsql.files.wordpress.com/2017/04/2017-04-10-20_33_13-saving_input_output_r_sp_execute_external_script-sql-sicn-kastrun-plan-spar_s.png?w=456"></p>
<p>So far, very slim possibility to get some extra and additional information from query statistics DMV or execution plan.</p>
<h4>SQL SERVER PROFILER</h4>
<p>So opening SQL Profiler and running the example sp_execute_external_script code, I was finally able to see the actual R code within profiler:</p>
<p><img sizes="(max-width: 700px) 100vw, 700px" data-image-description="" data-orig-file="https://tomaztsql.files.wordpress.com/2017/04/2017-04-10-20_40_39-greenshot-image-editor.png?w=456" data-orig-size="961,212" data-image-title="2017-04-10 20_40_39-Greenshot image editor" data-comments-opened="1" class="wp-image-4206" data-recalc-dims="1" data-attachment-id="4206" data-permalink="https://tomaztsql.wordpress.com/2017/04/10/saving-input-and-output-with-sp_execute_external_script/2017-04-10-20_40_39-greenshot-image-editor/#main" alt="2017-04-10 20_40_39-Greenshot image editor" data-medium-file="https://tomaztsql.files.wordpress.com/2017/04/2017-04-10-20_40_39-greenshot-image-editor.png?w=456?w=300" data-large-file="https://tomaztsql.files.wordpress.com/2017/04/2017-04-10-20_40_39-greenshot-image-editor.png?w=456?w=700" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://tomaztsql.files.wordpress.com/2017/04/2017-04-10-20_40_39-greenshot-image-editor.png?w=456"></p>
<p>Upon closer look, we can see that profiler wraps execution of external procedure with following command SET STATISTICS XML ON/OFF. So we can store the results from profiler into a table or trace file and later filter out the R-code!</p>
<h4>QUERY STORE</h4>
<p>Query store is very very useful and new feature with flagship MSSQL2016. Storing the queries and execution times is therefore needed in order to do later performance analysis. So in this phase, let’s just see, if we can store external procedure code in query store.</p>
<p>With execution of R external procedure, I execute following query to check the Query Store (QS):</p>
<pre>
SELECT 
  QSQT.query_text_id
 ,QSQT.query_sql_text
 ,QSP.plan_id
FROM 
    sys.query_store_plan AS QSP
    JOIN sys.query_store_query AS QSQ  
    ON QSP.query_id = QSQ.query_id  
    JOIN sys.query_store_query_text AS QSQT  
    ON QSQ.query_text_id = QSQT.query_text_id</pre>
<p>And the results are – in a way – not surprising at all, since many of query store statistics base on DMV. So result for my external procedure is again, very little informative in order to extract R code:</p>
<p><img sizes="(max-width: 447px) 100vw, 447px" data-image-description="" data-orig-file="https://tomaztsql.files.wordpress.com/2017/04/2017-04-10-21_01_32-saving_input_output_r_sp_execute_external_script-sql-sicn-kastrun-plan-spar_s.png?w=456" data-orig-size="447,45" data-image-title="2017-04-10 21_01_32-Saving_Input_Output_R_sp_execute_external_script.sql – SICN-KASTRUN.Plan (SPAR_s" data-comments-opened="1" class="wp-image-4219" data-recalc-dims="1" data-attachment-id="4219" data-permalink="https://tomaztsql.wordpress.com/2017/04/10/saving-input-and-output-with-sp_execute_external_script/2017-04-10-21_01_32-saving_input_output_r_sp_execute_external_script-sql-sicn-kastrun-plan-spar_s/#main" alt="2017-04-10 21_01_32-Saving_Input_Output_R_sp_execute_external_script.sql - SICN-KASTRUN.Plan (SPAR_s" data-medium-file="https://tomaztsql.files.wordpress.com/2017/04/2017-04-10-21_01_32-saving_input_output_r_sp_execute_external_script-sql-sicn-kastrun-plan-spar_s.png?w=456?w=300" data-large-file="https://tomaztsql.files.wordpress.com/2017/04/2017-04-10-21_01_32-saving_input_output_r_sp_execute_external_script-sql-sicn-kastrun-plan-spar_s.png?w=456?w=447" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://tomaztsql.files.wordpress.com/2017/04/2017-04-10-21_01_32-saving_input_output_r_sp_execute_external_script-sql-sicn-kastrun-plan-spar_s.png?w=456"></p>
<p>Something, we have seen already couple of times. And no sign of execution of R Script. In fact, looking from this, it is hard even to tell, this was passed to RLaunchpad.exe external program.</p>
<h4>SINK</h4>
<p>Sink is a R function to store the output of the executed R code into external file. With execution of any of the two T-SQL code, I will never be able to either get the results nor the R code itself.</p>
<p>In case of results:</p>
<pre>EXEC sys.sp_execute_external_script
     @language = N'R'
    ,@script = N'
        sink(&quot;C:\\DataTK\\logRSQLsession3.txt&quot;)
        d &lt;- InputDataSet
        c &lt;- data.frame(Num_V1 = c(1,2,3))
        c
        sink()
        OutputDataSet &lt;- c'
    ,@input_data_1 = N'SELECT 1 AS Nmbrs_From_R'
WITH RESULT SETS ((Numbers_From_R INT));

EXEC sys.sp_execute_external_script
     @language = N'R'
    ,@script = N'
        c &lt;- data.frame(Num_V1 = c(1,2,3))
        c
        sink(&quot;C:\\DataTK\\logRSQLsession3.txt&quot;)'
    ,@input_data_1 = N'SELECT 1 AS Nmbrs_From_R'
WITH RESULT SETS NONE;</pre>
<p>In both cases the file is created, but it is just that. Empty file. No content whatsoever.</p>
<h4>LOAD</h4>
<p>Load will store intermediate results into file for later analysis or for semi aggreagated data, used for further calculations. So, I have tested it as following:</p>
<pre>EXEC sys.sp_execute_external_script
     @language = N'R'
    ,@script = N'
        c &lt;- data.frame(Num_V1 = c(1,2,3))
        c
        save(c, file=&quot;C:\\DataTK\\logRSQLsession3.rda&quot;)
        #load(file=&quot;C:\\DataTK\\logRSQLsession3.rda&quot;)'
    ,@input_data_1 = N'SELECT 1 AS Nmbrs_From_R'
WITH RESULT SETS NONE;


-- LOAD RESULTS
EXEC sys.sp_execute_external_script
     @language = N'R'
    ,@script = N'
        load(file=&quot;C:\\DataTK\\logRSQLsession3.rda&quot;)
        OutputDataSet &lt;- c'
    ,@input_data_1 = N'SELECT 1 AS Nmbrs_From_R'
WITH RESULT SETS ((Num_V1 INT));</pre>
<p> </p>
<h4>EXTENSIBILITY LOG</h4>
<p>Extensibility Log will store information about the session but it will not store the R or R environment information or data, just session information and data. Navigate to:</p>
<p>C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\LOG\ExtensibilityLog</p>
<p>to check the content and to see, if there is anything useful for your needs.</p>
<h3>Conclusion</h3>
<p>We are very limited in terms of exporting executed R code, results or Logs. Same applies for importing any additional code. We have seen that import, source are not working, whereas Load for loading *.rda files is working. At least something <img alt="🙂" class="wp-smiley" src="https://i0.wp.com/s0.wp.com/wp-content/mu-plugins/wpcom-smileys/twemoji/2/72x72/1f642.png?w=456&ssl=1" data-recalc-dims="1"> There should be more ways to get into the, especially with Rterm or Vanilla R, but the idea was to have everything run comfortably from the SSMS environment.</p>
<p>As you can see, there is little possibilities to store R code separately or store execution R logs in external files. But I presume, I haven’t exhausted all the possibilities, so there should be still some ways to try and do this.</p>
<p>As always, the code is available at <a target="_blank" rel="nofollow" href="https://github.com/tomaztk/Saving_input_and_output_with_sp_execute_external_script">Github</a>.</p>
<p>Happy Rrrrr!</p>
<p> <a target="_blank" rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/tomaztsql.wordpress.com/4166/"><img alt="" src="http://feeds.wordpress.com/1.0/comments/tomaztsql.wordpress.com/4166/" border="0"></a> <img alt="" src="https://i1.wp.com/pixel.wp.com/b.gif?resize=1%2C1&ssl=1" border="0" data-recalc-dims="1"></p>
<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://tomaztsql.wordpress.com/2017/04/10/saving-input-and-output-with-sp_execute_external_script/"> R – TomazTsql</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a title="The R Project for Statistical Computing" rel="nofollow" href="https://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/search/data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="https://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/search/map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/search/animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/search/sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/search/SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/search/git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/search/hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/search/PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/search/time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/search/trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/Uuabil1YC54"></a></div>