'default' showing up so, procedure or function has too many arguments specified;

Question

Overall Goal

Re-write portion of web site exposed to longtime clients that still relies upon SQL query execution of the format and is currently working as expected:

SQLQuery = "exec spNewPayments " & "162611"
  Set rsPmts = objConnection.Execute(SQLQuery)

Where 162611 is an integer. The goal then is to parameterize query using command object and adding parameters using .Parameters.Append .CreateParameter technique.

Additionally, secondary goal is to eventually add additional parameters to limit the scope of the query, but I haven't even gotten that far in this post.

Problem

When running query using objConnection.Execute, the stored procedure runs as expected with one parameter, an integer (see above). When running query using recordsource.Open command I receive message:

  spNewPayments has too many arguments specified
  Code: 80040E14

The stored procedure is created using:

USE [maindb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[spNewPayments] @claim_id int = NULL AS
select *
FROM dbo.objectTest co
WHERE co.foreign_id = @claim_id;

GO

The VBScript that I am currently using as a test is:

Dim strCnxn 
strCnxn = "DSN=myDSN"
Dim objConnection
Set objConnection = CreateObject("ADODB.Connection")  
objConnection.Mode = acModeRead
objConnection.Open strCnxn

Dim cmd
Set cmd = CreateObject("ADODB.Command")
Dim rsPmts 
Set rsPmts = CreateObject("ADODB.Recordset")
With cmd
.ActiveConnection = objConnection

.CommandType = 4
.CommandText = "spNewPayments"

.Parameters.Refresh

.Parameters.Append .CreateParameter("@claim_id", 3, , , 162611)

End With

rsPmts.Open cmd
rsPmts.Close 

Set rsPmts = Nothing
 Set objConnection = Nothing
 Set cmd = Nothing

As mentioned, when I run this, I get: "Procedure or function spNewPayments has too many arguments specified" It occurs in the line rsPmts.Open cmd

I trace using Profiler in the SQL Server MS to see how the server is responding, and I see:

declare @p1 int
set @p1=180150003
declare @p3 int
set @p3=8
declare @p4 int
set @p4=1
declare @p5 int
set @p5=2
exec sp_ddopen @p1 output,N'sp_sproc_columns',@p3 output,@p4 output,@p5 output,N'spNewPayments',NULL,NULL,NULL
select @p1, @p3, @p4, @p5
go
exec sp_cursorfetch 180150003,2,1,1
go
exec sp_cursorfetch 180150003,2,1,1
go
exec sp_cursorfetch 180150003,2,1,1
go
exec sp_cursorclose 180150003
go
exec spNewPayments default,162611
go

Aha! It appears it is passing two parameters, the first parameter as default. Huh? What is that? How does it get in there?

Obviously, this is caused by having too many parameters, but I don't think my code is adding two parameters, just one.

This error also seems to come up when people are inadvertently in a loop, but I haven't even gotten to my loop yet. I plan to loop through the recordsource later.

Answer 1

The .Parameter.Refresh() causes ADO to request the parameters from SQL Server which is an overhead you can see in the SQL Profiler trace you have provided. Calling this method populates the .Parameters collection automatically but is not recommended for production due to the server request being made to populate the collection on every execution.

These lines in the trace are due to the .Parameters.Refresh call;

-- Declaration and setting of temporary varibles
declare @p1 int
set @p1=180150003
declare @p3 int
set @p3=8
declare @p4 int
set @p4=1
declare @p5 int
set @p5=2
/*
Execution of sp_sproc_columns to get the expected parameters from the
spNewPayments stored procedure (see https://stackoverflow.com/q/7903268/692942).
*/
exec sp_ddopen @p1 output,N'sp_sproc_columns',@p3 output,@p4 output,@p5 output,N'spNewPayments',NULL,NULL,NULL
select @p1, @p3, @p4, @p5
go
-- Use cursor to loop through results.
exec sp_cursorfetch 180150003,2,1,1
go
exec sp_cursorfetch 180150003,2,1,1
go
exec sp_cursorfetch 180150003,2,1,1
go
-- Close the cursor.
exec sp_cursorclose 180150003
go

The reason for the error is because you then manually add the parameter using the .Parameters.Append() and the CreateParameter() methods. This would work fine and would be my recommended approach, if you hadn’t called Refresh() first in effect doubling up the parameter @claim_id in the Parameters collection causing it to have Too many arguments.

So, the fix in your code example is to remove;

.Parameters.Refresh

---

Useful Links

• Using Stored Procedure in Classical ASP .. execute and get results
• Error trying to call stored procedure with prepared statement
• Using METADATA to Import DLL Constants (If you are using Classic ASP would highly recommend this approach to use named constants)

Answer 2

There are two things I normally look at that should fix this.

The first is set the CommandType property of the ADODB.Command object to adCmdStoredProc like this:

cmd.CommandType = adCmdStoredProc 

But you may have already done that on this line:

.CommandType = 4

I'm just not sure from memory what value the adCmdStoredProc constant has.

The other is putting the placeholder directly in the command text:

.CommandText = "spNewPayments @claim_id"

This goes best with the adCmdText CommandType, and is not usually needed or helpful if CommandType was set to adCmdStoredProc, so this is a one-or-the-other situation. I've seen it done both ways effectively.

Looking again, you might want .Parameters.Clear rather than .Parameters.Refresh, but as you just created the object you shouldn't need either and it's been a very long time since I've done old-school ADO.