Bug with sp_execute_external_script

Question

I think I encountered a bug with sp_execute_external_script, so here is the replay.

I will get an error if I run the following script:

-- error occurs
exec sp_execute_external_script @language=N'R'
, @script = N' result <- as.data.frame(c(i, j));'
, @params = N'@i varchar(100), @j varchar(100)'
, @i = 'hello'
, @j = 'world'
, @output_data_1_name = N'result' -- the position of this param caused problem
with result sets ((test varchar(100)));

I will get an error

Msg 8144, Level 16, State 2, Line 1
Procedure or function has too many arguments specified.

However, if I just move up the @output_data_1_name like the following:

-- NO error occurs
exec sp_execute_external_script @language=N'R'
, @script = N' result <- as.data.frame(c(i, j));'
, @output_data_1_name = N'result' -- this position in front of @parms caused NO problem
, @params = N'@i varchar(100), @j varchar(100)'
, @i = 'hello'
, @j = 'world'
with result sets ((test varchar(100)));

I will get a normal output:

enter image description here

Does anyone know whether I am doing something wrong or it indeed could be a bug in SQL Server 2016?

My environment is (by select @@version):

Microsoft SQL Server 2016 (SP1-CU1) (KB3208177) - 13.0.4411.0 (X64) 
 Jan 6 2017 14:24:37 
 Copyright (c) Microsoft Corporation
 Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 14393: )

Update: I tested a little bit more and indeed the position of parameters will cause errors, such as following:

exec sp_execute_external_script 
 @script = N' result <- as.data.frame(c(i, j));'
, @output_data_1_name = N'result' -- the position of this param caused NO problem
, @params = N'@i varchar(100), @j varchar(100)'
, @i = N'hello'
, @j = N'world'
, @language=N'R' -- if I put @language here, it will cause error
with result sets ((test varchar(100)));

or this:

exec sp_execute_external_script @language=N'R'
, @script = N' result <- as.data.frame(c(i, j));'
, @output_data_1_name = N'result' -- the position of this param caused NO problem
, @i = N'hello'
, @j = N'world'
, @params = N'@i varchar(100), @j varchar(100)'
with result sets ((test varchar(100)));

The error message will be:

Msg 214, Level 16, State 175, Procedure sp_execute_external_script, Line 1 [Batch Start Line 19]
Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'.

But I actually give each parameter of type nvarchar already.

I will consider open a bug report to MS if I do not see anyone pointing out my "errors".

• McNets: AFAIK sp_execute_external_script is a CLR function, I suppose that params represents a variable number of arguments, then it should be place at end of the param list.

Answer 1

The parameter list in @params can have names matching the names of the parameters used by the sp_execute_external_script procedure itself, like @script or @language, for instance. There would need to be a way, then, to determine whether a parameter pertains to the system SP or to the user script.

Making it a rule that @params be always specified last in the list of the parameters pertaining to sp_execute_external_script and all the parameters specified after @params be considered pertaining to the user script is one easy way to implement the distinction. So, if a parameter like @language is specified after @params, it will be interpreted as a script parameter and not the SP's parameter.

And I believe that is what is happening in the cases where you are specifying @output_data_1_name or @language after @params. Those parameters simply must go before @params to avoid potential ambiguity.

Granted, there is not a word about this in the manual, which, however, is a problem with the manual more than a problem with sp_execute_external_script.

• jyao: Thanks Anndriy, I vote your theory up since it works, i.e. anything after @ params are considered part of @ params. But I am not sure it is a bug in the document or it is a bug in the SP itself. Could any CLR expert here explains whether in such business requirement, there is NO way to put the parameters of a CLR SP arbitrarily or we have to follow a specified order of parameters?

Answer 2

I received the following reply from MS Connect, I purposely highlight the key info (in bold font). So in short, it is indeed a by-design behavior. :-)

Greetings from Microsoft Connect!

This notification was generated for feedback item: sp_execute_external_script will report error when named parameters are not in a specific position. which you submitted at theMicrosoft Connect site.

This behavior is by design. The mandatory parameters like @language, @script have to be specified first before the user-defined parameters. This is like sp_executesql behavior in T-SQL today.

You may receive a general "Feedback Item Updated" notification as well, if any other changes were made by Microsoft.

Thank you for using Microsoft Connect!

Regards,

the Microsoft Connect Team