?
Solved

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

Posted on 2010-09-22
12
Medium Priority
?
806 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 143

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 143

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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 143

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

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…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
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…
Suggested Courses

770 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