Error While running SQL Stored procedure thru Access 2010 VBA

Question

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.

What's wrong with my code?

    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

You are calling objCmd.Parameters.Refresh and then also creating the parameter and appending. Wouldn't you just set the parameter's value after Refresh. — Cetin Basoz
– Cetin Basoz, Commented Jul 22, 2018 at 11:13

Dan Guzman · Accepted Answer · 2018-07-22 13:15:33Z

1

It looks like this code is adding the same parameter twice. The Refresh method populates the parameter collection and then you are manually adding the same parameter again, and only one of the parameter values is set.

' 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

One way to fix is to remove the CreateParameter:

objCmd.Parameters.Refresh
objCmd.Parameters("@ExcelFilePath").Value = FilePath

Alternatively, you could remove the Refresh and create the parameters manually. The advantage of this method is it avoids the extra round trip to retrieve parameter meta-data.

Set objReturnCodeParm = objCmd.CreateParameter("@RETURN_CODE", adInteger, adParamReturnValue)
objCmd.Parameters.Append objReturnCodeParm
Set objParm = objCmd.CreateParameter("@ExcelFilePath", adVariant, adParamInput, , FilePath)
objCmd.Parameters.Append objParm

Note that sp_ should be avoided as a stored procedure name prefix. That prefix is used to designate system stored procedures.

EDIT:

catalog.start_execution will execute the package asynchronously so the wrapper proc will finish before it completes. If you want to wait, add a SYNCHRONIZED parameter like the example below. Be aware that the default ADO command timeout is 30 seconds so you may need to increase the value by setting objCmd.CommandTimeout to a higher value, or zero for no timeout.

EXEC SSISDB.catalog.set_execution_parameter_value
     @ExecutionID
    ,@object_type = @ObjectType --this must be 50
    ,@parameter_name = N'SYNCHRONIZED'
    ,@parameter_value = 1;

edited Jul 22, 2018 at 13:15

answered Jul 22, 2018 at 11:20

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Prasanna Over a year ago

Thanks for the response Dan, 2nd option executed without any error message. but the data is not imported to the SQL Server. do we need to set any permission to execute SSIS thru Stored procedure. as I am running this code from different user.

Nick.Mc Over a year ago

You need to take some steps to get more information than "data is not imported "

Dan Guzman Over a year ago

@Prasanna, check the execution log for the package. Packages started with catalog.start_execution run asynchronously so your proc will complete before the package actually runs.

Prasanna Over a year ago

@Dan, yes package execution log is showing as failed. how can we instruct stored procedure wait till the package get executed, as i am beginner in sql you are assistance will be helpful

Dan Guzman Over a year ago

@Prasanna, I added a synchronous example to my answer. Be aware that if this code is invoked from a form, the UI will freeze until the proc completes. You could also query the status column of catalog.executions after completion in your proc to determine if the package completed successfully.

|

Collectives™ on Stack Overflow

Error While running SQL Stored procedure thru Access 2010 VBA

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related