Too many arguments were supplied - Stored Procedure

[mamartin]

I have a VB (6.0) app that is calling a SQL Server 7.0 stored procedure. The app calls the stored procedure from a Do Until loop based upon the number of rows returned from a query. The SP gathers rows from remote databases and inserts them into a temporary table created on the local server. It works fine for the first iteration, but when it goes to run the SP a second time, the message "too many arguments were supplied for procedure sp_all_resc_furn". Here is the code from the VB app:

Private Sub cmd_pre_process_Click()

Dim Conn1 As New ADODB.Connection
Dim Conn2 As New ADODB.Connection
Dim RecSet1 As New ADODB.Recordset
Dim RecSet2 As New ADODB.Recordset
Dim prmdbnamevar As ADODB.Parameter
Dim cmd As New ADODB.Command
Dim strSQL As String

Dim IP_ADDR As String
Dim MyServer As String
MyServer = "xxx.xxx.xxx.xxx"

Conn1.Open "DSN=homes3"
Conn1.CommandTimeout = 300

RecSet1.Open "Use master Select datasource From sysservers Where srvstatus = 225", Conn1, adOpenStatic, adLockReadOnly

RecSet1.MoveFirst
Do Until RecSet1.EOF
IP_ADDR = "[" + RecSet1.Fields("datasource&quot + "].HOMES"

Conn2.Provider = "sqloledb"
Conn2.Properties("Data Source&quot.Value = MyServer
Conn2.Properties("Initial Catalog&quot.Value = "HOMES"
Conn2.Properties("Integrated Security&quot.Value = "SSPI"
Conn2.Open

cmd.ActiveConnection = Conn2
cmd.CommandText = "sp_all_resc_furn"
cmd.CommandType = adCmdStoredProc

Set prmdbnamevar = Nothing
Set prmdbnamevar = cmd.CreateParameter("dbnamevar", adVarChar, adParamInput, 25, IP_ADDR)
cmd.Parameters.Append prmdbnamevar

Set RecSet2 = cmd.Execute

Set RecSet2 = Nothing
Set Conn2 = Nothing
RecSet1.MoveNext
Loop

Set RecSet1 = Nothing
Set Conn1 = Nothing

End Sub


Here is the stored_procedure:

CREATE PROCEDURE sp_all_resc_furn @dbnamevar varchar(25)
AS

DECLARE @sqlvar nvarchar(4000)

SET @sqlvar =
' INSERT INTO ALL_RESC_FURN
SELECT
IVAR.installation_name As Installation_Name,
Cast(FL.uic_indicator as Integer) As UIC,
FCR.rollup_description As Roll_Desc,
FC.item_description As Item_Desc,
FC.stock_number As Stk_Nbr,
FL.listing_type As List_Type,
FC.accounting_recovery_code As Dwelling_Status,
SUM(FPL.on_hand_quantity) As OH_Qty
FROM ' +
@dbnamevar + '.DBO.FURNISHINGS_LISTING FL INNER JOIN ' +
@dbnamevar + '.DBO.FURNISHINGS_PER_LISTING FPL ON
FL.listing_id = FPL.listing_id INNER JOIN ' +
@dbnamevar + '.DBO.FURNISHINGS_CATALOG FC ON
FPL.catalog_item_id = FC.catalog_item_id INNER JOIN ' +
@dbnamevar + '.DBO.FURNISHINGS_CATALOG_ROLLUP FCR ON
FC.rollup_id = FCR.rollup_id,
' + @dbnamevar + '.DBO.FURN_INSTALLATION_VARIABLE IVAR
WHERE
FL.listing_type in (''B'', ''M'', ''N'', ''T'', ''U'', ''W'') and
FPL.on_hand_quantity > 0
GROUP BY
Installation_Name,
Cast(FL.uic_indicator as Integer),
FCR.rollup_description,
FC.item_description,
FC.stock_number,
FL.listing_type,
FC.accounting_recovery_code'

exec sp_executesql @sqlvar


If anybody has any ideas/tips, please pass 'em on.

Thanks,
Michael A. Martin

 

[zemp]

Seems that you are adding another parameter to your command object with each iteration. Try creating and destroying your command object (cmd) within each iteration like you do with the recordset and connection objects (RecSet2,Conn2).

Thanks and Good Luck!

zemp

 

[mamartin]

Thanks, what I added was the statement "cmd.Parameters.Refresh" after calling and executing the SP. That seemed to work quite well.

Michael