Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run SQL Server stored procedure in Excel macro
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=xx xx" 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'" -- Peder Myhre |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run SQL Server stored procedure in Excel macro
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? -- Ed Ferrero http://edferrero.m6.net/ 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=xx xx" 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'" -- Peder Myhre |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pass paramter to SQL server stored procedure | Excel Programming | |||
How to pass an Excel range as an argument to a SQL Server stored Procedure | Excel Programming | |||
calling a stored procedure on MS SQL Server within MS Excel 2000 | Excel Programming | |||
Importing Data from a Stored Procedure (SQL SErver) | Excel Programming | |||
How can I grab data from a SQL Server stored procedure | Excel Programming |