Procedure or Function .... has too many arguments specified

I have written the attatched stored procedure in SQL 2005.

I am calling it in VB6 using the 2nd piece of attatched code.

For some reason when I try to pass the one Parameter "RecordGUID" to the SP I get the error

Procedure or Function DORIS_KeepAgentSpecific has too many arguments specified


does anybody have any ideas?
CREATE PROCEDURE DORIS_KeepAgentSpecific
	@RecordGUID AS NVARCHAR(255)
AS
	DECLARE @AgentGUID AS NVARCHAR(255)
	DECLARE @AgentName AS NVARCHAR(255)

	IF (SELECT COUNT(*) FROM _HistoryCalls_TorquilClarkApplication WHERE RecordGUID = @RecordGUID AND Result = '98') <> 0
	-- This record has been called today
		BEGIN
			SET @AgentName = (SELECT TOP 1 Agent_Desc FROM _HistoryCalls_TorquilClarkApplication WHERE RecordGUID = @RecordGUID AND Result = '98' ORDER BY Call_PreStart ASC)
			SET @AgentGUID = (SELECT ID FROM _Agent WHERE Name = @AgentName)
		END
	ELSE
		BEGIN
			IF (SELECT COUNT(*) FROM ApplicationServerDialler_Archive.DBO._Archive_HistoryCalls_TorquilClarkApplication WHERE RecordGUID = @RecordGUID AND Result = '98') <> 0
			-- This record has not been called today
				BEGIN
					SET @AgentName = (SELECT TOP 1 Agent_Desc FROM ApplicationServerDialler_Archive.DBO._Archive_HistoryCalls_TorquilClarkApplication WHERE RecordGUID = @RecordGUID AND Result = '98' ORDER BY Call_PreStart ASC)
					SET @AgentGUID = (SELECT ID FROM _Agent WHERE Name = @AgentName)
				END
			ELSE
				BEGIN
					RETURN
				END
		END

	BEGIN
		UPDATE _Callbacks_TorquilClarkApplication
		SET Agent_GUID = @AgentGUID
		WHERE Record_GUID = @RecordGUID
	END

Open in new window

Dim cmd2 As New ADODB.Command
Dim id As String
    
cmd2.ActiveConnection = sqlDialler
cmd2.CommandType = adCmdStoredProc
cmd2.CommandText = "DORIS_KeepAgentSpecific"
    
id = rs.Fields(0).Value
    
cmd2.Parameters.Append cmd2.CreateParameter("RecordGUID", adChar, adParamInput, 50, id)

cmd2.Execute

Open in new window

LVL 1
ict-torquilclarkAsked:
Who is Participating?
 
3_SConnect With a Mentor Commented:
Change
cmd2.CommandText = "DORIS_KeepAgentSpecific"
to
cmd2.CommandText = "Databasename.DBO.DORIS_KeepAgentSpecific"
(change databasename to your databasename. Then you are sure the correct sp is taken)

Change
cmd2.Parameters.Append cmd2.CreateParameter("RecordGUID", adChar, adParamInput, 50, id)
to
cmd2.Parameters.Append cmd2.CreateParameter("RecordGUID", adVarChar, adParamInput, len(id), id)

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I would say: you are not on the same database for the CREATE PROC vs the execution of the proc...
0
 
ict-torquilclarkAuthor Commented:
I have attatched the connection string that I am using in cmd.activeconnection

and I have attatched a picture from SQL management stuid with the proc and db name visible

I think they are the same? :-(
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ApplicationServerDialler;Data Source=SQL1

Open in new window

untitled.JPG
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
unless you have a trigger on update for the table _Callbacks_TorquilClarkApplication , which in turn calls the procedure, I don't see how this error could happen, then ...
0
 
Bhavesh ShahLead AnalysistCommented:
Dear Author,

Please check 2 things

1st one - cmd2.Parameters.Append cmd2.CreateParameter("RecordGUID", adChar, adParamInput, 50, id)
what value u r passing.

2nd is -

can u able print whole sql server error as it also contain object name as Super Expert said that there could be some trigger.



0
 
ict-torquilclarkAuthor Commented:
Can i check that where i have used RETURN (line 23) that will exit from the procedure?
0
 
Bhavesh ShahLead AnalysistCommented:
Yes you can do so from backend.

just
print 'After return' after RETURN statement.

it wont be print.

execute proc from backend.

2ndly can u please execute proc from backend with parameter "RecordGUID"


0
 
ict-torquilclarkAuthor Commented:
I have it seems fine...
0
 
ict-torquilclarkAuthor Commented:
Here is the full vb6 code....


Private Sub KeepAgentSpecific()

Dim rs As New ADODB.Recordset
Dim sqlstr As String

sqlstr = "SELECT D.ID FROM _Campaign_1 D INNER JOIN _Callbacks_TorquilClarkApplication C ON D.ID = C.Record_GUID WHERE C.Agent_GUID IS NULL AND D.Times_Called > 0 AND D.Status <> 1"
rs.Open sqlstr, sqlDialler, adOpenStatic, adLockOptimistic

If rs.RecordCount = 0 Then
    rs.Close
    Exit Sub
End If

Dim x As Integer
x = 1


Do While x <= rs.RecordCount
    
    Dim cmd2 As New ADODB.Command
    Dim id As String
    
    cmd2.ActiveConnection = sqlDialler
    cmd2.CommandType = adCmdStoredProc
    cmd2.CommandText = "DORIS_KeepAgentSpecific"
    
    id = rs.Fields(0).Value
    MsgBox (id)
    cmd2.Parameters.Append cmd2.CreateParameter("RecordGUID", adChar, adParamInput, 50, id)
    
    cmd2.Execute
    
    x = x + 1
    rs.MoveNext
Loop

End Sub

Open in new window

0
 
ict-torquilclarkAuthor Commented:
sorry I added the msgbox to see what was being passed to make surei was ok
0
 
Bhavesh ShahLead AnalysistCommented:
dont find any problem.
can u share exact error information.?

did u execute proc from backend?does it worked fine?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what of the 2 suggestions was the fix?
I must presume, the first one, which would match my first comment:

>I would say: you are not on the same database for the CREATE PROC vs the execution of the proc...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.