Run SQL Server stored procedure in Excel macro

[Peder Myhre]

I am trying to run a parameterized SQL Server stored procedure from an Excel
macro using the ADODB library ("Microsoft ActiveX Data Objects 2.8 Library").
The stored procedure only has action queries. It only accepts one input
parameter, no output parameters. I have tried numerous methods of running the
procedure with no luck. I keep getting an "automation error", "unspecified
error", but no error description. Here is the code:

Private Sub CommandButton1_Click()
' This requires a Reference to Microsoft ActiveX Data Objects 2.x Library
Const cConnection = "Provider=sqloledb;" & _
"server=finseaa16;database=rfdb;uid=rfdb_rw;pwd=xxxx"
Const cSQL = "CLS_PKG_TOP20_BLR"
Dim con As ADODB.Connection, cmd As ADODB.Command
Set con = New ADODB.Connection
con.Open cConnection 'Open connection to the database
Set cmd = New ADODB.Command
cmd.ActiveConnection = con 'Set up our command object for executing SQL
statement
cmd.CommandText = cSQL
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("InputRun", adVarChar,
adParamInput, 8, "R09SEP05")
'cmd("InputRun").Value = "R09SEP05"
cmd.Execute Options:=adExecuteNoRecords
End Sub

I have also tried using a CommandType of acCmdText and including the
parameter in the CommandText like this:

..CommandText = "EXEC CLS_PKG_TOP20_BLR 'R09SEP05'"

 

[Ed Ferrero]

Hi Peder,

Here is what I use

Dim Conn_obj As New ADODB.Connection
Dim Cmd_obj As New ADODB.Command

' build connection string using paramaters
Conn_obj.Open "Driver=SQL Server;Server=" & R_Server & ";Database=" &
R_database, txtUsr, txtPw

If Conn_obj.State = adStateOpen Then

' set the command object properties
Cmd_obj.ActiveConnection = Conn_obj
Cmd_obj.CommandText = "ContractExists_P"
Cmd_obj.CommandType = adCmdStoredProc

' set the command object parameters
Cmd_obj.Parameters.Append Cmd_obj.CreateParameter("RC", adInteger,
adParamReturnValue)
Cmd_obj.Parameters.Append Cmd_obj.CreateParameter("Contract_id",
adInteger, adParamInput, , contract)
Cmd_obj.Parameters.Append Cmd_obj.CreateParameter("RetVal", adInteger,
adParamOutput)

Cmd_obj.Execute

' check the return value
If Cmd_obj("RC") <> 0 Then
CheckContractExists = 0
Else
CheckContractExists = Cmd_obj.Parameters.Item("RetVal")
End If

End If

Set Cmd_obj = Nothing
Conn_obj.Close
Set Conn_obj = Nothing

Offhand, I can't see anything wrong with your code.
Things you can check;
- do you have the correct permissions on the stored procedure?
- are you sure the parameter is a VarChar?