Solved

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

Posted on 2010-09-22
12
740 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]
Comment Utility
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
Comment Utility
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]
Comment Utility
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:ict-torquilclark
Comment Utility
I have it seems fine...
0
 
LVL 1

Author Comment

by:ict-torquilclark
Comment Utility
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
Comment Utility
sorry I added the msgbox to see what was being passed to make surei was ok
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
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
Comment Utility
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]
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql Audit table 3 46
VB6 Compile Compatibility Issue 4 23
sorting efficency of sorting algorithm 30 77
Round up to 100% in .NET 10 39
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now