Error While running SQL Stored procedure thru Access 2010 VBA

[rd.prasanna]

Hi All,

I am trying to run sql stored procedure from Access form.
Which is throwing error “procedure or function has too many arguments” while I have only one parameter passing to stored procedure.

i am using sql server 2012

I have attached the stored procedure and VBA code below.



Kindly check and let me know where iam going wrong.

ALTER Procedure [dbo].[SP_SSIS_pkg_Rfnd_BSP] (@ExcelFilePath sql_variant)
 
As
 
 begin
 DECLARE @FolderName nvarchar(128) = 'Import_RAData_BSP'
 DECLARE @ProjectName nvarchar(128) = 'SSIS_Rfnd_App_BSP'
 DECLARE @PackageName nvarchar(260) = 'pkg_Rfnd_BSP.dtsx'
 DECLARE @LoggingLevel varchar(16) = 'Basic'
 DECLARE @Use32BitRunTime bit = 0
 DECLARE @ReferenceID bigint = NULL
 DECLARE @ObjectType smallint = 50
 DECLARE @ExecutionID bigint 
 
 Set NOCOUNT ON
  
   /* Call the catalog.create_execution stored procedure
      to initialize execution location and parameters */
  Exec SSISDB.catalog.create_execution
   @package_name = @PackageName
  ,@execution_id = @ExecutionID Output
  ,@folder_name = @FolderName
  ,@project_name = @ProjectName
  ,@use32bitruntime = @Use32BitRunTime
  ,@reference_id = @ReferenceID
 
   /* Populate the @ExecutionID parameter for OUTPUT */
  Select @ExecutionID As Execution_Id
 
   /* Create a parameter (variable) named @Sql */
  Declare @logging_level smallint
   /* Decode the Logging Level */
  Select @logging_level = Case
                           When Upper(@LoggingLevel) = 'BASIC'
                           Then 1
                           When Upper(@LoggingLevel) = 'PERFORMANCE'
                           Then 2
                            When Upper(@LoggingLevel) = 'VERBOSE'
                           Then 3
                           Else 0 /* 'None' */
                          End
 
   /* Call the catalog.set_execution_parameter_value stored
      procedure to update the LOGGING_LEVEL parameter */
  Exec SSISDB.catalog.set_execution_parameter_value
    @ExecutionID
   ,@object_type = 30
   ,@parameter_name = N'ExcelFilePath'
   ,@parameter_value = @ExcelFilePath
 
 
   /* Call the catalog.set_execution_parameter_value stored
      procedure to update the LOGGING_LEVEL parameter */
  Exec SSISDB.catalog.set_execution_parameter_value
    @ExecutionID
   ,@object_type = @ObjectType
   ,@parameter_name = N'LOGGING_LEVEL'
   ,@parameter_value = @logging_level
 
   /* Call the catalog.start_execution (self-explanatory) */
  Exec SSISDB.catalog.start_execution @ExecutionID
 end

VBA Function to execute stored procedure

Function Import_RA_Data(ByVal FileName As String, FName As String)
    On Error GoTo ErrHandler:
 
    Dim objConn As New ADODB.Connection
    Dim objCmd As New ADODB.Command
    Dim objParm As New ADODB.Parameter
    Dim objRs As New ADODB.Recordset
    Dim FilePath As String
  
    ' Set CommandText equal to the stored procedure name.
    objCmd.CommandText = "SP_SSIS_pkg_Rfnd_BSP"
    objCmd.CommandType = adCmdStoredProc
 
    ' Connect to the data source.
    Set objConn = GetNewConnection
    objCmd.ActiveConnection = objConn
 
    ' Automatically fill in parameter info from stored procedure.
    objCmd.Parameters.Refresh
    objParm.Value = FilePath
 
    Set objParm = objCmd.CreateParameter("@ExcelFilePath", adVariant, adParamInput, , objParm.Value)
        objCmd.Parameters.Append objParm
   
    objRs.CursorType = adOpenStatic
    objRs.CursorLocation = adUseClient
    objRs.LockType = adLockOptimistic
    objRs.Open objCmd
   
    ' Execute once and display...
    Set objRs = objCmd.Execute
 
    'clean up
    objRs.Close
    objConn.Close
    Set objRs = Nothing
    Set objConn = Nothing
    Set objCmd = Nothing
    Set objParm = Nothing
    Exit Function
 
ErrHandler:
    'clean up
    If objRs.State = adStateOpen Then
        objRs.Close
    End If
 
    If objConn.State = adStateOpen Then
        objConn.Close
    End If
 
    Set objRs = Nothing
    Set objConn = Nothing
    Set objCmd = Nothing
    Set objParm = Nothing
 
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, vbCritical, "Error"
 
    End If
End Function

Regards,
Prasanna

[CJ_London]

cross posted here with answers

https://www.access-programmers.co.uk...d.php?t=300625

@prasanna - please read this link about the etiquette of cross posting http://www.excelguru.ca/content.php?184

[rd.prasanna]

My Apologies....

[isladogs]

Also at http://www.utteraccess.com/forum/ind...ic=2049896&hl=

@rdprasanna
Please post links at all other sites yourself to avoid others unaware of the multiple posts wasting time