Solved

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

Posted on 2010-09-22
12
756 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
 
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 2 45
I need help embedding an image as HTML in my vb.net application 3 49
Child Form in front 4 38
Help with AsEnumerable(), LINQ 4 22
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…

920 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

12 Experts available now in Live!

Get 1:1 Help Now