2

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".

  • 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. – McNets Jan 19 '17 at 9:15
2

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.

  • 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? – jyao Jan 19 '17 at 16:28
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.