@args is not a parameter for procedure

Question

I am trying to call a stored procedure with a string as parameter (VARCHAR (MAX)) but again and again it tells my @args parameter is not when it certainly is. This is my test procedure:

IF OBJECT_ID ( 'TEST', 'P' ) IS NOT NULL   
    DROP PROCEDURE TEST;  
GO

CREATE PROCEDURE TEST (@args varchar (max)) AS
BEGIN
    EXEC sp_execute_external_script
    @language = N'R'
    , @script = N'OutputDataSet <- as.data.frame(...);'
    , @params = N'@args varchar(max)'
    , @args = @args
WITH RESULT SETS ((...));
RETURN 0;
END

If I call it from management studio, it works:

SET LANGUAGE ENGLISH
EXEC dbo.TEST @args = 'long string'
GO

but not through C#

public static void Main()
{
    Console.WriteLine("Connection test!");
    Console.WriteLine("Press ESC to stop");

    string ConnectionString = "...";
    SqlConnection conn = new SqlConnection(ConnectionString);
    SqlCommand    cmd  = new SqlCommand("TEST");
    SqlDataReader rdr  = null;

    string args = "very long string";

    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection  = conn;
    cmd.Parameters.Add("@args", SqlDbType.VarChar, -1).Value = args;
    conn.Open();

    var returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);
    returnParameter.Direction = ParameterDirection.ReturnValue;
    try { cmd.ExecuteNonQuery(); } // @args is not a parameter for TEST Procedure
    catch (SqlException ex)

I am not reusing any parameter which is just a varchar(max). Any ideas?

You can try to change this line `cmd.Parameters.Add("@args", SqlDbType.VarChar, -1).Value = args;` to `cmd.Parameters.Add("@args", SqlDbType.VarChar).Value = args;` — Rigerta, Jun 16 '17 at 11:25
Unfortunately, same result. I understand -1 tells C# to set args as a varchar(max). What is the difference if I remove it? — Leonardo Lanchas, Jun 16 '17 at 11:29
Theoretically your code looks OK. Even with it, it should have worked and you are right. Maybe you are not connected to the right database then, on your solution? — Rigerta, Jun 16 '17 at 11:31
Assuming you are connected to the correct database what you have looks ok, what is the exact error message? — Alex K., Jun 16 '17 at 11:31
The message says: Index #0 Message: @args ist kein Parameter für die TEST-Prozedur. (@args is not a parameter for the TEST procedure.) LineNumber: 0 Source: .Net SqlClient Data Provider Procedure: TEST By the way, I know I am connected to the right DB because the error rises when I change the param type. Everything is ok when I sum 2 ints for example. — Leonardo Lanchas, Jun 16 '17 at 11:34
Does command text of "dbo.TEST" make a difference? As a sanity check drop the proc & run the code and see if you get a cannot find procedure error. — Alex K., Jun 16 '17 at 11:40
@mjwills Same error when renaming the variable. What is interesting is what happens after dropping the procedure as suggested by Alex K, I get the "cannot find procedure error" but C# still tells that args (or bob) is not a parameter, so I infer the error is in C# but where? — Leonardo Lanchas, Jun 16 '17 at 11:46
@jdweng a long string with params and settings for the R script — Leonardo Lanchas, Jun 16 '17 at 11:47
I get the "cannot find procedure error" but C# still tells that args (or bob) is not a parameter, so I infer the error is in C# but where? -> Can you show us screenshots of both those errors (cannot find procedure and args is not a parameter) when you delete the stored proc? — mjwills, Jun 16 '17 at 11:53
Yes. In fact, that's part of the R script, so it shouldn't affect. — Leonardo Lanchas, Jun 16 '17 at 11:54
Try running the stored procedure in SQL Server Management Studio (SSMS)., There may be an error in the query. The error messages are much better in SSMS than VS, — jdweng, Jun 16 '17 at 12:02

score 0 · Accepted Answer · answered Jun 16 '17 at 18:20

Finally I found the error which was extremely silly.

Short answer: added the SQL USE statement before dropping and creating the proc.

USE myDB;
GO

Long answer: It turns out I have 2 DBs and I was creating the SP in master but I had another procedure in my testing DB. So although I deleted the SP, there was another one in the other DB with the same name but different parameters, hence the error. As I could not understand why @args was incorrect, I listed the SP params (https://stackoverflow.com/a/3038470/2846161, https://stackoverflow.com/a/3038530/2846161) and it turned out that the procedure was listed even being deleted, therefore it was replicated.

@args is not a parameter for procedure

1 Answers1