Solved

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

Posted on 2010-09-22
12
765 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:ict-torquilclark
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33733084
I would say: you are not on the same database for the CREATE PROC vs the execution of the proc...
0
 
LVL 1

Author Comment

by:ict-torquilclark
ID: 33733173
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33733190
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33733238
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
 
LVL 1

Author Comment

by:ict-torquilclark
ID: 33733337
Can i check that where i have used RETURN (line 23) that will exit from the procedure?
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33733477
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
 
LVL 1

Author Comment

by:ict-torquilclark
ID: 33733680
I have it seems fine...
0
 
LVL 1

Author Comment

by:ict-torquilclark
ID: 33733693
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
 
LVL 1

Author Comment

by:ict-torquilclark
ID: 33733700
sorry I added the msgbox to see what was being passed to make surei was ok
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33734587
dont find any problem.
can u share exact error information.?

did u execute proc from backend?does it worked fine?
0
 
LVL 10

Accepted Solution

by:
3_S earned 500 total points
ID: 33737265
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33752773
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question