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 |
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 |