Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Incorrect error message for missing parameter with sp_execute_external_script

I use sp_execute_external_script and I spell a parameter name incorrectly. For instance:

EXEC sp_execute_external_script @language = N'Python',
@script = N'import re, pandas
Ret = InputDataSet
Ret["an"] = pandas.Series([re.sub("[^0-9]", "", i) for i in Ret["an"]], index = Ret.index, dtype = "int32")
',
@input_data_1 = N'SELECT an FROM alphanum',
@output_data = N'Ret'

The error message is

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

Which is confusing , since sp_execute_external_script has a parameter by that name, but it is not mandatory.

In fact, I get the same message, even if I provide @params:

EXEC sp_execute_external_script @language = N'Python',
@script = N'import re, pandas
Ret = InputDataSet
Ret["an"] = pandas.Series([re.sub("[^0-9]", "", i) for i in Ret["an"]], index = Ret.index, dtype = "int32")
',
@input_data_1 = N'SELECT an FROM alphanum',
@output_name = N'Ret',
@params = N'@Ret int'

The error message should say something like "@output_name is not a parameter to sp_execute_external_script".

3 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

Erland Sommarskog shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

1 comment

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...
  • AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) commented  ·   ·  Flag as inappropriate

    In the first case:
    EXEC sp_execute_external_script @language = N’Python’,
    @script = N’import re, pandas
    Ret = InputDataSet
    Ret[“an”] = pandas.Series([re.sub(”[^0-9]”, “”, i) for i in Ret[“an”]], index = Ret.index, dtype = “int32″)‘,
    @input_data_1 = N’SELECT an FROM alphanum’,
    @output_data = N’Ret’

    we would treat @output_data as a user defined parameter and expect the params clause. There is no way for us to know whether @output_data is a misspelling of @output_data_1_name or the user wants a parameter @output_data.

    There are two issues in the second example:
    EXEC sp_execute_external_script @language = N’Python’,
    @script = N’import re, pandas
    Ret = InputDataSet
    Ret[“an”] = pandas.Series([re.sub(”[^0-9]”, “”, i) for i in Ret[“an”]], index = Ret.index, dtype = “int32″)‘,
    @input_data_1 = N’SELECT an FROM alphanum’,
    @output_name = N’Ret’,
    @params = N’@Ret int’

    @params should be before @output_name and should contain the declaration of the @output_name parameter, not the value N'Ret'. Something like:

    EXEC sp_execute_external_script
    @language = N'Python',
    @script = N'...',
    @input_data_1 = N'SELECT an FROM alphanum',
    @params = N'@output_name nvarchar(max)',
    @output_name = N'Ret'

Feedback and Knowledge Base