<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/passing-two-sql-queries-to-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/07/25/passing-two-sql-queries-to-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>Recently, I got a question on one of my previous blog posts, if there is possibility to pass two queries in same run-time as an argument to external procedure sp_execute_external_script.</p>
<p>Some of the  <a rel="nofollow" href="https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-execute-external-script-transact-sql" target="_blank">arguments</a> of the procedure sp_execute_external_script are enumerated. This is valid for the inputting dataset and as the name of argument <em>@input_data_1</em> suggests, one can easily (and this is valid doubt) think, there can also be @input_data_2 argument, and so on. Unfortunately, this is not true.  External procedure can hold only one T-SQL dataset, inserted through this parameter.</p>
<p>There are many reasons for that, one would be the cost of sending several datasets to external process and back, so inadvertently, this forces user to rethink and pre-prepare the dataset (meaning, do all the data munging beforehand), prior to sending it into external procedure.</p>
<p>But there are workarounds on how to pass additional query/queries to sp_execute_external_script. I am not advocating this, and I strongly disagree with such usage, but here it is.</p>
<p>First I will create two small datasets, using T-SQL</p>
<pre>USE SQLR;
GO

DROP TABLE IF EXISTS dataset;
GO

CREATE TABLE dataset
(ID INT IDENTITY(1,1) NOT NULL
,v1 INT
,v2 INT
CONSTRAINT pk_dataset PRIMARY KEY (id)
)

SET NOCOUNT ON;
GO

INSERT INTO dataset(v1,v2)
SELECT TOP 1
 (SELECT TOP 1 number FROM master..spt_values WHERE type IN ('EOB') ORDER BY NEWID()) AS V1
,(SELECT TOP 1 number FROM master..spt_values WHERE type IN ('EOD') ORDER BY NEWID()) AS v2
FROM master..spt_values
GO 50</pre>
<p>This dataset will be used directly into @input_data_1 argument. The next one will be used through R code:</p>
<pre>CREATE TABLE external_dataset
(ID INT IDENTITY(1,1) NOT NULL
,v1 INT
CONSTRAINT pk_external_dataset PRIMARY KEY (id)
)

SET NOCOUNT ON;
GO

INSERT INTO external_dataset(v1)
SELECT TOP 1
 (SELECT TOP 1 number FROM master..spt_values WHERE type IN ('EOB') ORDER BY NEWID()) AS V1
FROM master..spt_values
GO 50</pre>
<p>Normally,  one would use a single dataset like:</p>
<pre>EXEC sp_execute_external_script
     @language = N'R'
    ,@script = N'OutputDataSet &lt;- data.frame(MySet);'
    ,@input_data_1 = N'SELECT TOP 5 v1, v2 FROM dataset;'
    ,@input_data_1_name = N'MySet'
WITH RESULT SETS
((
 Val1 INT
 ,Val2 INT
))</pre>
<p>But by “injecting” the  ODBC into R code, we can allow external procedure, to get back to your SQL Server and get additional dataset.</p>
<p>This can be done by following:</p>
<pre>EXECUTE AS USER = 'RR'; 
GO

DECLARE @Rscript NVARCHAR(MAX)
SET @Rscript = '
   library(RODBC)
   myconn &lt;-odbcDriverConnect(&quot;driver={SQL Server};
         Server=SICN-KASTRUN;database=SQLR;uid=RR;pwd=Read!2$16&quot;)
  External_source &lt;- sqlQuery(myconn, &quot;SELECT v1 AS v3 
                    FROM external_dataset&quot;)
  close(myconn) 
  Myset &lt;- data.frame(MySet)
   #Merge both datasets
   mergeDataSet &lt;- data.frame(cbind(Myset, External_source));'

EXEC sp_execute_external_script
    @language = N'R'
   ,@script = @Rscript
   ,@input_data_1 = N'SELECT v1, v2 FROM dataset;'
   ,@input_data_1_name = N'MySet'
   ,@output_data_1_name = N'mergeDataSet'
WITH RESULT SETS
((
    Val1 INT
   ,Val2 INT
   ,Val3 INT
))

REVERT;
GO</pre>
<p>And the result will be merged two datasets, in total three columns:</p>
<p><img data-orig-file="https://tomaztsql.files.wordpress.com/2017/07/2017-07-25-22_04_48-two_data_sets_r_sp_execute_external_script-sql-sicn-kastrun-sqlr-spar_si01017.png?w=456" data-orig-size="261,213" data-medium-file="https://tomaztsql.files.wordpress.com/2017/07/2017-07-25-22_04_48-two_data_sets_r_sp_execute_external_script-sql-sicn-kastrun-sqlr-spar_si01017.png?w=456?w=261" data-attachment-id="4741" data-image-meta="{" data-image-description="" src="https://tomaztsql.files.wordpress.com/2017/07/2017-07-25-22_04_48-two_data_sets_r_sp_execute_external_script-sql-sicn-kastrun-sqlr-spar_si01017.png?w=456" data-permalink="https://tomaztsql.wordpress.com/2017/07/25/passing-two-sql-queries-to-sp_execute_external_script/2017-07-25-22_04_48-two_data_sets_r_sp_execute_external_script-sql-sicn-kastrun-sqlr-spar_si01017/#main" data-large-file="https://tomaztsql.files.wordpress.com/2017/07/2017-07-25-22_04_48-two_data_sets_r_sp_execute_external_script-sql-sicn-kastrun-sqlr-spar_si01017.png?w=456?w=261" alt="2017-07-25 22_04_48-Two_data_sets_R_sp_execute_external_script.sql - SICN-KASTRUN.SQLR (SPAR_si01017" data-image-title="2017-07-25 22_04_48-Two_data_sets_R_sp_execute_external_script.sql – SICN-KASTRUN.SQLR (SPAR_si01017" sizes="(max-width: 261px) 100vw, 261px" data-comments-opened="1" class="wp-image-4741" data-recalc-dims="1"></p>
<p>which correspond to two datasets:</p>
<pre>-- Check the results!
SELECT * FROM dataset
SELECT * FROM external_dataset</pre>
<p>There are, as already mentioned, several opposing factors to this approach, and I would not recommend this. Some are:</p>
<ul>
<li>validating and keeping R code in one place</li>
<li>performance issues</li>
<li>additional costs of data transferring</li>
<li>using ODBC connectors</li>
<li>installing additional R packages (in my case RODBC package)</li>
<li>keeping different datasets in one place</li>
<li>security issues</li>
<li>additional login/user settings</li>
<li>firewall inbound/outbound rules setting</li>
</ul>
<p>This, of course, can also be achieved with *.XDF file formats, if they are stored locally or on server as a files.</p>
<p>As always, code is available at <a rel="nofollow" href="https://github.com/tomaztk/Passing_two_queries_to_sp_execute_external_script" target="_blank">Github</a>.</p>
<p>Happy R-SQLing! <img alt="🙂" src="https://i0.wp.com/s0.wp.com/wp-content/mu-plugins/wpcom-smileys/twemoji/2/72x72/1f642.png?w=456&ssl=1" class="wp-smiley" data-recalc-dims="1"></p>
<p> <a rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/tomaztsql.wordpress.com/4705/" target="_blank"><img border="0" alt="" src="http://feeds.wordpress.com/1.0/comments/tomaztsql.wordpress.com/4705/"></a> <img border="0" alt="" src="https://i1.wp.com/pixel.wp.com/b.gif?resize=1%2C1&ssl=1" 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/07/25/passing-two-sql-queries-to-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 rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/GJHznSk5dyM" height="1"></a></div>